Ein typisches Verhalten in Reporting-Services ist es, dass Daten, die mit Parametern herausgefiltert werden, auch im Bericht nicht dargestellt werden. Beispiel: In diesem Chart werden die Umsätze für ein Jahr dargestellt: 

 Wenn nun nur noch die Monate Januar-April im Month-Parameter ausgewählt werden, erscheint folgendes Chart:

Die Daten werden per MDX aus einem Cube gelesen und im Dataset mit einem Subselect oder in der WHERE-Bedingung gefiltert:

SELECT

 {[Measures].[Sales Amount]} ON 0,

 NON EMPTY

 {([Date].[Calendar].[Date].ALLMEMBERS )} ON 1

 FROM

 (SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0

 FROM

 (SELECT ( STRTOSET(@DateMonthofYear, CONSTRAINED) ) ON 0

 FROM [Adventure Works]))

Der Kunde wünscht nun, dass die übrigen Monate aber dennoch (mit dem Wert 0) angezeigt werden sollen. Hierfür gibt es zum einen die Möglichkeit, dieses bereits im MDX-Query zu lösen. Dies geschieht über einen String-Vergleich in einem IIF-Block. Leider ist diese Methode recht unperformant.

Ein besseres Ergebnis kann man mit dieser Methode erzielen, vorausgesetzt man verwendet SQL Server ab Version 2008 R2:

Im Dataset werden zunächst alle Daten für das Jahr geholt:

SELECT

 {[Measures].[Sales Amount]} ON 0,

 NON EMPTY

 {([Date].[Calendar].[Date].ALLMEMBERS )} ON 1

 FROM

 (SELECT ( STRTOSET(@DateCalendarYear, CONSTRAINED) ) ON 0

 FROM [Adventure Works])

Dann wird ein zweites Dataset „GueltigeMonate“ erstellt, in dem die Monate anhand des Parameters gefiltert werden:

WITH

MEMBER [Measures].[ParameterCaption] AS [Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

MEMBER [Measures].[ParameterLevel] AS [Date].[Month of Year].CURRENTMEMBER.LEVEL.ORDINAL

SELECT

{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

[Date].[Month of Year].ALLMEMBERS ON ROWS

FROM

(SELECT STRTOSET(@Monate,CONSTRAINED) ON 0

FROM [Adventure Works])

Nun müssen beide Datasets miteinander verbunden werden. Hierzu werden die Eigenschaften der Serie geöffnet:

Im Feld für die Daten (Value) wird nun der Lookup gegen das Dataset „GueltigeMonate“  hergestellt. Hierfür wird die Funktion „Lookup“ benötigt. Die Syntax sieht so aus:

=IIF(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5)=

LOOKUP(LEFT(Fields!Month.Value,LEN(Fields!Month.Value)-5), Fields!ParameterCaption.Value,Fields!ParameterCaption.Value,”GueltigeMonate”),

Sum(Fields!Sales_Amount.Value),0)

Diese Syntax bewirkt folgendes:

Wenn der Monatsname im Haupt-Dataset gleich einem Monatsnamen ist, der im gefilterten Monats-Dataset vorhanden ist, so wird der Wert angezeigt, ansonsten wird die Zahl 0 angezeigt.

Wenn nun im Bericht die gleiche Selektion wie am Anfang beschreiben durchgeführt wird, ergibt sich das gewünschte Bild: 

Die Anzeige-Geschwindigkeit konnte mit der Lookup-Methode gegenüber der Behandlung im Query halbiert werden. In einem der nächsten Blog-einträge werde ich die Lookup-Funktion dann nochmal näher beleuchten.

SSRS 2008; Data basis: AdventureWorks 2008  

The results are to be represented in chart as bars. The data are to be summarized dynamically in groups. The dataset is used in other tables and diagrams and should not be changed.  

Solution: In the characteristics of the Category Group Group Expressions can be inserted. The Expression accesses report parameters, which can be filled by other datasets. Example:  

=Fields!Reseller_Order_Count.Value<= Parameters!StartBucket1.Value  

In this example all data are summarized, whose value is smaller or equal to the parameter. Also the Expression of the announcement is accordingly dynamically filled:  

=IIF(Fields!Reseller_Order_Count.Value <= Parameters!StartBucket1.Value,”1-” & Parameters!StartBucket1.Value,  

IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket1.Value AND Fields!Reseller_Order_Count.Value<=Parameters!StartBucket2.Value, 

 IIF(Fields!Reseller_Order_Count.Value>Parameters!StartBucket2.Value AND Fields!Reseller_Order_Count.Value<=Parameters!

StartBucket3.Value,Parameters!StartBucket2.Value+1 & “-” & Parameters!StartBucket3.Value,”>” & Parameters!StartBucket3.Value)))  

For each group a parameter is needed. As many as desired groups can be provided. If the Expression should not supply value, the group is not indicated, charts behaves also here dynamically. There should be values, which are larger than the highest value of a parameter, these are indicated automatically in their own group. In the following example data with 3 groups/parameters are indicated, the fourth group contain the values, which are larger than the indicated highest parameter 10:

  

SQL Server 2005 | SQL Server 2008

Building your time attribute relationship you have to consider the following questions:

• How will I normally navigate the date hierarchies?
• What formatting options do you usually use in your reports, eg. for the month: fullname (January, …), shortname (Jan, …), number (1, …)
• What other information do you need (eg. holidays, day number of year)?

