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.

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. 

How to combine a bar chart with a line graph and running totals in SSRS.

In of my previous blogs a wrote about dashboards design rules and the do's and don'ts. Easy readable charts are essential in the success of your reports. When building reports you have a lot of different chart types you can choose for your reports. By default you can use a bar chart of a line chart. When comparing figures for instance current year with prevoius year of current year with the budget. You can use a standard bar chart like this one:

However the graph is a little bit busy to read. Using the combination of a bar for the current year and line graph for the comparing previous year will make the chart easier to read. Like this:

How to do this:

• Open the report in Business Intelligent Development Studio (BIDS).
• Double click on the bars in the chart.

• Select the Bar of previous year in the Chart data window. In my example RevenueYTD_1.
• Change chart type
• Select the line type you want. That's all you need to do.

How to create running totals?

• Double click on the bar in the chart.
• Select the Bar of current year in the Chart data window.
• Series properties.
• Press on the expression button for the value field.
• Use next expression: =Runningvalue(Sum(Fields!Previous_Year.Value),SUM,"RevenueYTD_YTDPY")
• RevenueYTD_YTDPY is the name of my dataset. The column name used is 'Previous_Year'
• Do the same for the line graph.
• That's all you have to do.

Enjoy it to build nice and easy readable reports.

Dashboard design rules, the do's and don'ts

If you are going to build dashboards you need to read the book: Information Dashboard Design written by Stephen Few. Dashboards are very popular because they can be very powerful. However, this potential is rarely realized. A dashboard should directly tell you what you need to know. If that is not the case the dashboard will never be used again. This book will teach you the visual design skills you need to have to create dashboards that communicate clearly, rapidly and compellingly. It will explain how to:

• Avoid the thirteen mistakes common to dashboard design
• Provide viewers with the information they need quickly and clearly
• Apply what we know about visual perception to the visual presentation of information
• Minimize distractions, cliches, and unneccessary embellishments that create confusion
• Organize business information to support meaning and usability
• Create an aesthetically pleasing viewing experience
• Maintain consistency of design to provide accurate interpretation
• Optimize the power of dashboard technology by pairing it with visual effectiviness

The book start with the definition of a dashboard. Without knowing the definition you do not know what to build.

Visual display

of

the most important information needed to achieve on or more objectives

which

fits entirely on a single computer screen

so it can be

monitored at a glance

I will tell some observations I had when I read the book with a lot of pleasure.

• We do not see with our eyes. We see with our brains. If you need to think how to read the data, you have not used the best display media.
• Dashboards display information needed to achieve objectives. What do you prefer? 1) Nice fancy dashboard which is not be re-used. 2) Functional dashboard which help you to reach your goal
• A dashboard fits on a single computer screen. No scroll bars etc.
• Colors have a function. Do not use them because it looks nice. Colors should tell something for instance to grab attention .
• Blank space is better than meaningless decoration. Do not use pictures, logos'. It is a waste of valuable space. If you need to use a logo, make it small and visually subtle, and place it somewhere out of the way.
• Use gridlines will care. Mostly the do nothing but distract from the data.  
• Think about what you want to show or compare. Based on that you need to choose the best display media. Do not use a pie chart because it looks nice. Mostly a bar graph is much better.
• 3D Graphs are nice but 2D are easier to read and understand.
• Dashboards are used to monitor information at a glance. Information is abbreviated in the form of summaries or exceptions.
• Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.
• The drop shadows on a graph are visual fluff. These elements serve only to distract.
• In general Keep It Simple.

To test your dashboard design skills you can do the Graph Design IQ Test on the website of the writer Stephen Few.

Enjoy reading the book and do not forget to do the Graph Design IQ Test

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

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)

Part 2: Analyze SQL Profile traces with SSRS dashboard. How does it work?

As described in my previous blogpost 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 trace files. In this blogpost I will explain what you need to install and configure to use this dashboard.

What do you need to install:

• SQL Server 2008 R2 database engine. This database server is used to import all trace files into a database.
• SQL Server Reporting Services. (2008 R2). This reporting server is used to host the dashboard.
• SQL Server Business Intelligence Development Studio. With BIDS you can modify the dashboard and deploy the reports to the reporting server.

