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:

Follow

Get every new post delivered to your Inbox.

Join 37 other followers