Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for the ‘PowerPivot’ Category

Sorting PowerPivot Labels Manually

Posted by denglishbi on June 23, 2010

One of the items that you are going to run into is the sorting order of items in PowerPivot.  For the most part you might get by with items being in ascending or descending order, but there are going to be times when a more manual approach might be needed.  One particular case would be the order to calendar month labels in a report.  Initially it would be displayed in alphabetical order like the following:

image

So how can you get this in the order that you are used to seeing the months displayed?  As you can see this is not how you want it and it isn’t like Analysis Services where you can define an additional column to sort the data by.  One option would be to have an additional sort order column that you could concatenate with the month label like the posting here by the PowerPivot Team – Sorting month by natural sort order.  Another option is to use the manual sorting functionality that is available.  How does that work?  It isn’t extremely obvious and after a bit of playing around I finally figured it out.  Here is how you can do it:

1. Right-click the label and select Sort and then More Sort Options…

   image

2.  In the Sort dialog box select the Manual (you can drag items to rearrange them) option and then click OK

   image

3.  Now in the PivotTable select an item and place the cursor towards the right-hand side of the cell until you get the arrows displaying in all four directions

   image

4.  Now click on that cell and drag it to the desired spot where you would like it to be displayed

   image

5.  Then drop the item and now you have not only moved the item with this display, but everywhere it is being displayed in the PivotTable

   image

6. Now simply repeat the process until everything is in the proper order that you want for reporting.  Note: this is just for this PivotTable.

   image

There you go.  Just another option that you have for sorting items in your PowerPivot reports.

UPDATE (11/22/2010) – I have another blog posting here in regards to sorting that you might find interesting as well – Sorting PowerPivot Labels Continued

Posted in PowerPivot | Tagged: | 6 Comments »

PowerPivot Relationship Detection Experience

Posted by denglishbi on June 23, 2010

I just wanted to put together some notes and feedback with the relationship detection feature in PowerPivot.  I was working through the samples in the recently published Professional Microsoft PowerPivot for Excel and SharePoint book and had run into an issue with loading the data PowerPivot Memory Error – Not Enough Storage.  This caused me to have to import the last fact table, FactSalesQuota, separately.  When I did this none of the foreign key relationships were formed on import into PowerPivot and I realized this when I started to do the first PivotTable example and saw that the numbers for Sales Amount Quota were not being reflected properly:

image

Since the relationship Detection is turned on by default in the PowerPivot tab in the Ribbon:

image

I was prompted at the top of the Field List in the Excel worksheet that ‘Relationship may be needed’ and I can click on the Create button to have PowerPivot automatically try and detection and create relationships between the tables that are currently being used in the PivotTable analysis.

image

So I go ahead and run this option

image

And unfortunately it was not successful

image

So I have to go back into the PowerPivot window and manually create the relationship

image image

And when I go back into the Excel worksheet with the PivotTable I get a new message above the Field List stating ‘PowerPivot data was modified’ along with a button to Refresh.

image

After click on that the data in the PivotTable is updated and now I get the correct view of the data (minus the proper sorting of the calendar months, but that will be resolved in a later post):

image

So why didn’t the auto detection wizard work? This seems like an extremely easy detection since the column names have the same name minus the case – in the Date dimension the primary key is Datekey and in the Sales Quota fact table the foreign key DateKey (see the Create Relationship screenshot above) .  You will notice that in the Contoso Retail DW database not all of the columns have camel casing setup the same.  I thought this might be the issue, but I tried the same scenario with the Channel dimension with the Sales fact table

image

 image

and that was successful – in the Channel dimension the primary key is ChannelKey and in the Sales fact table the foreign key is channelKey.  Since this worked properly I would have to say that the issue then is with the data type.  I did find it really odd that the primary key for the Date dimension was a datetime data type

image

so as of now I would have to say that the auto relationship detection feature will not work with datetime fields.  If the fact table would have imported into PowerPivot the first time without the storage errors then the relationship would have been created automatically because of the relationships and constraints that are defined in the underlying database.  In my case though they were not and I was forced to create them manually.

If we hop over to the ever famous Adventure Works DW database and do a similar detection with the Date dimension and one of the fact tables it does work

image

image

image

but in this case the big difference is that this database uses a int data type versus the datetime like the new Contoso sample uses

image

