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.

Design tips for My favorite dashboard.

Dashboards are very popular way to display data. Other popular names are (performance) cockpits. Sometimes I see dashboards for which I think, which vision is used to build such a bad dashboard. If you start building a dashboard without a vision it will end up in a dashboard which maybe looks nice as a first impression, but is useless on a daily basis. In this blog post I will describe my vision how you should build a useful dashboard. First of all I will start with 2 examples of a dashboard. Both will show the same information. The only difference is the presentation of the data. Examples are the best way to explain.
Let's start with a dashboard in which a lot of improvements can be made:

Now the same information in presented in a better way:

I will comment on the dashboard which can be improved. In general one rule can be applied: Keep in mind. Less is better.

1. Report title in big font and printed Bold. Goal of the dashboard is to show attention to the figures not to the title.
2. Gray background in the report. This is visual fluff. The gray color has no function. Keep in mind. Less is better.
3. Axe values. A lot of zero's is expensive space and is difficult to read. It will make the bar chart it self smaller. We read with our brains. Is the axis value 200.000 or 2 million? 
4. Red color of the bar chart. Is something wrong? Colors have a function. Red is a color to grab attention that something is wrong (STOP). In this example nothing is wrong. Revenue is better than previous year.
5. 3D graph. A dashboard is not a painting. The dashboard is used in a business environment. Not in a gallery. Always use 2D graphs. They are easier to read and understand. For example: What is the value for period 2 of previous year?
6. Aqua color with gradient style center. This is visual fluff. Keep in mind. Less is better.
7. Legend takes a lot of valuable space which result in a smaller bar chart. It's not the legend but the chart which need to most space. 
8. Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.
9. Use grid lines carefully. Keep in mind. Less is better.
10. Matrix header in bigger font, printed bold on a colored background. One way of visualization is enough to emphasize difference between the lines. Using a list level for other lines will show difference between header and sub-lines. Keep in mind. Less is better.
11. Underline the header. This is useless. Keep in mind. Less is better.
12. Repeating currency symbol. One currency symbol is enough. Is will save value space in every column.
13. Hyperlink to other report is printed in the same font color. Use a other color for hyperlinks, so the user can see that a hyperlink to another report is available.
14. Remove the border of the bar chart.

Some more remarks to take into account during the design of your dashboard.

• A dashboard is used on a daily basis. Display only data which needs your attention, so you can take action to improve. Report the KPI's which needs improvement. The dashboard need to help you to achieve your goal. Data content which will not change overtime is use less on a daily used dashboard.
• We do not see with our eyes, we see with our brains. Content on the dashboard should be clear to understand. If people need to think about what they see, you need to change the visualization of your information.
• A dashboard should fit on one screen. Avoid scrolling bars. With scrolling bars, it can happen that something that needs your attention (RED) is outside your screen.
• Do not use shadows. It is visual fluff with no meaning.
• Do not use logo's and pictures. It is wasting your valuable space. If needed make it small and place it somewhere out of the way.
• What do you want to show or compare? Based on this select the best visualization for it. See : http://www.keepitsimpleandfast.com/2012/07/which-visualizations-should-i-use-in-my.html For example using a pie chart to compare 2 values can be useful  However it can take a lot of useful space. Do not use is to compare multiple values especially if you do not know how many values you need to compare. A bar chart is better in this situation.
• Display consolidated information, summaries or exceptions. Do not display details. Details are used in slice and dice reports to explain the consolidated information, summaries or exceptions.
• Put data which is relevant to each other, close together.
• Use a gray color for your fonts instead of black.

Enjoy it to build dashboards to deliver a good user experience for your users. 

An impression to your future, with an important role for BI

Microsoft published a new video that shows how the company believes technology is poised to evolve over the next five to 10 years, based on the trends its researchers and engineers are seeing in software, devices, displays, sensors, processors and intelligent systems. You will see a lot of BI in this movie. BI will help you to get the information in the way you want and need it.


Enjoy it to have a look into your future.

Part 3: Analyze SQL Profile traces with a SSRS dashboard. The reports itself

As described in my previous blogpost Part 1 and Part 2, I started a series in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler traces. In this last blogpost I will supply all reports for dashboard.