When building up your time dimension, you should

• Set up proper hierarchies giving the commonly used navigation routes
• Remove attribute hierarchies of attributes that are also appear as a level in a hierarchy
• Use proper keys for your attributes and set up the attribute relationship accordingly
• Use rigid instead of flexible relationship whenever possible (usually this is true for date attributes…)
• Name your attributes using a defined naming schema. The attribute type property gives a good advice. For example you can use names like DayOfMonth (1 to 31), DayOfWeek (1 to 7), DayOfYear (1 to 365) etc. Don’t get confused with the many options available. Usually you will only need few of them. There is a good overview at http://technet.microsoft.com/en-us/library/ms175662.aspx.
• Set the proper attribute type for each of your attributes
• Set the dimension type property to "time" (important if you’re also using semiadditive measures as they require a time dimension)

Below is an example of a date dimension I recently used. Keep in mind though that this is just an example. You might not need an ISO week or quarters (I usually try to avoid them…) or you may even want to see semesters or incorporate a manufacturing calendar.

Of course this is just one way of doing it and you might start using this approach and finetune it to your needs later. You may notice that I used the same source columns as names for fiscal month and month (and other related attributes). This is done in order to provide different keys for the fiscal periods in order to sort for example the months accordingly. For the standard calendar you want to see your months as January, February, …, December while for a fiscal calendar with a fiscal year start of April 1 you want to see the fiscal months in the correct order April, May, June, … December, January, February, March. The separate attributes allow different keys and so the sorting is not complicated.

You will also notice the ISO Week / ISO Year attributes which I’ve included for the European countries. I’ll give some more information about this in a later post.

In this situation you could build natural hierarchies like the following ones:

The following screenshot demonstrates how you can leverage the atttribute relations for example in Microsoft Excel 2007:

Here, we simple display some more attributes alongside with the day information.

The result may then look like this

If you’ve struggled with date dimension attributes and relationship too like I did, I’m very interested in the ideas you developed for setting up your date dimension and I encourage you to leave me a comment.

SQL Server 2005 | SQL Server 2008

Last week I had the opportunity to talk with Peter Leweke from Liquiverse about their new data browser and their focus to turn data from lists into a meaning or as they say on their homepage "Life beyond lists". It’s really exciting to play with the beta release I got. Liquiverse will announce their new release of LiquidCSV soon and it’s definitely worth taking a look.

I’ve included a screenshot taken from an export of some AdventureWorks’ human resources data.

What makes the browser really interesting is the way, how fast and dynamically elements flow when changing sort order or applying filters. Also, when you move your mouse over some area with many data points, they magically drift away so you can easily address every single item of data. It’s hard to explain – you will have to experience it.

Now, what does this have to do with OLAP? Today Liquiverse doesn’t offer an interface to MDX or XMLA data sources but the API will be available in 2009 and it shouldn’t be too difficult to include other data sources or to apply drill down effects using the API. So, I’m really looking forward to what’s going on at Liquiverse.

SQL Server 2005

Microsoft MDX offers a huge set of mathematical function, which can be further enhanced by using Excel-functions. Excel-functions may be called as Excel!functionname but the performance for this approach is not really good. Besides that, not all of your every-day functions are available through MDX. Before using Excel-functions or writing your own SSAS stored procedures it’s worth taking a look at the built-in capabilities of MDX.

In this post we’re talking about the calulcation of quantiles. Quantiles are used e.g. for computing risk measures like the so called Value-At-Risk (VaR). The Value-At-Risk is usually a 1% or 5% quantile of the P&L distribution over a set of scenarios (typically created by a monte carlo simulation).

In the following MDX-sample we calculate a simple quantile over a set of days (listed in a dimension "Date"). In order to compute the p% quantile of a set of n values we first have to sort the values and take the element at position (n-1)*p%+1. Usually we will not end up with an integer here but we don’t want to complicate things here too much.

So let’s try this code in the Adventure Works cube script:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Quantil5Prc] AS
max(bottomcount([Date].[Date].[Date],
int((Count([Date].[Date].[Date])-1)*0.05)+1,
[Measures].[Gross Profit]),[Measures].[Gross Profit]),
NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount], [Measures].[Total Product Cost] },
VISIBLE = 1 ;

We use a bottomcount here to get the smallest p% elements. By using the max-function we can easily get the biggest of those values (which is actually the last if we had sorted the complete set). The expression int(Count([Date].[Date].[Date]) gives the count of days in our date dimension. Be aware that if you have the unknown member enabled for the dimension this element is also counted so you should disable the unknown member or reduce the count by 2 instead of 1.

For Adventure Works I found 1158 days in the Date dimension. Calculating the quantile for single elements or sets of elements executes pretty fast. Of course, query performance depends on the value of p%. For calculating a 90% quantile with the above formula the same query takes longer than a 5% quantile. So it’s better to provide a low p% and switch to topcount instead of sorting too many rows.

The simple test query I used looked like this:

select {[MEASURES].[Quantil5Prc]} on 0
from [Adventure Works]

The query results in the value of 986.6533$ meaning that only in 5% of all days we have a gross profit below this value. Of course, you could put [Product].[Category].[Category] for example on the rows to get a more detailed result.

For further enhancements think of putting the p% value in a separate dimension with given numbers and a reasonable default member. Then you could easily switch the percentage of the quantile or compare different quantiles in one pivot table.