Performance tips for your Power Pivot sheet

Power Pivot is a really good personal Business Intelligence tool with a great performance. However, for every tool there are tips to optimize the performance. In Power Pivot you need to define the BISM. (Business Intelligence Semantic model), please take next tips into consideration during the design of your BISM model:

• Use views to import data in Power Pivot. The view will contain the business logic of how the data is stored in your database. If changes are made to your business logic, you only need to change the views. The Power Pivot sheet will still work.
• Use logical columns names in the views. For instance [Account code] in stead of debnr. Everybody should understand what kind of content is stored in each column.
• Import only columns you really need. Avoid SELECT * FROM MyView1 As described in my previous blog post: Memory management in Power Pivot, all data is kept in memory. Every column which is not used will use memory which can not be used for other purposes.
• Import columns which are useful for analytics purposes. For instance for customer data: Account code, Country, State. Columns like street name are not so useful. As described here, it will create a lot of distinct values in your dictionary for this column. This will have a negative impact on performance.
• Import DateTime columns in 2 separate columns. One Date column and one Time column. If time portion is not useful for your analytics do not import it at all.
• Import master data in separate tabs. For instance all item attributes in one tab and use the item key in all transactional tabs. Link the item key from the transactional tab to the item key of the Item master tab.
• Reduce the number of rows to import. If you analyse on month level, group all data in the view to the level you want. For instance group by Date, Item, Amount. This will save a lot of rows to import. Of course, this is not possible sometimes because you do not want to loose the granularity of analysis.
• Reduce the number of rows to import by selecting only the subset you are going the analyze. For instance your database contains financial transaction as of financial year 2008. If you need to analyze of the current and previous year, import only the last 2 years.
• Optimize column data types. A column with few distinct values will be lighter than a column with a high number of distinct values. This is important also for measures, which are considered also possible quantitative attributes. If the measure you are storing is a float and is the result of a calculation, consider reducing the number of digits to be imported. This will reduce the size of the dictionary, and possibly also the number of distinct values.
• Avoid high-cardinality columns. Columns with unique ID's like invoice numbers are very expensive. Sometimes you can skip this columns and use the COUNTROWS function instead of the DISTINCTCOUNT.
• Use measures instead of calculated columns if possible. Calculated columns are stored as an imported column. This does not apply to calculated measures. A calculated measure is calculated at query time.
• In case you need to store a measure in a calculated column, consider to reduce the number of digits of the calculation.
• Normalizing data doesn’t have a big effect on the size of the resulting database. However, it might have a strong impact on both processing time and memory required to process data. The key is to find a right balance. A Star schema is in most situation the right balance.

Enjoy it, to make your Power Pivot sheets even more powerful.

PowerPivot fieldlist is grey and not selectable

Sometimes my PowerPivot field list is grey and can't be selected. It can happen when you are busy in an existing PowerPivot sheet. It looks like a bug because a restart of Excel solved my situation.

Solution:

• Save your Power Pivot Sheet
• Close all other Excel sheets
• Close Excel
• Check in task manager if Excel.exe is not running
• If Excel.exe is still running, kill this task
• Start Excel
• Open your Power Pivot sheet
• Click on a graph and you should be able to select the Field list.

Unfortunatly, is is not clear when and how this happens. I'm using Power Pivot version 11.0.3000.0

Power Pivot: The PivotTable report will not fit on sheet.

During the refresh of data in an Excel Power Pivot next error can occur:

