Archive for May, 2013

Things you wish to have done when you look back on Life May 29th, 2013

Vinod Kumar

Don’t cry because it’s over, smile because it happened ― Dr. Seuss

Life is full of twists and aspirations that we always keep running against. How many of you ever looked back on your life and said – I should have done this or that. It is human instinct to always look back and wish things were not the way it has been for ages.

You only live once, but if you do it right, once is enough ― Mae West

In this blog post, let me take my top 21 wish list that I always love to share with folks. This is not complete list but just representative to what I thought comes to my mind. I am sure you will have a bigger list and do feel free to share them.

Life is what happens to you while you’re busy making other plans ― Allen Saunders

  1. Be more confident
  2. Live life more than you just existed
  3. Gain one good habit once a month
  4. Live the moment and enjoy it
  5. Love more than you hated
  6. Forgive people more than holding back
  7. Laugh more than the times we cry
  8. Break all bad habits
  9. Be kinder

    Everything you can imagine is real ― Pablo Picasso

  10. Trust yourself more and follow your intuitions
  11. Stop eating fast-food :)
  12. Get outside the comfort zone
  13. Boldly show how you were right to others
  14. Overcome your fears
  15. Mastering yourself
  16. Appreciate people for who they are
  17. Dealing with negative people
  18. Enjoy the silence
  19. Have no regrets
  20. Life is about doing and just not saying
  21. Enjoy today more than yesterday

Life is like riding a bicycle. To keep your balance, you must keep moving ― Albert Einstein

Life is beautiful and fun if we regret less and chase our dreams. So do feel free to share me your list too. Does this resonate something in your mind?

Continue reading...


 

Is SQL datetime2/datetimeoffset conversion deterministic? May 28th, 2013

Vinod Kumar

Recently in a customer code review I came across some interesting logic about Datetime2 and DatetimeOffset datatype. Here is the problem statement for your reference:

Customer was trying to create a view that contains a computed column that converts a datetime2(7) column to a datetimeoffset(7) column. The CAST function looks like:

CAST((CONVERT(varchar(255), DATEADD(hh, +8, RawLog.[Created]),126) + ‘+08:00’) AS datetimeoffset(7)) AS [Created_GMTPlus8]

This returned them an error when a index was created on this view:

Cannot create index on view "MyVideo..RawLogView". The view contains a convert that is imprecise or non-deterministic.

So the customer was asking, how to make this deterministic? Would love to learn if you have any other option.

Possible Solution

Here are couple of possible solutions to this problem. The root reason is that the cast string to datetimeoffset(7) is not deterministic,  we need use convert with style 126.  Let me show how we can rewrite this query to achieve the same. Note, in below example Option 2, we use SWITCHOFFSET built-in to convert a datetime UTC values to a datetimeoffset, and then SWITCH the timezone to +08:00. Let us see the code now:

USE tempdb

GO 

— If it exists Drop it.

DROP TABLE rawlog

GO 

CREATE TABLE rawlog (id INT IDENTITY(1,1) PRIMARY KEY,

            created DATETIME2(7))

GO

INSERT INTO rawlog VALUES(SYSDATETIME())

GO

We have created our dummy table for use with a value. Let us check if the string conversion returns the same result.

— Make sure SWITHOFFSET return the same value as we expected

SELECT created,CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) CASTStringValue,

SWITCHOFFSET(CAST(created AS DATETIMEOFFSET),‘+08:00’)  AS result FROM dbo.rawlog

GO

Our output looks like:

image

Our values are exactly the same. Let us create the view next, and we will then check if it can be indexed as per our problem statement.

CREATE VIEW v1 WITH SCHEMABINDING AS

  SELECT CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) AS result

  FROM dbo.rawlog

GO

–return 0 because of the case string to datetimeoffset is not deterministic

SELECT OBJECTPROPERTY(object_id(‘v1’),‘IsIndexable’)

GO

Option 1:

Next let us try to use the proper Conversion function and check. First add the offset with DATEADD and then convert it into DATETIMEOFFSET(7).

CREATE VIEW v2 WITH SCHEMABINDING AS

  SELECT CONVERT(DATETIMEOFFSET(7), CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’,126) AS result

  FROM dbo.rawlog

GO

–return 1 because convert with 126 is deterministic

SELECT ObjectProperty(object_id(‘v2’),‘IsIndexable’)