What else do you need:

• Use the default trace file template of SQL Profiler to trace your application. Be aware to use the correct filter for the trace, so you got only these queries you want to analyze. For instance you can filter on hostname, spid or applicationname.
• Script to create database and table in which all trace files are imported.
• Import script to import the SQL Profiler trace files (.TRC)
• The SSRS reports of the dashboard to analyze the trace files.

First we will start to create a new PerformanceAnalyze database and import table: TraceFileImport

-- BEGIN Performance analyze script created by André van de Graaf
-- Blog site http://www.keepitsimpleandfast.com/

USE MASTER
GO

-- Create PerformanceAnalyze database
CREATE DATABASE [PerformanceAnalyze] ON PRIMARY
(NAME = N'PerformanceAnalyze',
FILENAME = N'D:\Data\PerformanceAnalyze.mdf',
SIZE = 102400KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'PerformanceAnalyze_log',
FILENAME = N'D:\Data\PerformanceAnalyze_log.ldf',
SIZE = 10240KB , FILEGROWTH = 10%)

GO

USE [PerformanceAnalyze]
GO

-- Create table in which all trace files will be uploaded.
CREATE TABLE [dbo].[TraceFileImport](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[TextData] [ntext] NULL,
[ApplicationName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[LoginName] [nvarchar](128) NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[Duration] [bigint] NULL,
[ClientProcessID] [int] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ImportID] [nvarchar](50) NULL,

PRIMARY KEY CLUSTERED
([RowNumber] ASC)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- END Performance analyze script created by André van de Graaf


Now we have the database and import table, so we are ready to import the first trace files. The trace files will be imported by the stored procedure: PA_ImportTraceFile.

-- Create stored procedure to import the trace files
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PA_ImportTraceFile]
(@ImportID nvarchar(50), @TraceFile NVARCHAR(2000)) AS
INSERT INTO TraceFileImport
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData,@ImportID AS importid
FROM :: fn_trace_gettable(@TraceFile,default )
GO

This stored procedure uses 2 parameters: Importid,Tracefilename_and_location
The ImportID is used in the SSRS reports to select your trace file.

Example:
EXEC PA_ImportTraceFile 'CustomerX_1','D:\Traces\Cust_Trace1.trc'

In my next blogpost I will make the SSRS reports available and will explain what you need to configure at the reporting server to use these reports. So stay tuned for the SSRS reports.

Previous posts in this series:
Part 1 Analyze SQL Profile traces with SSRS dashboard.

Part 1: Analyze SQL Profile traces with a SSRS dashboard.

This blogpost is the first of a serie, in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler trace files. SQL Profiler is a perfect tool you need to use to analyze the performance of your application. When you have a SQL Profiler trace file the challenge begins to find that part of your application where you can make significant improvements. Improvements can be made in different ways, you can focus on:

• CPU
• Reads
• Writes
• Number of queries
• Repeating queries.
• Applications
• No SQL time. (Time between end time of query X and start time of query X+1)

The big question to answer, what is the best to focus on. This can all be done with the SQL profiler itself however it takes a lot of manual work. With SQL Server Reporting Services I made a dashboard which can be used to analyze a SQL Profile trace file. The dashboard will help you to visualize the bottleneck and to some in to this bottleneck to get more details. This dashboard contains 13 SSRS reports in total.

Click on the picture to enlarge.

In this dashboard, you will get statistics for the selected time frame about:

Total number of queries
Total trace time
Total Duration (SQL time)

Maximum number of Reads,Writes, CPU, Time SQL
Average number of Reads,Writes, CPU, Time SQL
Sum of total Reads,Writes, CPU, Time SQL
Query TOP 5 Reads,Writes, CPU, Time SQL
Repeating Query TOP 25 Reads,Writes, CPU, Time SQL
TOP 25 Reads,Writes, CPU, Time SQL by application

You can use the filters in the report to analyze a specific time frame or to zoom in to a specific bottleneck. In future posts I will explain how to make this dashboard. Please let me know if this can be interesting for you to use.