I am assuming that they went with the datetime data type to simulate more real world data that an end-user might receive in a data feed or source extraction.  As long as the end-user knows how to create manual relationships this shouldn’t be a show stopping issue, just something to be aware of when working with different data types.

The other thing to note was that once I created the relationship manually in the Contoso example and later deleted it to try and perform some additional relationship testing, I was unable to get the relationship detection wizard to reappear.  It would be nice if you could some how force this to appear instead of having it magically display above the Field List.  It is just a wizard, so I would expect an option in the Detection portion of the PowerPivot Ribbon to be able to manually call this feature.  Well, this is version 1, so maybe in the next release this data type issue will be resolved and a new option will be available for detection.

If you think that this appears to be an issue and should be worked on please vote here (I posted as a suggestion instead of a bug, wasn’t sure what the proper classification was for this) – PowerPivot DateTime Relationship Detection.  Thanks for voting!

Posted in PowerPivot | Tagged: | Leave a Comment »

Where did my PowerPivot Excel 2010 tab go?

Posted by denglishbi on June 21, 2010

The other day I launched Excel 2010 to do a quick calculation and changed my mind.  By changing my mind I didn’t mean that I didn’t want to perform the calculation anymore, just decided I didn’t want to wait for Excel 2010 to load.  The reason that it takes a while now to load is the additional add-ins that I have installed which includes PowerPivot.  As long as theses are activated they need to be loaded each time.  Needless to say I am somewhat impatient and decided I would simply use another application to perform the calculation so I cancelled Excel during the load process.

image

It is an option, not sure if it is the right one that you want to pick, but needless to say this is what I went with and this caused some other message boxes to be displayed.  And at this point I simply pick the option to close or cancel because once again I am a bit rather impatient sometimes.

image

image

So once that was done I open up my handy calculator and do the original calculation that I wanted.  Later on I go ahead and open up Excel 2010 because I want to actually do some PowerPivot work.  Well…where did my PowerPivot tab go?

image

What did I do?  Oh no, I lost my PowerPivot.  Did I corrupt it?  Do I need to re-install the add-in for Excel?  Help!  I decided to do a quick Bing search on the issue and I come across a posting by the one and only Dave Wickert, PowerPivotGeek, PowerPivot for Excel add-in is not coming up in Excel.  This tells me that maybe I need to activate the COM Add-in.  Well the only thing it doesn’t tell me is where to do that…minor detail.  First I need to find where the add-ins are even displayed.

  1. Click File—>Options and then select the Add-Ins page
  2. The in the Add-Ins page from the Manage drop down list select COM Add-ins and click the Go… button

image

    3.   Now check the box next to PowerPivot for Excel and click the OK button

image

Now the PowerPivot tab is back in the Excel 2010 Ribbon and you are back in business. 

image

I still don’t like having to wait for the PowerPivot add-in to load each time because I am not actually using PowerPivot every single time when I open Excel and as I have stated, I can tend to be a tad on the impatient side sometimes.  It would be nice to simply have a button that I could toggle to have this feature loaded on an as needed basis.  Now I will tend to shy away from using Excel just because of the load time.  I will look to leverage other applications like the calculator and the Internet more to handle the basic calculations that I used to use Excel 2007 for.

Here are a couple of additional resources for troubleshooting PowerPivot issues (in addition to the one I mentioned above):

Posted in PowerPivot | Tagged: | 12 Comments »

PowerPivot Memory Error – Not Enough Storage

Posted by denglishbi on June 21, 2010

I just got the Professional Microsoft PowerPivot for Excel and SharePoint book and I started working through the tutorials.  Well right out of the gate I received an error message – Memory error: Allocation failure : Not enough storage is available to process this command. . The operation has been cancelled.  My initial reaction was to check the hard drive space.  I had around 15GB of space available, so that wasn’t the issue.  I then checked my available memory (RAM).  And I had just about nothing left.  I went ahead and shutdown every service and application that was not needed, along with restarting my SQL Server database service, so that I just had Excel open.  At this point I had 1.6GB of RAM free and tried to reload the table, FactSalesQuota from the new Contoso Retail DW database, that I was unsuccessfully able to load initially.  Still no luck –

image

