Three ways to check your Power Pivot results.

Power Pivot is a good personal Business Intelligence tool. It should help you to give you better insight in what is happening in your company. The goal is to give insight, so you can take action to do it better in the future. Analyzing the data can results in 3 situations

1. Results you expect.
2. Positive results you do not expect
3. Negative results you do not expect.

In case of situation 3: ‘Negative results you do not expect.’ people will doubt on the correctness of the report. This blog post will help you to analyze the correctness of the data in more detail. In my previous blog I explained the basics of Power Pivot. Pleas read this document before using this blogpost.

1) Add slicers to your report to get a dataset you can understand.

PivotTables and PivotCharts will show consolidated data. This can be a total of thousand or more records. Try to lower the number of records so you can understand the end result in the PivotTable or PivotChart. This can be done by defining some additional slicers which you can use to filter the data. For instance in a revenue report add the country, state and representative to the PivotTable. Now you can look to the revenue report for only one employee, in one country and one state.

How to add slicer to the PivotChart?

Select the Pivot Chart, Open tab PivotChart Tools Analyze.
Press the Insert Slicer button in the ribbon.
Select the country field from the debtor dataset.

Click to enlarge

Repeat these steps for other slicers like statecode or representative.

2) Add filters in Power Pivot window

The Power Pivot Window will show the import dataset records. In the Power Pivot you can filer the dataset to analyze a smaller set of data. For instance for year 2012 and period 6

Open the Power Pivot window. Open PowerPivot tab and select PowerPivot Windows button in the ribbon.

Click to enlarge

Select the dataset you want to filter. For instance InvoiceHistory. Select the drop down button in the column Invoice Year. Select only 2012. Press OK.

Click to enlarge

 Repeat these steps for other columns in the dataset. Look if you got the records you expect.

3) Use query analyzer to add filters to the view.The last step you can do is to analyze the dataset itself in the SQL Server Management Studio. (SSMS) .  For this you need to have knowledge of building TSQL queries.
First of all we need to know the query of the dataset in PowerPivot to import the data.
Select the dataset tab. Select Design tab, Press Table Properties button. In the Edit table properties window switch to: Query editor.

Click to enlarge

Select the query and copy it to your clipboard. (CTRL C)
Open SSMS, Open new query windows and connect to the database. Paste the query from your clipboard to the query window.
Now you can add WHERE clauses, JOIN’S with other tables etc  to the query.

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.

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.