The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns.  (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.

Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.

Script to generate a time dimension table to use in Power Pivot.

In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.

• Create a time dimension table.  (See later in this blog how to do)
• Mark this table as Date table.
• Link from your Revenue table the column invoicedate to the key of the Time Dimension table.

Now you are ready to use this feature.

To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from here. At the end of the script you can specify the start date and end date of the Time dimension entries. 


INSERT INTO Dimtime
SELECT * FROM dbo.F_TABLE_DATE ('20000101','20201231')

In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.

How to use Power Pivot for Excel on your Exact Globe database.

As described in my previous blog, easier to build reports on a Exact Globe database with your preferred reporting tools by making use of the Globe reporting views, I described the introduction of the Globe reporting views. In this blog I will describe how you can use these reporting views with Power Pivot for Excel (one of my favorite 10 features of SQL 2008 R2).  PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers.

To make it a little bit easier for you I have created an Power Pivot for Excel sheet with some pre configured charts and tables on it. Of course in combinatie with the slicers. Last but not least, some relations between the different views. These relations are very important to analyze and combine different types of data.

This Power Pivot for Excel sheet make use of the Globe Reporting Views (GRV_*). As of Exact Globe product update 402 the first version of the reporting views are shipped. These views will only be installed as of SQL 2005.

In this Excel sheet you will find tabs for:

1. Revenue Overview (Slicers on: Year, Period,Country, Statecode, Sectorcode, Employee)
2. Revenue Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
3. Cost Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
4. Sales Account Analyze (Slicers on: Year, Period)
5. Sales Employee Analyze (Slicers on: Year, Period)
6. Sales Item Analyze (Slicers on: Year, Period)
7. Sales Assortment Analyze (Slicers on: Year, Period)
8. Top Suppliers (Slicers on: Year, Period)
9. Margin Items (Slicers on: Year, Period, Assortment)

Before starting you need to have:

• MS Office Excel 2010, preferable 64 bits version.
• Globe database installed on SQL 2005 or higher.
• Power Pivot for Excel.
• Globe Reporting Views installed on your Globe database. In case you can't wait for Globe release 402. Download the script to install the views.
• Read access to the Globe Reporting Views
• Download and Open the GRV_PowerPivotGlobe excel sheet.

Now it is time to load your Globe data in your sheet. Next steps are described in my blogpost: How to change the database for your Power Pivot sheet.

1. Change the database connection to your Globe database
2. Update (Refresh) the the Power Pivot Window.
3. Update (Refresh) the Excel sheet.
4. Now you are ready to analyze your Globe administration in Power Pivot for Excel.
5. Adjust the Charts, Pivots, tables etc to your own preferences.

Enjoy the power of Power Pivot for Excel on your Globe databases.

Easier to build reports on a Globe database with your preferred reporting tools by making use of the Globe Reporting Views.

A lot of data is stored by business solutions in one or more databases. Every business solution will ship a lot of overviews to support your business process. However, a lot of users requires some custom made reports, which are not part of the standard solution. As of today a lot of reporting tools are available to build reports on your database. For instance:

• SQL Server Reporting Services (SSRS)
• Crystal Reports (CRW)
• Excel
• Power Pivot for Excel

Every reporting tool has it's own advantages and disadvantages. It's up to the report builder to use his favorite reporting tool.

Building reports can be divided into next steps:

1. Define the goal of your report.
2. Retreive the data from the data source.
3. Make calculations with the data you have retrieved.
4. Format the report according your UX design rules.

The report builder expert will know all in's and out's of the reporting tool. However this expert does not always understand the business logic of the solution. To get a better understanding you need to read and understand the database documentation of the solution. I will explain with one example: How can I get all outstanding invoices of customer X. To answer this question you need to know in which tables this information is stored but also how it is stored. For Exact Globe next tables are used: GBKMUT, BankTransactions, CICMPY. The outstanding items query will contain 140 lines of code. So being a report builder expert you can still not build the requested reports if you do not understand the business logic.

To solve the challenge of the business logic we have developed database views which contains the business logic. The view will present the data in an understandable format which can be used in the report.

The advantages of using views:

1. Business logic is in the database, not in the report. If business logic changes, the view will be updated with the new logic. No changes are needed in the report.
2. Data is presented in an understandable way. For instance 'Supplier code' instead of a databasse column name: 'cicmpy.crdcode'
   
   
3. Data sets in the reports are easier to read and maintain. 
4. Performance can  be better guaranteed.

As of Exact Globe product update 402 the first version of the reporting views are shipped. The Globe Reporting Views start with 'GRV_' in their name.  In my next blog post I will describe how you can use these views with Power Pivot for Excel.

Empower your BI using Powerpivot with the datasets of the Windows Azure Marketplace.

To empower you BI solutions, you want to merge data from different data providers. In most companies data is merged from databases within the company. As of today a lot more information is available which can be useful to empower your BI solution. I will explain this with an example.
Assume your company is making ice creams. The sales revenue of ice cream depends on the weather. The higher the tempature, the more ice creams will be consumed. So your revenue of ice creams should be compared with the tempature. By comparing your revenue with the tempature you can see if the increase in revenue is caused by a good job of your sales people or by extreem hot temparture of the last period. How do you know what the tempature has been in the last year? This kind of data will come available in the Windows Azure Market Place for premium data. It will enable information workers to integrate premium datasets available on DataMarket with their on-premise business data to perform analytics.

How to use premium Windows Azure DataMarket data?
In this example I will make use of a dataset which is for free: Datestream. Datestream is a date table feed designed for import into an Excel PowerPivot model. The table contains columns particularly suitable for time business intelligence applications.

1. Sign on to the Windows Azure market Place.
2. Select the Datestream dataset.
3. Select the hyperlink: Explorer this dataset.
   
   
4. Specify YearKey 2010. In my example I will analyze only the year 2010. Press on the Run Query button.
5. To generate a DataFeed. Select Export and press on the Export to Excel Powerpivot Download button.
   
   
6. Specify a name for your data feed and save it to a location on your PC.
7. Open Excel PowerPivot and import your on premise data you want to analyze. For instance your ice cream sales per day.
8. Press the From Azure DataMarket button to import the Datestream datafeed.
   
   
9. Specify the Azure DataMarket dataset URL. Browse to the datafeed file you made in step 6.
10. Specify your Account key. This can be found in the Windows Azure market place under the tab My account. Select account keys.
    
    
11. Refresh your datasets in the PowerPivot window. You will get 365 rows to import the date details for the year 2010.
    
    
12. Create a relationship between the date from the DateStream and your ice scream sales dates. This can be done in the design tab of the PowerPivot window.
13. Now you can start analyzing your data. For instance, which day of the week do you sell the most ice creams.

Enjoy it to use the Azure DataMarket to empower your BI experience.

What is said about your business on Twitter? PowerPivot will help you.

Social media, like Twitter, is a powerful medium in which users can express thier emotion about what they are doing. Emotions can be positieve and negative. As a company, it is interesting to know what is said about your business. Of course you are happy if people post positive tweets about your business, but it will also happen that people post negative experiences. Microsoft has made a nice application which will help you to analyze what is said about your business on twitter. It is called: Analytics for Twitter.
You specify the hashtags, twitternames etc, you want to analyze and PowerPivot will do the rest for you. Analyze will be done on the tweets of the last 7 days. After downloading all tweets, you can start analyzing the tweets with the slicers of PowerPivot. Click on the next picture to enlarge the example of Analytics of Twitter.

Analytics for Twitter can be downloaded here.
Requirements: latest version of Microsoft Excel 2010, Microsoft PowerPivot for Excel 2010 

Enjoy analyzing your business on Twitter.

How to update your PowerPivot Field List after a change in the datamodel?

It can happen that you have made a nice PowerPivot dashboard with a lot of usefull pivots and charts. After a while some changes are made in the datamodel of the database on which you have build your PowerPivot dashboard. For instance one column is added to an existing table or view. In this blog I will describe how you can update your PowerPivot Field List with the new column, so you can make use of it.

• Open the Excel sheet with your PowerPivot dashboard.
• Select PowerPivot tab in the Ribbon.
• Press the button PowerPivot Window button to launch the PowerPivot window.
• Select the table or view on which a column is added

• Press the button Table Properties.

• Be sure to select Column names from Source.
• Scroll to the right. At the end you will see the new added column. Select this column. In this example Zipcode
• The added column ZIPCode is now added to the PowerPivot Window.
• Select the Excel sheet with you rPowerPivot dashboard.
• Show the Field List.
• The PowerPivot Field list has detected that a change is made. Press the refresh button.

• Select the Data tab in the Ribbon of your Excel sheet and press the refresh all button. This update your excel sheet with the information of the added column.
  

• Now look in the PowerPivot Field List and you will see the added column in the table or view.

Enjoy using your PowerPivot dashboard with the new column(s)

How to change the database for your PowerPivot sheet.

As of Office 2010, you can use PowerPivot in combination with Excel. The PowerPivot is a very powerfull tool to analyze the data in your database. After having build some nice pivots on one of your databases, it can happen that you want to re-use the same sheet on a different database.

In this blog I will explain how you can update your PowerPivot sheet with data from a different database.

• Open your Excell sheet.
• Select the PowerPivot ribbon tab.

• Select the PowerPivot Window button in the ribbon. The PowerPivot window is now started.
• Select the design tab.
• Select button: Existing Connections.

• Select your PowerPivot data connection.

• Press edit button.
• Change the server and or database you want to use.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• Press the 'test connection' button to see if you can connect to the database.
• Press save.
• Press Close
• Select the Home tab in the PowerPivot ribbon.

• Select Refresh all button. Now the data refresh window is displayed.

 

• Press Close when data is updated successfully.

Now we need to update the excell sheet with the data in the power pivot sheet.
 

• Go back to your excell sheet.
• Select the Data tab in the ribbon.
• Select the refresh all button.

Now all data in your excell sheet is updated with the data of the selected database.
Enjoy analyzing your data with the PowerPivot.