I was baffled.  Initially I was able to load the required dimension tables, FactInventory (over 8MM rows), FactSales (over 3MM rows), but I couldn’t load the FactSalesQuota (over 7MM rows) data.  I found it odd that the sales quota data was double the sales fact table, but i believe this is because of the scenario dimension.  Anyway, I was try to figure out what to do next.  I should rewind a bit and state that I am run the 32-bit (x86) version of Excel 2010 and PowerPivot add-in.  The reason for this was because it was the easiest upgrade path for me from Office 2007 since I didn’t want to uninstall the 32-bit version of Office 2007 in order to install the 64-bit version of Office 2010.

I should also mention that when I initially loaded all of my fact tables that were required for the first tutorial I had removed the ETLLoadID, LoadDate, and UpdateDate fields since these would not be needed for analysis and didn’t want them to consume memory and space.

image

So now that I was stuck in a rock and a hard place because of my 32-bit limitation I reviewed the fact table to see if I could possibly remove some unneeded data that was possibly outside the related fact sales information, but it was the same range of dates.  Then I check the columns in the table and realized that there was an identity field setup on the table.  Well I don’t need this column for analysis or relationships, so I went ahead and removed this column as well during the import process to load the data into PowerPivot.  Plus this column is unique per record, so no compression would be performed during import (I would recommend removing the other fact table identity columns as well).

image

And this time…success!  I was able to import an additional 7.5MM rows of data into PowerPivot.

image

After this import my Excel file was hovering around 700MB of RAM consumption.

image

Now I was off to the races in my 32-bit environment.  I would highly recommend going with Office 2010 64-bit at least for Excel if you are going to be doing anything with PowerPivot – at least this way I can experience what other users will go through and be able to help troubleshoot their issues.  I will have more posts coming out shortly related to the tutorials and my experiences and feedback in regards to PowerPivot, so stay tuned.  For more information in regards to PowerPivot memory consumption issues check out the following additional links:

Posted in PowerPivot | Tagged: | 4 Comments »

Hierarchies in PowerPivot

Posted by denglishbi on May 19, 2010

I saw one of Denny Lee’s tweets this morning titled “Hierarchies, Oh Hierarchies…where are thou? (in PowerPivot)”, so I was intrigued since I just talked about this not being available yesterday in my PASSMN presentation.  In his example that he provided was two tables, one for the list of states and another with the city names along with the corresponding state id value reference, and he showed how even though you can relate the two tables that the results could be misleading.  If you tried to select the state name and then city name the city name would actually be replicated for each state.  Not what you would expect.

image

As a work around for this solution he suggested to add a fact table and once that was created then you would see the expected results and the cities would be mapped to the respective states.  My thought was to simply add a calculated column in the city table that had the state id mapping.  So the tables would look like such:

image image image

So initially the StateName column did not exist in the city state table I had defined, but once the relationship was in place I simply added a new calculated column in the table using the RELATED DAX function to pull in the state name associated with the cities (see the DAX function in the formula bar in the middle screenshot).  Now you can provide the single table for the end-users to reference and they will get the following results:

image

The good thing about this is that you don’t necessarily need to display the state table anymore since the information is included in the city state table and the end-users will know that the columns are truly related to each other since they are in a single table.  Still doesn’t exactly make sense why it didn’t work out-of-the-box originally without the fact table solution that Denny provided, but the calculated column solution takes care of the issue, plus provides the single denormalized table that we would typically see for geography information in this instance.

Now the one thing that you don’t get is the user-defined hierarchy like you have in Analysis Services (SSAS) where you would have the navigation path clearly defined for the users to be able to drag-and-drop and drill down (or up).  So when I state that hierarchies are not available this is what I am referring to.

Posted in PowerPivot | Tagged: | 2 Comments »

Alpha Geek PowerPivot Challenge 3 Entry

Posted by denglishbi on February 16, 2010

I just placed an entry in an online Microsoft geek challenge utilizing PowerPivot. You can check it out in the link below and I have added a comment to the entry to provide some background as to what I did for the challenge and why. I tried to get my in this past Saturday, but probably best it didn’t get entered until the new challenge started up.

You can vote once per day.  If you can remember to vote for me each day I would greatly appreciate it.

VIEW MY ENTRY AND VOTE FOR ME

FAA_Strike_Data_Alpha_Challenge3.1

I appreciate your support.

Thanks.

VIEW MY ENTRY AND VOTE FOR ME