GO

Option 2:

Other than using convert with 126 style, we can also use SWITCHOFFSET function which is also deterministic

CREATE VIEW v3 WITH SCHEMABINDING AS

  SELECT SWITCHOFFSET(CAST(created as datetimeoffset),‘+08:00’)  AS result FROM dbo.rawlog

GO

— Return 1 because SWITHCHOFFSET is deterministic

SELECT OBJECTPROPERTY(OBJECT_id(‘v2’),‘IsIndexable’)

GO

These are just two of the methods to achieve the goal. Do let me know if you use Indexed views in your code today? What are the scenarios you use them? Have you encountered any of these deterministic restrictions in your coding anytime? Do let me know, would love to learn from you too.

Continue reading...


 

Introduction to DAX in Excel May 27th, 2013

Vinod Kumar

DAX (Data Analysis Expression) was first introduced with PowerPivot as part of SQL Server 2008 R2 release. It is an expression language following the similar syntax of Excel formula, enabling Excel power users and BI developers the ability to write calculations inside PowerPivot workbook to extend PowerPivot’s analytical capability. DAX can be used to create a calculated column inside a PowerPivot table or a calculated measure dynamically evaluated inside the PivotTable against the PowerPivot data source. The details and implementation of this will be for future blog posts. In this blog post:

  1. Introduce DAX
  2. Enhancements with SQL Server 2012
  3. Functions available with DAX
  4. How are they different from Excel Functions
  5. How are they different from MDX functions

In V1 release of SQL Server 2008 R2, DAX supports functions which perform:

  1. Common mathematic and statistical functions, such as CEILING(), FLOOR(), ABS(), AVERAGE(), MIN(), MAX()
  2. Logical functions, such as IF(), TRUE(), FALSE()
  3. Informational functions, such as ISERROR(), ISBLANK()
  4. Date and Time functions, such as YEAR(), DATE(), MONTH()
  5. Filter function, such as FILTER()
  6. Functions that handle relationship of the tables, such as RELATED(), RELATEDTABLE()
  7. Functions that control the current context of the evaluation, such as VALUES(), EARLIER(), EARLIEST(), CALCULATE().
  8. Time Intelligence functions to allow analysis over time, such as PARALLELPERIOD(), CLOSINGBALANCEMONTH(), OPENNINGBALANCEMONTH()
What is new in SQL Server 2012

SQL Server 2012 has enhanced DAX functionality and removed some of the limitations from the V1 release to meet the following goals:

  1. Extend the functions available with SQL Server 2008 R2 release. These new functions not only expand the analytical capabilities, but also make authoring DAX calculations easier.
  2. Supports Power View report which uses BISM tabular database as the backend. This includes the new table queries, parameters and variables.

The list below offers a quick glance on the new functions in SQL Server 2012. The details on these functions will be covered in the later blogs.

Filter Functions
  1. HASONEFILTER
  2. HASONEVALUE
  3. ISCROSSFILTERED
  4. ISFILTERED
  5. ALLSELECTED
Informational Functions
  1. CUSTOMDATA
  2. CONTAINS
  3. LOOKUPVALUE

The following new functions are added to create parent child relationship: 

  1. PATH
  2. PATHCONTAINS
  3. PATHITEM
  4. PATHITEMREVERSE
  5. PATHLENGTH
  6. USERNAME
  7. USERELATIONSHIP
Logical Functions
  1. SWITCH
Math Functions
  1. CURRENCY
Statistical Functions
  1. ADDCOLUMNS
  2. CROSSJOIN
  3. DISTINCTCOUNT
  4. GENERATE
  5. GENERATEALL
  6. ROW
  7. STDEV.P
  8. STDEV.S
  9. STDEVX.P
  10. STDEVX.S
  11. SUMMARIZE
  12. TOPN
  13. VAR.P
  14. VAR.S
  15. VARX.P
  16. VARX.S
Basic syntax of DAX

A DAX expression always begins with an equal sign followed by the formula. The formula can contain constant values, DAX functions or the combination of the two. For instance,

