Office 2013 Preview – Enabling PowerPivot in Excel

With the Office 2013 announcements today I was excited to download and get my hands on the tools.  Because this is still a customer preview we’re bound to find things here and there that aren’t functioning as expected when experimenting with the Office tools.

The things I had on my list to test first were Power View and PowerPivot from Excel (Yes, Power View is now available in Excel).  In this post I’ll focus on helping you get started with PowerPivot.

With Excel 2013 you no longer have to download PowerPivot as a separate add-in but you may find when exploring the PowerPivot tab that all the available buttons in the Office Ribbon are greyed out.  The problem is that while the add-in maybe installed it is not enabled.  To fix this click File->Options as shown below.

Go to the Add-Ins page.

Change the Manage property to COM Add-ins then select Go

Check Microsoft Office PowerPivot for Excel 2013 then click OK.  You may also notice PowerPivot for Excel as an option here if you had a previous version of PowerPivot installed.

You should now be able to start working with PowerPivot.  Hope this helps getting you started!

Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

• Reporting Services
• Excel
• PowerPivot
• PerformancePoint
• Power View

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

• Ad Hoc Reporting
• Content Sharing
• User Flexibility
• Developer Flexibility
• Time to Develop

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

http://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

Choosing the Right Microsoft Reporting Technology Part 3: PowerPivot

Today I’ll be continuing in a blog series designed to help you decide which presentation tool is best for your reporting needs.  So far I’ve discussed in previous posts:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)

You learned how these two tools are almost opposites of each other.  Reporting Services is generally thought of as a static reporting tool, while Excel is usually used for ad-hoc reporting.  The products still left to cover are:

• PowerPivot
• PerformancePoint
• Power View

Part three of the series will focus on PowerPivot.

PowerPivot

What it is

PowerPivot is much more powerful than any of the other reporting tools that will be discuss in this series because it does much more than just product reports.  While it does have the ability to create some impressive reports it really cannot create any addition visualization that regular Excel PivotTables can’t already do.

The real impressive part about PowerPivot is the modeling part of it.  With PowerPivot you actually design a modeling layer that brings in the different objects that you choose.  I purposely use the word objects generically here because you can import any data source that you can think of into PowerPivot.  Whether you need to bring in a table from SQL Server, a flat file or even something from DB2 it can all be done very easily with PowerPivot.  In fact, even if I needed to bring in all three of those objects into the same PowerPivot document and relate them to each other that is possible too.  Within the tool you have the ability to create logical relationships that may not exist on the source systems.  The view below shows how the new PowerPivot 2012 has improved the way relationships are designed.  Previously designing these relationships was done through a single dialog box instead of the new graphic diagram view shown here.

PowerPivot uses a fairly new in-memory technology called xVelocity (formally called Vertipaq) to handle all of the report processing requests.  With xVelocity Excel is able to process hundreds of millions of rows with amazing response times from a desktop machine. The xVelocity engine uses in-memory column-oriented storage and a highly compression data storage to produced the results you see today.

Another key thing to understand about PowerPivot is that the data pulled into PowerPivot is actually stored in the document an import is performed.  That allows it to use the store engine described earlier.  The one problem with this is that the data is static until manual update is kicked off.  When the update is run all of the data in the PowerPivot document is reloaded backed back in file.  Unfortunately, there is not incremental update yet so if you have a significantly large data source it may take a while update.  Later when I discuss how PowerPivot can be consumed I’ll talk about how SharePoint can assist in automating this data refresh process.  SharePoint is really the true way to scale out PowerPivot so other users can utilize the reports you build.

What it isn’t

One worry that many IT staffers feel about PowerPivot is that power users will begin creating these documents and making decisions off of them while10 other users have created simpler but different documents and get different results.  If you see something like this happening in your environment, which is completely possible, you could create a full Analysis Services solution (either tabular or multidimensional) to replace the PowerPivot documents to ensure all these users a consuming a single source for their reports.

It is also important to note that PowerPivot is not a replacement for any ETL that performs data cleansing or applies business rules.  Most data warehouses have a set of ETL processes that perform these tasks and that way all users are looking at the same data set.  PowerPivot does not eliminate the need for this.

