Strategic, tactical and operational dashboards

In my former post i already described the different needs of the different users of dashboards. For these users we can develop operational, tactical and strategic dashboards. Operational dashboards focusses on the operational users, tactical dashboards focusses on the tactical users and strategic dashboards focusses on the strategic users. Logical.

In the book of Wayne Eckerson "Performance dashboards", Wayne speaks about building a management summary on a operational dashboard (for example) and that started me thinking. Isn't building a management summary on a operational dashboard a tactical dashboard (and building a strategic dashboard on a tactical dashboard)? In an ideal world this would be true. Yes, operational, tactical and strategic dashboard should be in line which each other. KPI's needs to be operationalized to lower levels....or lower level indicators needs to be gathered and summarized to higher levels. Otherwise said: you have a top down approach or bottom up approach.

When applying the top down method, you'll be focussed on a balanced scorecard and strategymaps at strategic levels, the tactical dashboards metrics are derived from the KPI's and operational dashboard uses further detailed metrics. Off course it's also possible to use diagnostric metrics at the different levels.

In case of a bottom up approach the relevant operational metrics are summarized to higher aggregated levels for tactical analysis and again tactical metrics are collected into KPI's at strategic levels. In my opinion, with the bottom up approach is difficult to build a aligned organization. The probability that all measures are available for building a higher level dashboard is small. So aligning the dashboards will always have some top down initiatives.

Conclusion
In my opinion there is always a need in the bottom up approach for top down initiatives because at some certain point on a certain level more information is needed than is available. It's much harder building a dashboard with a bottom up method. To get an aligned organization the best way to start is building a strategic dashboard, initially. When this is succesful start implementing tactical and operational dashboards.

Regards,
Hennie

What kind of users will use a dashboard?

As you may know, i've to build a dashboard for a customer. The dashboard should be build for the board of the directors as well as for workers on the workfloor. Currently reading Wayne Eckerson's book : Performance dashboards and it gives some insights about building dashoards. The first thing i'm interested in is which information is presented to whom. Who are my users and what are their demends and wishes? How do handle information? How can a dashboard aid their work? A director has different needs than a professional worker. So these questions come to my mind regarding usage of dashboards.

I think there are three different users of a dashboard:

• Strategic users: These are the board of directors, executives, high level managers, etc. These managers monitor execution of strategic objectives and are frequently involved implementing a balanced scorecard. They will use the dashboard to align the organization with the business objectives. You can define this as a top down approach. The usage is more focussed on management. A list of typical usage is this:
• Scorecarding.
• Monitoring KPI's.
• Tactical users: These are the departmental users like LOB managers, business analysts to compare performance of their areas or projects to budget plans, forecasts or last period's results. For example if a customer wants to monitor defects and the improvements of a machine they want to use a dashboard to display, monitor and analyze progress during the previous 12 months, for instance. The usage is oriented on analysis: Why did this happen? A list of types of usage is this:
• Tracing trends in relation to company's goals and initiatives. 
• Rootcause analysis.
• Issues.
• Operational users: Operational users are frontline workers and managers who deal directly with cusomers or manages the production line. This information is lightly summarized. Operational users are more focussed on monitoring.
• Real time/right time information
• Acting very quickly on information.

An interesting graph i found in a internet document from "Juice : A Guide to Creating Dashboards People Love to Use". These suggests different graphs for different usage.

But there's nothing mentioned about a Gauge diagram, for example. So, the idea is great but i would like more information about which diagrams should you use in which situation.

Greetz,
Hennie

SSAS : datatype struggling

I'm a bit struggling with SSAS and it's about a couple of measures with datatype 'bit' in my SQL Server fact table. At first i thought that i could add them up in SSAS with aggregatefunction 'sum'. Well, that doesn't seem the case here. An error occurred: "bit fields aren't additive". So then i changed the datatype to tinyint and when i processed the cube, the following error occured:

"Errors in the metadata manager. The data type of the 'field' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count "

And i changed the datatype to tinyint and then i got really strange values like -93. Stange because there were only 0 and 1 values in this field. Reading a blog of Darren Gosbell tells me that the -93 is probably an overflow of the datatype. Then i changed the datatype to Integer and still i got errors like the one above (in red).  Then i noticed more properties : source. 

It seems there is another datatype property at a measure and it's defining the source attribute. When i changed that to integer i got the correct values. So there are four places where you can see and/or set datatypes:

1. SQL Server table.
2. Datasource view.
3. Measure.
4. Measure (Source group properties).

1 and 2 are quite obvious. Option 3 is also an option i do understand. You need to set a datatype in your cube when you roll up values from tinyint datatype to integer or perhaps to bigint. But the fourth option is less self explainable. So what is the role of this property group "source"? Is it a sort of CAST operation of the sourceattribute? Looking in on MSDN states that the source properties are describing the source of the measure. Litterally that doesn't seems to me a true statement because when i change the source attribute to the similar datatype in the dsv i got an error.

Below you can read my steps to glory:

1. Change the field in the fact table to Tinyint
2. Change the aggregatefunction to sum.
3. Change the property datatype to integer
4. Change the property datatype of the grouped properties 'source' to integer

I've posted a question on the MS SSAS forum about this issue and when i got a satisfied answer i will post it here on my blog.

Thnx,

Hennie

SQL Server Linked Server best practices

This post is one in a series of posts about SQL Server best practices. I've posted best practices about the following subjects, so far:

• General best practices
• Naming conventions
• Tables
• Columns
• Stored procedures.