= 1.1 * SUM(‘FactInternetSale'[SaleAmount])

= ‘Customer'[FName] & ‘, ‘ & ‘Customer'[LName]

All DAX functions share the common syntax as below,

DAXfunctionName(argument1, argument2, ….)

Depending on the function, argument can be a table expression (that returns a table), a column name or an explicit value. Table name in DAX needs to be enclosed by the single quotation ‘table name ‘ if the table name contains a white space between the characters, a special character or a reserved word. Column name must always be enclosed in []. It is not required to precede column name with the table name as in ‘FactSales'[SaleAmount], but it is highly recommended to fully qualify the column name to avoid any potential ambiguity when resolving a column name.

Data type supported in DAX

The data type for a column can be:

  1. Text
  2. Float
  3. Whole number
  4. Currency
  5. Date
  6. Boolean (True/False).
Difference between DAX and Excel formula

Even though DAX follows the same syntax convention as the Excel formula, there are fundamental differences between the two.

  1. DAX will only work with PowerPivot workbook and PivotTable created from the PowerPivot workbook. DAX cannot be consumed by regular non PowerPivot workbook or non PowerPivot PivotTable. Similarly, the regular Excel functions that are not implemented in DAX cannot be used in PowerPivot workbook or PowerPivot PivotTable.
  2. DAX is built upon the relational database concept, i.e. table and table relationship. Consequently, functions utilizing relationship provided in DAX. There are no such functionalities in Excel.
  3. DAX operates on entire table or entire column, i.e. DAX cannot operate on an individual cell, or a range/array of cells.
  4. To work with In Memory data, DAX has its own implementation on the common native Excel aggregation functions, such as SUM, AVERAGE, MIN, MAX, COUNT, which only takes column as the argument, unlike the native Excel functions which can take a list of ranges of cells as the function arguments.
  5. DAX has new aggregation functions that aggregates an expression over the rows of a table, such as SUMX, AVERAGEX, MINX, MAXX, COUNTX.
  6. DAX calculated measure provides dynamic aggregation based on the current filter context as defined by the members in the Row label, Column label, Value field, Slicers and Report Filter in the PivotTable.
  7. DAX provides Time Intelligence capabilities.
  8. DAX functions that returns a table can be used as the intermediate result to other DAX function that returns a single scalar value.
  9. The return data type may be different for the functions that share the same name in DAX and Excel, for instance, date and time functions return datetime data type in DAX. The same set of functions return integer in Excel.
  10. DAX supports several different data types such as Integer, Double, Datetime, String, Currency, Decimal, etc. In Excel, everything is either a number (double) or a string. Numbers are then formatted to appear as dates or as currency or with a fixed number of decimals. This use of formatting in Excel is different from having distinct data types as in PowerPivot.
Difference between DAX and MDX
  1. MDX is primarily a query language which asks for the specific data from the multi-dimensional Analysis Services database. MDX can also be used to create calculations in the traditional Analysis Services database, including calculated measures, calculated members, scope assignment, custom rollup etc.
  2. DAX is an expression language which allows Excel power user to create a calculated column in PowerPivot workbook or a calculated measure in Pivot table based on the PowerPivot workbook, using the familiar Excel formula syntax. DAX is not designed to replace MDX.
  3. MDX is based on multi-dimensional objects such as dimensions, measure groups, attributes, and hierarchies. MDX syntax includes notation for sets and tuples.
  4. DAX is based on relational constructs such as tables, columns, and relationships. DAX syntax is designed to mimic the syntax of Excel formulas.
  5. Inside PivotTable against PowerPivot data, Excel generates and sends MDX query to SSAS engine when user drags and drops members onto Row, Column, and Value fields.
  6. A DAX calculated measure or calculated column can only return one data type, while MDX treats measure as variant data type and one measure can return different data type based on the calculation condition. For example, inside the IF statement If (condition, expr1, expr2) in DAX, both expressions need to return the same data type, while in MDX, one expression can return a number and another returns a string.

Final Words

This is just a start to many more blog posts to come on DAX. Do let me know if you learnt something new today. I have seen customers ask these questions time and again when I show them Excel and DAX functionality. Thought this will be a dump of these conversations for reference to all. Thanks for reading this far.

Continue reading...


 

Windows Phone 8 features I love using May 23rd, 2013

Vinod Kumar

I was tracking the various hits made to this blog and I found one of the most sought after post was around the tip: Tip: Get “Me Tile” back on Windows Phone. Sometimes it is not that easy to discover new hidden features when it comes to Windows Phone. In this blog post, let me call out some of these hidden gems I discovered and love using daily.

This by any chance is not an exhaustive list but my top loved features which I felt is worth sharing. Do let me know if you explored and found anything interesting playing around with your Windows Phone 8.

PS: Some of these features will not be available on older version (pre-Windows Phone 8).

Kids Corner

Kids generally love smart phones. And the worst is they tend to dial out numbers, post over FB and what not. This is my favorite feature when showing off the Windows Phone. To access Kids Corner:

  1. Select “Settings” from Applications.
  2. Scroll down to “Kids Corner” option.
  3. Enable it using the slider.
  4. Add Applications to what has to be seen in Kids Corner.

To access Kids Corner, You can launch it from the above screen. Or When your Phone is in Locked mode, Just slide the screen left – the Kids Corner is launched.

Kids-Corner-Windows-Phone-8

Font Size

When my dad uses the phone, he prefers having larger fonts and I was pleasantly surprised to see this option. To access this option:

  1. Goto Application List.
  2. Select “Settings”.
  3. Check to “Ease of access”.
  4. Under “Text size”, use the slider to increase the size.
  5. See the sample text and increase the font size to your desire.

Interestingly, this is the same place where we can find “High Contrast” options too. Do try to play around with this.

Rooms and GROUps

This feature allows for creating your own private group to share photo’s, calendar, text messages and more. Have you ever explored this?

Instead of the steps, I am attaching a video for reference. I hope you will use this.

Attach more to messages

To add more to messages, Tap on messaging – select the small “Clip” at the bottom to attach a number of options. The screen comes up as below:

Windows-Phone-8-Attach-Options

If you have the appropriate data plan, this should work just fine.

Upload to SkyDrive

This is my favorite feature for me since Windows Phone 7. When you take pictures, allow it to automatically upload into Skydrive. To enable this:

  1. Goto Applications, select “Photo”.
  2. Select the more option “…” from the bottom.
  3. Select “Settings” on this screen.
  4. Under “Auto upload”, select “SkyDrive”

From now on, any photo or video you take will automagically gets stored onto your SkyDrive.

Text Reply to Phone call

Assume you were in an important meeting and wanted to

To configure pre-defined texts

  1. Under Applications, select “Phone”.
  2. Select the more button “…”
  3. Select “Settings”
  4. Enable “Text reply”.
  5. Click “edit replies”.
  6. Modify the replies to suit your needs.
  7. Next time when a call comes, select “text reply”, this pops up the stored relies. In single click it send the message to person who called. Simple, yet powerful.

So here were my Top 6 feature list I thought were worth sharing. There are many more like cloud backup, applications etc which I will keep it for a different post. If you are using Windows Phone 8 – what is your most used and loved feature?

Continue reading...


 

Less means a lot more May 21st, 2013

Vinod Kumar

Life is about living to its potential. As humans we need to discover and learn from each other. In this pursuit of a happy life, how often have you become a black-hole attitude person? What I mean by this is – how often you have looked at things around you and have gone about getting it in an instinct? These days the gadget freaks are a classic example. Even though the devices have incremental improvements, we tend to see people wanting it more than every. It seems to hit a social status for individuals. I did post a message about value we associate with things before – Picture Post: How we associate Value. It is a mindset, it is instinctive, it is spontaneous – but it is not well thought through.

Humility is not thinking less of yourself, it’s thinking of yourself less – C. S. Lewis

In this blog post, let me take a different dimension to call out few things that I fell are more powerful when less. These are my Top 10 –

  1. Doing less means you get time for things that you really dream of doing.
  2. Give lesser excuses.
  3. Lessen the time between making mistakes and apologizing.
  4. Doubt lesser and try to accept more.
    Generosity is giving more than you can, and pride is taking less than you need – Khalil Gibran

  5. Lessen the time or be immediate when you have to “Thank” someone.
  6. Know the red-lights – and lessen the number of times you try to bail out of a situation.
  7. Spend less time complaining.
  8. Lessen your convenience and do what is your responsibility.
    To give anything less than your best, is to sacrifice the gift – Steve Prefontaine

  9. Lesser your debt’s in life, more happy you will be.
  10. Try to agree more and argue less.

This is not exhaustive to everything that we see as less to being more. What are your list that you would like to add to this list?

As I grow older, I pay less attention to what men say. I just watch what they do – Andrew Carnegie

Continue reading...