Who Uses it

The goal is to have power users the driving force behind the creation of PowerPivot documents.  This would be a person that understands the source database(s), business needs, understands the concept database relationships and understands Excel all fairly well.

While that may be the goal for who should be using PowerPivot that is not what I actually see happening in the field.  I am still seeing most PowerPivot implementations being completely controlled by IT.

Over the last year I have seen this start to change so it does become more user driven but I think the problem that is preventing more users from getting their hands on the tool is education.  A lot of clients that I visit either don’t know what PowerPivot is or if they do they’ve never be taught (even in a simple demo) how it works.  So until this changes we may see a lot of PowerPivot solutions started in IT.

How is it consumed

The two ways PowerPivot can be consumed is the either directly through Excel or through SharePoint.  PowerPivot documents that are used directly through Excel rely completely on the machines resources they are viewed from.  So for example if I create and use a PowerPivot document on my laptop then it use all the resources of my laptop for importing data and processing results.  If I wanted others to see this report using this method I would have to either place the file on a shared drive or email it to those I want to view it.  That sounds terrible!

The best way to scale PowerPivot so that it is usable by a larger number of users is to setup SharePoint integration.  When PowerPivot for SharePoint is installed any reports viewed from a PowerPivot Gallery (SharePoint library for PowerPivot) run using a special Analysis Services instance to do all report processing rather than your laptops resources as previously described.  You may have noticed going through the SQL Server 2008 R2 install that there is an option to install SQL Server PowerPivot for SharePoint.  Using this SharePoint integration not only allows you to using Analysis Services for report processing but also allows you to schedule data refreshes, which is a huge help because normally data refreshes are a manual process without SharePoint.

Limitations

The major known limitations with PowerPivot are experienced when SharePoint is not part of the solution.  All of these have been detailed previously but as a reminder:

• Refreshing report data in PowerPivot is a manual process
• PowerPivot uses the resources of the machine the file is on
• Sharing a PowerPivot reports can be difficult for large documents (shared drive or email)

PowerPivot for SharePoint of course addresses each of these limitations.  One other limitation that I have not detailed yet is working on 64 bit vs 32 bit PowerPivot.  I highly recommend if you use PowerPivot that you only do it with the 64 bit version.  You will find out very quickly when you begin importing data sources into you document that without 64 bit the process can be slow and painful.  You might even run into some limitations with the amount of data it will import on a 32 bit instance.  With 64 bit PowerPivot the sky is the limits though!

Summary

As we go through this series remember these high level characteristics about PowerPivot:

• For power users that can understand a little modeling and relationships
• Can bring in multiple unrelated data source together for reporting
• SharePoint is the right way to make PowerPivot scalable

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PerformancePoint. To read any of the other parts to this series follow the links below.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

PowerPivot 2012 RTM Release

Somewhat overshadowed, for obvious reasons, by the big RTM release of SQL Server 2012 was the RTM release of PowerPivot 2012.  You can go download the much improved version of PowerPivot for Excel here.

http://www.microsoft.com/download/en/details.aspx?id=29074

To give you a fair warning there are some prerequisites to installing that were not required in the previous version of PowerPivot that you can find on the download site:

• Install .NET Framework 4.0 and Visual Studio 2010 Tools for Office Runtime.
• If you are running Windows Vista or Windows Server 2008, install the platform update.
• Install Microsoft® Office 2010.
• Install PowerPivot for Microsoft Excel

Also, here’s a few post I wrote several months ago that will help you learn the latest release:

• PowerPivot – Creating KPIs
• PowerPivot – Perspectives
• PowerPivot – Creating Hierarchies
• PowerPivot – Commonly Used DAX Expressions

PowerPivot – Commonly Used DAX Expressions

Today I will be presenting in a webinar, which by the way is recorded for later viewing, on Commonly used DAX Expressions.  You can register (or if you miss it live watch the recording) for this webinar here.  I thought it would be great if I could push out the code that I plan to show ahead of time so you could review it immediately following or even during the webinar.  Also, if I run out of time you can see what I planned to cover!

