Twitter Updates

    follow me on Twitter

    Wednesday, December 07, 2011

    PowerPivot Nuggets - Part 13 - Save PowerPivot workbook as Analysis Services Cube

    Consider the following scenario's:

    • A business person created a PowerPivot workbook, but is struggling with some more advanced DAX.
    • A BI consultant created a protype of a cube in PowerPivot
    • A PowerPivot workbook becomes very popular and needs to be rolled out at the enterprise level.  We need to add security, partions, perspecitves ...

    Wouldn't it be nice if we had an option like "File - Save As Analysis Services Cube".

    Well, the title of this nugget is a little misleading.  You won't find "File - Save As Analysis Services Cube" in PowerPivot for Excel.  However there are  "Import from PowerPivot" possibilities.

    First one is in Visual Studio (SQL Data Tools).  Choose, File, New Project, Analysis Services.  And there you have an option to import a PowerPivot workbook.

    Second option is via SQL Server Management Studio.  Connect to a SSAS instance running in tabular mode and Choose "Restore From PowerPivot" from the object explorer.



    Personally I think this is one of the killer features of PowerPivot in SQL Server 2012.  The possibility to easily "upgrade" and enchance an existing PowerPivot workbook and turn it into a full blown Analysis Services cube will dramatically change the we way how we develop and implement BI projects in the future.


    Friday, November 25, 2011

    PowerPivot Nuggets - Part 12 - The Measure Grid

    In PowerPivot v1 calculated columns were created in the PowerPivot window and calculated measures were created in the PowerPivot Field list interface in Excel. In v2 you can now also create calculated measures in the PowerPivot window by using the new measure grid.



    You cannot only create measures in the measure grid, but PowerPivot will actually calculate the measure. Another cool thing is the fact that the measure is recalculated when you start filtering the table, as you can see in the screenshot below (filtered on Clothing).

    Monday, November 21, 2011

    PowerPivot Nuggets - Part 11 - Add Values to Rows and Columns

    While reviewing the session scores of SQL Server Days I ran into a very nice new feature in PowerPivot v2. You can now add values to rows and columns. This allows you to do some very nice analysis very fast.

    I just wanted so know how many sessions scored a 10, and how many scored a 9, .....

    By simply dragging the score value to the rows area I got the following results.

    Friday, August 12, 2011

    PowerPivot Nuggets - Part 10 - KPIs

    KPIs are another Analysis Services concept coming to PowerPivot. Creating KPIs is pretty straight forward.

    In the example below I have table with Sales and Costs. Let's say we want to have a 5% margin.

    KPIs in PowerPivot are based on a meusure. So, first thing to do is create a measure to calculate the margin.


    Step 2 is setting the KPI target values. Target values can again be based on a measure or as in this simple example on a fixed value.


    Step 3 is setting the tresholds and finally we choose an icon style.

    And that's it, our KPI is ready to use!

    Thursday, August 11, 2011

    PowerPivot Nuggets - Part 9 - Descriptions

    After the long PowerPivot Nugget on Parent Child relations a very short nugget today.

    PowerPivot v2 allows you to add descriptions to various objects. Explore the user interface and you will discover that you can add descriptions on Tables, Columns, Calculated measures, KPIs, ....

    A nice little new feature that will make your models easier to understand for other users.

    Tuesday, August 09, 2011

    PowerPivot Nuggets - Part 8 - Parent Child Relations

    Parent Child hierarchies like organization hierarchies and account structures are very common in reporting environments. PowerPivot V2 ships with a set of new DAX functions which allows us to leverage this in PowerPivot.

    Let's have a look at our dimEmployee table. This table has a parent-child relations (ParentEmployeeKey --> EmployeeKey). As you can see in the screenshot below; you cannot create a relationship between the two columns in PowerPivot. Just like with roll-playing dimensions PowerPivot uses a different approach than Analysis Services.



    In PowerPivot Parent Child relatinships are managed through DAX. Let's focus on employee John Campbell and play around with some DAX functions.



    First function is PATH. PATH returns a delimited text string with the EmployeeKey of all the parents of the current EmployeeKey, starting with the earliest and continuing until the current.

    =PATH([EmployeeKey],[ParentEmployeeKey])

    John Campbell's manager is Peter Krebs, and Peter Kreb's manager is Ken Sánchez. If we look at the corresponding Employekeys this results in 112|23|20.



    Second function is PATHLENGTH(). PATHLENGTH(PATH([EmployeeKey],[ParentEmployeeKey])) will return the length of the path. In John Campbell's case this is 3.



    With PATHCONTAINS() we can check if a particular key exists in the path.

    =PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),23) will return TRUE if EmployeeKey 23 is found anyware in the levels above the current Employee. In the case of John Campbell this will return TRUE.

    PATHITEM AND PATHITEMREVERSE allows us to pick out a particular level in the hierarchy.



    PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the top. PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the bottom.

    In the case of John Campbell this is two times 23.

    The last function we need to peek at is LOOKUPVALUE which allows us to lookup a particular column value for a given search criteria. The following DAX statement will return the manager's name.

    =LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2))

    For John Campbell this is Peter Krebs.



    If we now combine LOOKUPVALUE, PATH, and PATHITEM we can turn this parent-child dimensions into a natural hierarchy by creating a column for each level:

    =LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),1))
    =LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2))



    This approach is very similar to the concept used by BIDSHelper's Parent-Child Dimension Naturalizer for Analysis Services.

    Next step is to create a hierarchy in the diagram view and create a pivottable.


    This nugget was written on CTP3. One thing that is missing here is an easy way to hide the "empty levels". E.g. Peter Krebs is on level 2, so level 3, 4, 5, .. are empty for Peter Krebs.
    In Analysis Services we solve this by setting the HideMemberIf property. Alberto Ferrari blogged a workaround for this in his post on Parent Child relationships. Alberto also opened an item on Microsoft Connect. Let's vote for this feature request and hope it makes it in the RTM version.

    The longest nugget so far on a very cool BI concept coming to PowerPivot.


    Monday, August 08, 2011

    PowerPivot Nuggets - Part 7 - Multiple Relationships

    Let's dive a little deeper into relationships today. In PowerPivot V1 we could only create one relationship between two tables. Good news coming to V2. We can now create multiple relationships between tables.

    Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.



    When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.

    CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))



    This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.

    Wednesday, August 03, 2011

    PowerPivot Nuggets - Part 6 - Sort By Column

    In PowerPivot V1 members in a slicer are sorted in alphabetic order by default. This is fine for product descriptions or customer names. But not for months or weekdays.



    The workaround in v1 was to add a number before the weekday as you can see on the right hand slicer in the above screenshot. It works, but it is - let's say' - a little ugly.

    In PowerPivot V2 we can specify a "Sort By Column".


    And this is the result:

    A small but nice enhancement.

    Tuesday, August 02, 2011

    PowerPivot Nuggets - Part 5 - Mark As Date Table

    PowerPivot is bulk loaded with Time Intelligence functions. Those functions make it very easy to do data calculations like Y-1, YTD, MTD, .... In V1 however, you needed to follow the golden rules as discussed in this blogpost by Kasper de Jonge. If you didn't, you ended with wrong results in DAX calculations.

    In a typical business scenario for PowerPivot we combine data coming from a relational datawarehouse with other data sources. One of the best practices when building relational datawarehouses is the use of surrogate integer keys. The primary key of our date dimension will be a integer column as well as the foreign keys in the fact table).

    This breaks of course rule 5. Make sure that relationships are based on a datetime column (and NOT based on another artificial key column).


    In V2 we no longer need to workaround this issue. We can mark a table as a Date Table. Once we do this time intelligence functions will work out fine, even if the relationship between the fact table and time dimensions is based on an integer colum.

    In the screenshot below you can see how a simple YTD calculation is wrong simply because the relationship between the two tables is based on an integer column.

    After marking DimTime as Date Table the calculations run just fine.


    In the mean time Kasper has blogged new Golden rules for PowerPivot V2.

    1. Never use the datetime column from the fact table in time functions.

    2. Always create a separate Date table.

    3. Make sure your date table has a continues date range

    4. Create relationships between fact tables and the Date table.

    5. The datetime column in the Date table should be at day granularity (without fractions of a day).

    6. Mark the Date table as a Date Table in PowerPivot and set the Date column.

    Monday, August 01, 2011

    PowerPivot Nuggets - Part 4 - Perspectives

    Perspectives, like drill-through, are another Analysis Services feature coming to PowerPivot.

    Perspectives allow you to expose a subset of your model to the end users. Let's say you created a PowerPivot model on Sales, Purchase and Inventory with 15 dimensions, each with about 10 attributes and a total of +50 measures. This model can be a little bit overwhelming for e.g. the Sales Manger.

    We can now create a perspectives that contains only the most important attributes and measures related to Sales and expose that to the Sales Manager.

    Before you can create perspectives, you must switch to PowerPivot in advanced mode.

    Next you choose Perspectives in the Advanced tab, and start creating your perspectives.

    In the Pivot table view in Excel you can choose the perspective from the top drop down-box in the PowerPivot Field List.

    Another nice Analysis Services (Enterprise!!) feature coming to PowerPivot.

    Friday, July 29, 2011

    PowerPivot Nuggets - Part 3 - Drill-through

    Analysis Services, PowerPivot's older and bigger brother, has had drill-through functionality for quite some time now. Basically this feature allows you to drill-through a cube and go to the underlying datasoure and fetch the detail rows.

    This feature is now coming to PowerPivot. Right click on a measure and choose "Show Details". Or simply double-click on a measure.

    Excel will open up a new sheet and fetch (by default the first 1000 rows) of the underlying table.


    You can change the number of rows to fetch in the connection properties of your PowerPivot Data Connection.

    A nice new feature that will make data analysis a lot easier.

    Thursday, July 28, 2011

    PowerPivot Nuggets - Part 2 - Hierarchies

    About two years ago I opened the following item on Microsoft Connect.

    "Dimension Hierarchies (eg. Category - SubCategory - Product) are a key element for making cube concepts easier to understand and use for a lot of business people.

    I didn't find a way to create hierarchies in the current CTP of Gemini. I hope this will make it in future CTPs and the final relase."

    Hierarchies didn't make in PowerPivot V1 but they are here now on V2.

    As highlighted in Nugget 1, hierarchies are created in the Diagram View.







    Once created you can use them in your pivot table in the row and colum areas. Another cool thing is that you can also drag a hierarchy to the vertical or horizontal slicer areas. Excel will automatically create a slicer for every level in the hierarchy.





    Cool stuff!

    Wednesday, July 27, 2011

    PowerPivot Nuggets - Part 1 - The Diagram View

    "Relationships are the most important thing to understand in PowerPivot". It's one of the first things I teach my students in every single PowerPivot training. Missing or incorrect relationships will lead to incorrect DAX calculations and wrong results in your pivot tables. The funny thing in V1 was that you needed to create and debug relationships in a tiny message box with only 4 drop down boxes.

    The good news in V2 is that PowerPivot ships with a great diagram view.





    The diagram view allows you to
    • get a clear ERD like view of your model and navigate trough it
    • create and manage your relationships
    • rename tables and columns
    • Create hierarchies
    • ...
    A long anticipated feature and a very nice addition to the tool.

    Tuesday, July 26, 2011

    Announcing PowerPivot Nuggets

    I have been working with PowerPivot for about two years now. I still think it is one of the most fascinating pieces of software that Microsoft has ever released. PowerPivot will dramatically change the way we do business intelligence projects in the coming years.

    Earlier this month Microsoft released SQL Server Denali CTP3 to the public. Along with the CTP came a new (CTP) version of PowerPivot. This public release is great news because we can finally blog and talk about the cool stuff coming to the next release of PowerPivot.

    So what am I announcing today? PowerPivot Nuggets. In the coming weeks I will blog almost daily about one new feature in PowerPivot Denali. No long in-depth articles, but just small nuggets highlighting one new feature.

    First nugget is coming tomorrow on the Datagram view.
    Enjoy!