This dashboard contains 13 reports and can be downloaded from here:

1. TracePerformanceOverview. This is the dashboard itself. It is the starting point of your analyze. It contains links to all other 12 reports.
2. ApplicationTOPXCPU: Overview of CPU usage grouped by application.
3. ApplicationTOPXReads: Overview of Reads grouped by application.
4. ApplicationTOPXTimeSQL: Overview of SQLDuration grouped by application.
5. ApplicationTOPXWrites: Overview of Writes grouped by application.
6. RepeatingTOPXCPU: Overview of recurring queries summarized by CPU.
7. Repeating TOPXReads: Overview of recurring queries summarized by Reads.
8. Repeating TOPXTimeSQL: Overview of recurring queries summarized by SQL Duration.
9. Repeating TOPXWrites: Overview of recurring queries summarized by Writes.
10. TOPXCPU: Overview of queries ordered by CPU usage.
11. TOPXReads: Overview of queries ordered by Reads.
12. TOPXTimeSQL: Overview of queries ordered by SQL Duration.
13. TOPXWrites: Overview of queries ordered by Writes.

• Deploy all these reports to your reporting server in one folder. The reports are made on a SQL 2008 R2 Reporting server. Therefor they can only be deployed on a SQL 2008 R2 Reporting server. If you deploy it on a SQL 2008 Reporting server you can get next error:  [rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.
• Create a Data source on your reporting server named: ExactPerformanceAnalyze

 Enjoy using the dashboard. Please leave a comment if you have questions or suggestions.

Tweet

My favorite Top 10 features of SQL 2008 R2

Photo credit: Sam_Churchill

In May 2010 the new version of SQL Server will be launched named: SQL 2008 R2.  In this blog I will list my favorite 8 features.

1. Report Builder 3.0. Report Builder 3.0 is a report authoring tool that you can use to design and publish your own reports. It's the successor of Report Builder 2.0 and it's support all new SQL 2008 R2 reporting services features. When you design a report, you specify where to get the data, which data to get, and how to display the data. When you run the report, the report processor takes all the information you have specified, retrieves the data and combines it with the report layout to generate each page as you view it. You can preview your reports in Report Builder, or you can publish your report to a report server where others can run it.
2. Reporting server support for Databars, Sparklines, Indicators and Maps. These visualization features enables a user to build nice dashboard.
   
   
   These visualization features enables you to build nice dashboards which can be used in web applications which have integrated SQL Server Reporting Services (SSRS)  like Exact Synergy Enterprise.
3. Compressed backup support in SQL 2008 R2 Standard Edition. Backup compression is introduced in SQL 2008 Enterprise edition. In SQL 2008 R2 it is also available in the Standard edition. 
4. Unicode compression. As more and more businesses developing global customer base, applications are being developed/modified to use unicode based data types such as NCHAR and NVARCHAR. SQL Server uses UCS-2 encoding scheme that takes two bytes of storage regardless of the locale. For example, in ASCII character set when stored as NCHAR, each character only needs 1 byte of storage but it is stored using 2 bytes with the significant byte being 0. In fact most European languages need only 1 byte of storage. When an application is either converted or written to user unicode based data types, it can, depending on the size of strings, increase the storage requirements significantly. Unfortunatly Unicode compression is not supported for NVARCHAR (MAX) type, including in-row values or for NTEXT. This is something Microsoft will consider in the future release. Unicode compression is automatically included in Page or Row compression. The compression is handled by the SQL server engine so no application changes are needed to use it.
5. Power Pivot for Excel. 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
6. Master Data Services. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. This is the first step in centrally manage master data. The challenge in your organization will always be:  Who is allowed to change what master data. The bigger your company is, the more complex it will be. 
7. SQL Server Utility and Multi-Server Dashboards. SQL Server utility and multi-server management will help organizations proactively manage database environments efficiently at scale through centralized visibility into resource utilization and streamlined consolidation and upgrade initiatives across the application lifecycle.
8. SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These 2 new premium editions are introduced to meet the needs of large scale datacenters and data warehouses.

What are your favorite features to complete this TOP 10 list?
Enjoy using SQL 2008 R2