Some of these have a short description while others are self explanatory based on the name of the calculations.  Please keep in mind if you have problems building any of these that they do build off each other so make sure you do all of them in order!  I?m also using the AdventureWorksDW2008R2 database as my data source.

Calculated Columns
==================================================

Calculated Column on DimCustomer (Concatanate Fields)

=DimCustomer[FirstName]&" "&DimCustomer[LastName]

Calculated Column on DimPromotion (IsNULL equivalent)

=IF(DimPromotion[MaxQty]=Blank(),"No Max Required","Max Qty Required")

Calculated Column on FactInternetSales

=RELATED(DimSalesTerritory[SalesTerritoryRegion])

Calculated Column on DimsalesTerritory

=SUMX(RELATEDTABLE(FactInternetSales),FactInternetSales[SalesAmount])

Calculated Measures

==================================================

Calculated Measure on FactInternetSales [Profit]

=SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])

Calculated Measure on FactInternetSales [Profit Margin]

=FactInternetSales[Profit]/SUM(FactInternetSales[SalesAmount])

Calculated Measure on FactInternetSales [Customer Count] Used to get distinct count of customers

=DISTINCTCOUNT(FactInternetSales[CustomerKey])

Calculated Measure on FactInternetSales [Due Date Sales Amount] Used for role playing dimensions

=CALCULATE(SUM(FactInternetSales[SalesAmount]), 
USERELATIONSHIP(DimDate[DateKey], FactInternetSales[DueDateKey]))

Calculated Measures – Time Intelligence

==================================================

Calculated Measure on FactInternetSales [YTD Profit] Returns YTD Profit

=CALCULATE(FactInternetSales[Profit],DATESYTD(DimDate[FullDateAlternateKey]),ALL(DimDate))

Calculated Measure on FactInternetSales [Last Year YTD Profit]

=TOTALYTD(FactInternetSales[Profit],
DATEADD(DimDate[FullDateAlternateKey],-12,MONTH))

Calculated Measure on FactInternetSales [Rolling 12 Months Profit] Returns running total of measure

=CALCULATE(FactInternetSales[Profit], 
DATESBETWEEN(DimDate[FullDateAlternateKey],
DATEADD(FIRSTDATE(DimDate[FullDateAlternateKey]),-11, MONTH),
LASTDATE(DimDate[FullDateAlternateKey])))

Calculated Measure on FactInternetSales [Last Years Profit]

=CALCULATE(FactInternetSales[Profit], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

Great PowerPivot/DAX Resources

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-data-analysis-expressions-dax-language.aspx
http://www.powerpivotblog.nl/

http://cwebbbi.wordpress.com/
http://powerpivotgeek.com/
http://powerpivotpro.com/
http://powerpivot-info.com/

PowerPivot – Creating KPIs

Probably one of the most anticipated additions to the latest version of PowerPivot is the ability to create KPIs. PowerPivot has made what was once a task that was done purely done by developers now something that anyone can do now.

KPIs (Key Performance Indicator) are an executive favorite that help provide a quick at a glance look at how one area of business is doing. Typically they output some kind of indicator like a red, yellow, green light of some kind that can tell the report viewer quickly if a goal is being reached.

To create a KPI you must first create at least two calculated measures. The first one being the actual values and the second being the target or goal values the KPI is attempting to reach. In my example I?ve already created two DAX calculations to show profit and last year?s profit.

Profit:

=SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])

ProfitLastYear:

