Creating Real World PowerPivot Models Recording and Q&A

Thanks everyone for attending my free webinar on Creating Real World PowerPivot Models on June, 18, 2013.  If you were not able join for the live event you can now watch the recording here.

In this webinar we built out a live solution with the Pragmatic Works Marketing Director, Rachel, to prove PowerPivot is a very capable End User tool.  Using the marketing data she collects we built a Self-Service BI solution entirely in PowerPivot and finished with a quick Power View map report with Excel 2013.

This was part of an ongoing webinar series by Pragmatic Works offers every Tuesday and Thursday at 11:00 AM EST.

I’ve started to write follow up posts to these webinars because we always get great questions but there’s no way I could answer them all during the time allotted.  Here’s some of the questions I wasn’t able to get to and answers for them.

Q: Is it possible to add a table once you’ve already completed the first import?

Yes, fortunately it’s very easy to add new tables as requirements change into PowerPivot.  This is done the same way we demonstrated the initial import.

Q: If I create a hierarchy in the Date Dim, can it be re-used in my next project?

The only thing that is close to this is you can use one PowerPivot workbook as the data source in another PowerPivot workbook but unfortunately any hierarchies that are created will not carry over to the new model.  They would have to be recreated but luckily that is a very quick process with PowerPivot.

Q: I love it.  We have lots of SQL views we have created for reporting.  Do we really want the information consumer to define this or should we be using our views as our PowerPivot definition?

The information consumer or really a Power User would be the one to define all of what we showed during the webinar.  I say Power User because it requires a little bit of knowledge about table relationships.  Once that Power User defines the model he/she can share it will the information consumers for simply reporting on with either Excel PivotTables or Power View.

Q: How is the PowerPivot model you just created different from building a Tabular Model in SQL 2012 SSAS?

The way they are designed is very similar.  In fact, when you create a new Tabular model you can import the design from a PowerPivot workbook.  The big differences have to do with scalability.  PowerPivot relies on the resource of your machine or desktop to pull in and process data.  Tabular uses the resources of a server instance which hopefully is a beefier machine then your laptop.  Scalability of PowerPivot can also be done with PowerPivot for SharePoint.  Tabular also give you the ability to create partitions to help performance, apply row level security and query down to the underlying data source using DirectQuery.

Q: Is Power View possible on SharePoint 2010 and SQL Server 2012 and Office 2013 ?

Yes, there’s two ways you can do Power View now.  The easiest way is Excel 2013, which has Power View built into it and doesn’t require any additional install.  The other way to do Power View is SharePoint 2010 SP1 or higher (SharePoint 2013) and SQL Server 2012.

Again, thanks for joining me for the webinar and hope to see you in a future one!

Power View Reporting on Multidimensional Cubes Released

I’m a little late to the celebration but on Friday some there was some exciting news regarding Power View was announced. You can read about the details of it at the Analysis Services and PowerPivot team blog for Microsoft here.

http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx

This is part of a SQL Server Cumulative update pack that you can download here.

http://support.microsoft.com/kb/2833645

One thing to probably make mention of that you can ready on the Microsoft team blog is that this is only for SQL Server and SharePoint Power View.  If you’re using Power View with Excel 2013 only then you’ll have to wait a little longer for an update.

Either way this is great news and for those that were anticipating projects to convert Multidimensional cubes to Tabular or PowerPivot so they could use Power View.

Analysis Services Webinar Recording and Q&A

I hope you were able to attend my free webinar on Introduction to Analysis Services on April 2, 2013.  If you weren’t you can now download the recording here

Because I covered new material all the way to the end of the webinar i thought I’d also answer some of the top questions I didn’t have time to answer here.

Q: Is it true that not all versions of Excel 2013 support PoverPivot.  From what I read you need Office 2013 "Pro Plus".

Here is a great post by Rob Collie that answers this question http://www.powerpivotpro.com/2013/02/hey-who-moved-my-powerpivot-2013-cheese/.

Q: Does Analysis services has write back capability?

The only version that currently supports writeback is Multidimensional Analysis Services.

Q: Could you give a link to the VS 2012 update for theses tools to develop against sql 2012

Here’s the link to the download for Visual Studio 2012 SSDT  http://www.microsoft.com/en-us/download/details.aspx?id=36843

Q: Can Analysis Services Tabular be run within SSRS or do we need to use SharePoint?

This ones is kind of a two part answer.  Yes, you can use Analysis Services Tabular as a data source in an SSRS report and you can also use Reporting Services reports as a data source in PowerPivot and Tabular.

image

Q: We have a number of cubes that users access via Excel. They would like to be able to add simple custom calculations.  Can they add there own calculations when connected to a SSAS cube via power pivot?

Yes, a user can create their own calculations in PowerPivot call Calculated Measures using DAX.  This can be done against any data source including SSAS Multidimensional.

Q: Do relationships have to be defined in the source database?

No but it certainly helps.  If relationships are defined in the source they can usually (depending on the type of data source) be carried over to the model you create.  If there are no relationships then you must define them in your model.

Getting Started with Microsoft Data Explorer

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from http://www.microsoft.com/en-us/download/details.aspx?id=36803.

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

image

Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.

image

Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.

image

Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx.

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

  1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
  2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.image
  3. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.image
  4. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.image
  5. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
  6. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.image
  7. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.image
  8. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.image
  9. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
  10. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:image

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

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.

image

Go to the Add-Ins page.

image

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

image

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.

image

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

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:

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.

image

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.

image

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.

f0801

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.

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:

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

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. 

clip_image002

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.

 clip_image004

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

clip_image006

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 – 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:

Follow

Get every new post delivered to your Inbox.

Join 29 other followers