Posted in PowerPivot | Tagged: | Leave a Comment »

PowerPivot Beta Alpha Geek Challenge 2 Entry

Posted by denglishbi on February 13, 2010

I waited too long to get my entry in most likely for Challenge 2 of the Alpha Geek Challenge.  It states that it goes through tonight, but not sure if my submission will get posted in the entry list for voting or not – View All Available Entries.

Oh well, it was fun.  I started in on it last night and unfortunately as I was 75% done Excel 2010 Beta decided to run into an unexpected error and close.  I was hoping that the file would be recovered when I re-opened Excel, but no such luck.  So I decided that I would possibly try again this morning.FAA Wildlife Strike logo

For my PowerPivot example I decided to use the FAA Wildlife Strike Database that was made public.  I saw this data being demoed during the BI Power Hour at the PASS 2009 Summit and thought it was interesting.  Especially when you look at the data and start to see species like foxes, opossum, deer, skunks, prairie dogs, squirrels, and moose…  Wow, wouldn’t want to be in the plane when the moose got in the way.

Once I got the data loaded into PowerPivot I decided that I would look at the data for airports in my region of the United States along with the data where the Microsoft BI 2010 Conference will be this year in Louisiana.  I took a look at the trend of strikes over the past nine years along with information on the cost of the repairs (wanted to see how significant the strike was), the top species that get hit (or is it the other way around?), and lastly at what point in the flight that the strike occurred.

FAA_Strike_Data_Alpha_Challenge3

With the slicers I setup filters to select the states that I wanted to view (MN and surrounding states along with LA), time of day (picked Day and Night since that is typically when I would be flying), and then the month (6 for June since that is when the Microsoft BI 2010 Conference is this year).  I also utilized some of the conditional formatting capabilities in the PivotTable for the heat map effect and I couldn’t resist putting in the pie charts.  Not all of the data was labeled with an identifier so you do get some unknowns or blanks, but it was still an interesting exercise and I hope that my entry will make it into the list.  The image above was one that I used in my third attempt to place an entry today and I tweaked the bottom two PivotCharts a bit.  We will see if it ever shows up today…hopefully.

I submitted my entry three different times this morning and you don’t get a confirmation message or feedback at all, so not sure if it even accepted it or was successful.  If you have some time later on tonight please check out the list of entries and Vote for me if I make the cutHot

ThanksThumbs-up

Posted in PowerPivot | Tagged: | Leave a Comment »

Gemini CTP2 Install Experience and Overview

Posted by denglishbi on August 29, 2009

It took me a while, but I tracked down the online links and filled out the survey to get access to the CTP2 release of Gemini known as ‘SQL Server 2008 R2 August CTP and “Gemini” Add-in’.  I also signed up for the CTP3 Gemini Preview.

Here are the screenshots of the install and a quick demo of using the Excel add-in:

image image image
     
image image image
     
image image image
Notice the new Add-ins being loaded when Excel 2010 is launched Message that is displayed to request permissions to install the new Add-in Notice the new Gemini section in the toolbar and that the circular Office button in the top left has been replaced with a new rectangular tab
image image image
Gemini items available in the Ribbon Version information and feedback options New Windows opens up when you are going to load & prepare the data
image   image image
Gemini Client Window Home Ribbon options – notice the new icon in the top left Gemini Client Window Column Tools Ribbon options Import wizard to get data from database
image image image
Setup the connection to database server and database (using Project REAL Warehouse Sample database) Select the source tables and you can chose the option to select related tables and preview/filter the data also Once you are done you will get the import summary (selected the date, store, item dimension tables, and the fact store inventory table)
image image image
Import progress, notice that the fact table is already displaying more than 2 million rows! Each source table is pulled into separate worksheets in the Gemini Client Window – almost 4 million fact records! Create relationships between the tables once they are imported
 image image image
Progress of the relationship being created Ability to manage the relationships (view them, add new ones, and delete them) Now you are ready to start working with the data and create a PivotTable or PivotChart within the Excel Workbook that you initially created

That is a quick overview of the install process and a walkthrough of getting the data established with the new Gemini Add-in.  The data that I used for this example was from the Project REAL Reference Implementation.

For additional information in regards to Gemini take a look at some of these areas:

Posted in PowerPivot | Tagged: | Leave a Comment »