=([Profit])(SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

Now the simple part is creating the KPI. Select the measure that the KPI will be based on, in my case that is Profit, then in the PowerPivot tab click Create KPI. 

This will open the Key Performance Indicator dialog box. The KPI base measure (value) should already be filled in for you with the measure you had selected when selecting Create KPI. Under Measure select the field the KPI should compare against. Optionally, you could provide an absolute value that could always be used in comparing. I want to try to beat last years sales so I select ProfitLastYear. The ranges on what determines if a value is in the red, yellow, or green is adjustable by either dragging or typing the percent you want. Notice the number of ranges and indicator types can easily be changed here as well.

 

After you hit OK the KPI is done! It?s automatically added to your report and your results would look something like this:

You can always adjust the KPI by hitting the Edit KPI Settings button in the ribbon. You can also create KPIs from the PowerPivot window in the Measure Grid.

Read my previous PowerPivot blog posts:

• PowerPivot - Persepectives
• PowerPivot - Creating Hierarchies
• PowerPivot - Diagram View
• PowerPivot - Measure Grid

PowerPivot – Perspectives

Many times when developing larger Analysis Services cubes you may find it necessary to use Perspectives to logically split up your cube so users can find what they want faster.  A perspective is similar to a TSQL view because it will take a larger object (the cube) and filter out objects that you want to split up.  It cannot however perform business logic or filtering of data like a view can.  So the case for perspectives would be if you have a cube for your entire company but you don’t want HR to have to filter through sales data to find what they need, and you don’t want the sales team to have to filter through HR data.  Having perspectives would save the user valuable time in finding the data they need without having to sift through other’s data.

**WARNING** Perspectives are not for security.  It is only a usability feature and does not prevent a user to see the entire cube.

Now that you have a little background on what Perspectives are in Analysis Services let’s talk about how you can now use them in PowerPivot.  When creating PowerPivot reports Perspectives appear similar to Measure Group selections appear in Analysis Services.  You will find a dropdown box that allows you to select the desired perspective, assuming you have already created one.  If you have not created one or more perspectives then the dropdown box is not visible.

To create a perspective open the PowerPivot window.  I’m going to assume you know how to get started with PowerPivot and not describe connecting to a data source and importing tables.  Select the Advanced tab in the Office Ribbon.  What you don’t see an Advanced tab?  That’s because it’s hidden.  Remember PowerPivot is intended for end user development so some advanced functions are hidden.  To unhide Advanced Mode click on the file menu in the top left of the window and select Switch to Advanced Mode.  This will give you the new Advanced tab.

Navigate to the Advanced tab and click the Perspectives button.

The Perspectives dialog box will open and you can now create a new perspective by clicking the New Perspective button.  When the new perspective is creating you will place checkboxes next to each object you wish to include.  You can select entire tables or just certain columns from table.  The buttons above the new perspective from left to right are.  Delete, Rename, and Copy which will allow you to copy what is in one perspective to a new one.

After you hit OK the perspective is done and you can use it when creating PowerPivot reports in Excel.  Remember the perspective will appear in the PowerPivot Field List as a dropdown box like this:

Read some of my previous PowerPivot Denali Blogs:

• PowerPivot – Creating Hierarchies
• PowerPivot – Diagram View
• PowerPivot – Measure Grid

PowerPivot – Creating Hierarchies

Hierarchies are one of the long awaited features that will now be available in the latest version of PowerPivot. Hierarchies are useful because it allows a single click for a user to bring in all the fields that exist in a natural table hierarchy.  Normally the user would have to drag in all fields individually so this can be a time saver especially if it is an 8 level hierarchy.

Hierarchies are very simple to create and easy to interact with in the reporting layer as well.  To get started you must be in the Diagram View of the PowerPivot Window.  Find the table you wish to create the hierarchy on and either right-click on it or click the Create Hierarchy button shown below.

After the hierarchy is created you will immediately be able to rename it.  After renaming it you drag the fields from the table down to the hierarchy that was just created.  You can also rename the fields so they are more user friendly by right-clicking on each individual field.  Here I’ve renamed my hierarchy and all my fields to something my users are more familiar with.

Let’s take a look at some of the options you are given when right-clicking on fields:

• Hide Source Column Name:  Removes the source column name that appears in parenthesis next to the column name that you have in the hierarchy.  This only removes it from the diagram view because the source column is never shown in the reporting layer.
• Move Up:  Moves a field to a higher level in the hierarchy.
• Move Down:  Moves a field to a lower level in the hierarchy.
• Remove from Hierarchy:  Takes to field completely out of the hierarchy.
• Rename:  Allows you to rename a hierarchy field to something more user friendly.

When you are happy with the hierarchy you have created you can use it in the reporting layer of excel.  The hierarchy interacts with the pivot table just like it would if it had come from an Analysis Services cube.

PowerPivot – Diagram View

The new “Denali” PowerPivot has a lot of fanatic new additions that I’ve really been looking forward to.  Many of my biggest complaints from the first version of the product have been addressed.  One of these complaints was the lack of a visual representation of object relationships.  You had to rely on the little window shown below (the Active column is new) to create and manage relationships.

In the latest version of PowerPivot you will now have the ability to view these relationships in a diagram view as shown below:

To change to the diagram view simply click the Diagram View button in the Office Ribbon or in the bottom right of the tool inside PowerPivot.

Now this may seem like a simple add but there’s a lot more that comes with the diagram view.  For example notice that in my diagram screenshot that DimDate has 3 relationships to the Fact table.  Yes, that’s right you can now use role playing dimensions inside PowerPivot, which was previously not possible.  I’ll devote a future blog post to that topic because it’s actually fairly lengthy to describe (this is where the new Active column in the manage relationships window comes into play).

You can also create new and manage existing relationships in the diagram view with a drag and drop interface.  By clicking and dragging your foreign key on top of the corresponding primary key the relationship will be created for you.  A much nicer interface than the prior version of PowerPivot!

In the top left of the diagram you can filter the objects Columns, Measures, Hierarchies and KPIs.  This makes it much easier to find object that you’ve built.  Did I say hierarchies and KPIs?  This is also new to PowerPivot that will require its own write up.  In the top right you will find a navigation window, which will allow you to resize and move more freely through larger diagrams.

If you right-click on any of the tables from inside the Diagram View you will find a few more options exposed.

Some of these options are obvious what they do but some are new so I’ll detail them all:

• Delete – Deletes the object from the model
• Create Relationship – Defines a new relationship between objects (without drag and drop).  You’ll generally select this option from the table that stores the foreign key.
• Create Hierarchy – Very exciting!  You can now do hierarchies in PowerPivot.  I’ll write details later on how this works but these are similar to user defined hierarchies that you may have built in Analysis Services.  These are very simple to create and work great!
• Hide from Client Tools – Just hides the table from the PowerPivot Field List so users cannot use it.
• Go To – Opens up the traditional PowerPivot grid view of the data in the table you’ve selected.
• Rename – Renames the table.
• Maximize – This is a nice usability feature that enlarges the table to a near full screen view, making it easier to manipulate.  Below is what the maximize view looks like this:

I love this change I think it helps a lot as far as the usability of managing relationships but it’s also opened the door for new functionality like hierarchies.

PowerPivot – Measure Grid

There are a lot of fantastic new features in the latest CTP 3 release of PowerPivot, which you can download here.  This is a huge change for PowerPivot and really shows how the product is maturing into become a tool that can solve a variety of problems that it could not handle in its initial release last year.  You can read up on all the change that in the latest release of PowerPivot here, but I thought I would write a series of blogs detailing each individually.

In this post I’d like to introduce you to the new Measure Grid object.  The Measure Grid provides you a new way for creating Calculated Measures for your PowerPivot reports.  Previously, anytime you wanted to create a Calculated Measure you had to be in the PivotTable Field List inside of Excel.  You would right-click on the table that you wanted the measure and write the DAX formula to create the calculation.  While this method is still available (and even has a few improvements of its own) today I want to focus on the new method for creating Calculated Measures.

The Measure Grid is different in that you will find it back in the PowerPivot window instead of your Excel PivotTable Field List.  To access it you simply click the Measure Grid icon in the Home ribbon.

This will open the grid where you can begin to write your measure formula.  The DAX statement written here is exactly the same as it was done previously but you provide the measure name in front of the formula like so:

Profit:=SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])

This will add the unformatted results of your formula into the Measure Grid within a single cell.  To fix things like formatting you simply right click in the Measure Grid and apply the appropriate formatting after clicking Format.  You can also delete the measure or provide a description of the measures intent for other users.

As you can see the Measure Grid can also create KPIs but I’ll save that for another blog because it will require a much lengthier description.  While this new PowerPivot may seem like a minimal change I think it’s a great change because it allows the developer of the PowerPivot model to segment out his/her thinking.  The PowerPivot window can now be used for all modeling and calculations and Excel can be just for building reports.  Like I said this does not prevent you from still creating measures in Excel as well like you would have done previously in PowerPivot.