This post is about linked servers. One note of appreciation for Arjan  Fraaij, who is a major contributor to this post. One of the serverobjects is linkedserver and is used for using data from a another server or storing data to another server. There are also other options available, like SSIS and therefore it's good to make a list of pros and cons of linked server. So when do you use a linked server? The main reason is that you need the information from another server immediately and in other cases i would suggest using SSIS. 

Below you can see diagram for a DTAP environment and databases A and B. On Server A a linked server is created to server B. This should be done in every environment : Development, Test, Acceptance and Production. It should not be allowed to create linked servers from a Test environment to a Acceptance environment.

Here are the Linked Server best practices:

• Naamconvention according to CamelCase.
• De namingconvention of a linked server is this::  ls<DatabaseName>
• eg. lsDatabaseB.
• A linked server should be related to a database (and not a server)
• A linked server name has never a servername incorporated.
• A linked server should only be created based on integrated security.
• A linked server can only point to a database in the same environment.
• If used in your organization , use a C-Name. This will enhance more flexibility in case migration scenarios.

Greetz,

Hennie

SSRS : Pareto analysis

Hi,

A pareto chart (by Vilfredo Pareto) is a graphical way of highlighting the most important among (typically large) set of factors. This type of chart contains both bars and a line graph. The bars display the values in descending order, and the line graph  shows the cumulative totals (cumulative percentage) for each category, left to right. In my current project i''ve to analyze all kinds of events and i'm thinking about using a pareto analysis.

I used a simple query from the SSRS recipes book and i started experimenting with this:

 SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


Drag a chart on the report and put the sales reasons on the category groups and internet sales amount in the details.

Experimenting with the custom attributes of the columns (chart series, clicking on the histograms) like this (don't forget to set the ShowColumnAs property

Will show a pareto diagram :

Conclusion
At first i thought that the pareto was a separate diagram in the toolbox but it is not. So the most important thing to do is setting the custom properties in the chart series (click on the bars). One thing i have to find out is get a line on 80% for determining what categories are responsible for 80% of the sales.

Greetz,
Hennie

SSRS : Report parameters

Hi,

Currently building a dashboard for a customer and i wanted to understand the concept of parameters a bit better. Especially in relation with MDX queries. Using parameters isn't complicated, but you need to understand them thoroughly when you're building more complex reports. So, lets understand the scope of this post: Parameters. Where do they all appear?

1. Query designer parameters
When you're designing your MDX query in the query designer it's possible to set whether or not you want a parameter.

The MDX query is something like this:

2. Dataset parameters
The dataset is built on a query and is used by the report. 

3. Report parameters
The report parameter is used for prompting to the user. The user can select a value and execute the report.

So i decided to rename the parameter. The first thing i did is renaming the query parameter (@dateCalendarYear into @qryDateCalendarYear) and executing the query and the following error occurs

Parser: The query contains the qryDateCalendarYear parameter, which is not declared. (msmgdsrv)

Then i noticed there is a another window in the query designer where information about a parameter is stored.

So i changed that from DateCalendarYear into qryDateCalendarYear.and now the query is executed.  When i take a look in the parameters window in the dataset properties window an  empty Parameter value is shown. So i choose the suggested option.

Okay let's see whether the reports run and it does.....Now lets change the report parameter and see whether if the report still runs.

An error occurs.....

So let's go the dataset properties window and that indicates that there is a mismatch between the parameter name and parametervalue.

Correcting this will let the report run.

Conclusion
So renaming the different parameters needs some different adjustments. There are four places where you can change something regarding parameters:

• Query designer
• Parameter window in the query desgner window
• Parameters at the dataset properties window 
• Report parameters in the main report development window.

It's a bit confusing that the parameter name and parameter value ara actually both parameter names (and not values). One is the report parameter name and the other is the query parameter name.

So that's it for now.

Greetz,

Hennie

SSRS : experimenting with sparklines

Hi,

Today a post about sparklines in SQL Server Reporting Services. As reading in the book ""SSRS recipes" from Paul Turley and Robert Bruckner, sparklines are invented by Edward Tufte. Sparklines are "small word sized graphics that are embedded in a context of words, numbers and images". I used the following query to experiment with. You can download this from the WROX site.

SELECT
    SUM(t.ExtendedAmountSum) AS ExtendedAmountSum
    , t.EnglishProductCategoryName
    , t.CalendarYear
    , t.MonthNumberOfYear
FROM
(
    SELECT
        SUM(FactInternetSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactInternetSales
    INNER JOIN
          DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
          DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
          DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
          DimDate ON FactInternetSales.OrderDateKey = DimDate.DateKey
    GROUP BY   
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    UNION ALL
    SELECT
        SUM(FactResellerSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactResellerSales
    INNER JOIN
          DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
          DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
          DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
          DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
    GROUP BY   
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
) t
GROUP BY   
        t.EnglishProductCategoryName
        , t.CalendarYear
        , t.MonthNumberOfYear
ORDER BY
    CalendarYear, EnglishProductCategoryName

1. And i started building the following report. Insert a table into the report body and delete the original row group and associated rows. Drag the calendar year and english productcategory into the row groups window. Drag the field extendedAmountSum on the data fields region.

Running the report will show this:

Now drag a sparklines component from the toolbox on the report body. Setup the fields calendaryears and the Salesamount for the sparklines.

And drag the sparkline into the tabel and run the report and you will get something like this:

If you change the sparkline type to "smooth area" and you get this:

 Change the chart type to pie and you get this:

I've some difficulty understanding what's length of the period of a sparkline. With a marker points you get a marker for every row for  grouped records. For instance for 2005 and accessoires there are 6 months, 6 records and 6 markerpoints. Playing with background colors and markerpoints and you get this:

So that's it with playing with sparklines.

Greetz,

Hennie