Christian Wade's Blog

DAX Common Calcs Reference List

christian.wade — Tue, 06 Sep 2011 19:46:00 GMT

Having recently evaluated the BISM Tabular (CTP3), I checked I can perform some common calcs and created this reference list. Rather than store it in the cloud somewhere, I thought I’d share it (and then find it easier when I want to use it later).

Many-to-Many Dimensions

No Allocation

Update 10/18/11 - use SUMMARIZE instead of COUNTROWS:

~~=CALCULATE( SUM('SalesFact'[Sales]),~~

~~FILTER( 'InnerDim',~~

~~CALCULATE( COUNTROWS('M2MFact') > 0 )~~

)

=CALCULATE( SUM('SalesFact'[Sales]),

SUMMARIZE( 'M2MFact', 'InnerDim'[InnerDimKey] )

)

With Allocation

=SUMX( 'InnerDim',

CALCULATE( SUM( 'SalesFact'[Sales]) *

SUM('M2MFact'[M2MFactAllocationFactor])

)

Moving Averages (day level)

=CALCULATE( [My Measure], --already existing summable measure

DATESINPERIOD( 'Date'[SQLDate],

LASTDATE( 'Date'[SQLDate]),

-89,

day

ALL('Date')

) / 90

Role Playing Dimensions

For multiple instances of the same dimension using different relationships, easy workaround is to just create multiple instances of the same table in the model (OK – it’s not perfect, but it works).

And we can create different measures that relate to the single instance of the dimension, but use "inactive" relationships using the USERELATIONSHIP function:

=CALCULATE([Sales], USERELATIONSHIP(DimDate[DateKey], FactSales[ShipDate])

Semi-Additive Measures

LastBalance=CALCULATE(SUM(Balances[Balance], LASTDATE(Date[Date]))

Period To Date

=CALCULATE( [My Measure], --already existing summable measure

DATESMTD('Date'[SQLDate]),

ALL('Date')

)

Parent Child Hierarchies

See http://www.powerpivotblog.nl/powerpivot-denali-parent-child-using-dax

Denali BISM Tabular Eval

christian.wade — Tue, 06 Sep 2011 19:39:00 GMT

Been looking at Denali and what would be gained/lost by upgrading a particular SSAS 2008 (multidimensional) cube to a Tabular BISM Model.

I have to say I’m quite pleased. Here are the main pros …

· The performance is a lot better (even in CTP3).

I haven’t quite managed 100 million rows in Excel yet. Oh and my users won’t be doing their analysis in the PowerPivot window. But joking aside, I am impressed with the performance – and I can fit a heck of a lot more rows in Excel.

· [Not sure if this one is a pro or a con] I must admit, it will be easier for “BI practitioners” to create/manage cubes. (Although I am upset that my 1,000 line MDX script that only I know how to maintain will likely get trashed.)

At CTP3, my biggest blocker to migrating to tabular is the lack of display folders! I’m guessing this is a good thing since this should be easy to add and I’d expect it by RTM. But here is my list of missing nice-to-haves (right now with CTP3) …

· No Display folders for measures

· Cannot control IgnoreUnrelatedDimensions

· No DefaultMembers for hierarchies / non-aggregateable hierarchies

· No utility dims / MDX scripting

· Relationships to user hierarchies have to be at leaf level. Say you want to show a report like this (very simplified)

		Target Growth %	Actual Growth %
Product Category 1	Product A	110	100
Product Category 1	Product B	110	100
Product Category 2	Product C	110	100
Product Category 2	Product D	110	100

And the target is set at the product category level. Well, it would be nice to give the users one way to get to products – via the product hierarchy that includes category. But I can’t relate the target to the user hierarchy at the non-leaf level. The alternative is nesting attributes in Excel - which of course is OK for a simple case like the above. But for complex models with facts at various levels of hierarchies, this becomes limiting. We all know the users love user hierarchies, but we also don’t like having to create lots of hierarchies showing the same thing, which is what will inevitably end up happening.

In multidimensional land, we can at least create relationships from measure groups to dimensions at the non-leaf level – and ensure IgnoreUnrelatedDimensions is set to true.

But the real solution would be to allow user hierarchies to be created independently of tables or dimensions – just an arbitrary collection of attributes from across the whole model – resulting in the same numbers (and different user experience) to nesting.

But again, I’m pretty happy with CTP3 and I see no showstoppers to migrating a particular cube.

As part of my evaluation, I checked I can perform some of the common calcs that I currently have in MDX. I have used this to create a DAX Common Calcs reference list in my next post.

Days Since Last Event: TSQL vs. MDX vs. DAX

christian.wade — Sat, 15 Jan 2011 02:17:00 GMT

I have often had to report the “days since last event”. I’m going to do it here using a variety of query languages and compare them.

Consider the following TSQL query on AdventureWorksDW - and the output below.

SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

FROM dbo.DimDate d

LEFT JOIN

(SELECT s.OrderDateKey, p.Color

FROM dbo.FactInternetSales s

INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

WHERE d.CalendarYear = 2003

GROUP BY d.FullDateAlternateKey

ORDER BY FullDateAlternateKey

What if we need a DaysSinceLastOrder column that would return this?

In TSQL, we could do it using a recursive CTE that iterates backwards by one date at a time.

WITH BaseQuery(FullDateAlternateKey, OrderCount) AS

(

SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

FROM dbo.DimDate d

LEFT JOIN

(SELECT s.OrderDateKey, p.Color

FROM dbo.FactInternetSales s

INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

WHERE d.CalendarYear = 2003

GROUP BY d.FullDateAlternateKey

), DaysSinceLast_Recursive(FullDateAlternateKey, OrderCount, DaysSinceLastOrder) AS

(

-- anchor members

SELECT FullDateAlternateKey, OrderCount, 0 AS DaysSinceLastOrder

FROM BaseQuery b

WHERE OrderCount > 0

UNION ALL

-- recursive members

SELECT b.FullDateAlternateKey, b.OrderCount,

d.DaysSinceLastOrder + 1 AS DaysSinceLastOrder

FROM BaseQuery b

INNER JOIN DaysSinceLast_Recursive d ON

DATEADD(day, -1, b.FullDateAlternateKey) = d.FullDateAlternateKey

AND NOT b.OrderCount > 0

)

SELECT * FROM DaysSinceLast_Recursive

ORDER BY FullDateAlternateKey

Or we could just get the max date with at least one order prior to the current date - and then use DATEDIFF to count the number of days.

WITH BaseQuery(FullDateAlternateKey, OrderCount) AS

(

SELECT d.FullDateAlternateKey, COUNT(dt.OrderDateKey) AS OrderCount

FROM dbo.DimDate d

LEFT JOIN

(SELECT s.OrderDateKey, p.Color

FROM dbo.FactInternetSales s

INNER JOIN dbo.DimProduct p ON s.ProductKey = p.ProductKey) dt

ON d.DateKey = dt.OrderDateKey AND dt.Color = 'Yellow'

WHERE d.CalendarYear = 2003

GROUP BY d.FullDateAlternateKey

)

SELECT FullDateAlternateKey, OrderCount,

CASE WHEN OrderCount > 0 THEN 0

ELSE

DATEDIFF(day,

(SELECT MAX(FullDateAlternateKey) FROM BaseQuery b2

WHERE b2.FullDateAlternateKey < b.FullDateAlternateKey AND

b2.OrderCount > 0),

FullDateAlternateKey)

END DaysSinceLastOrder

FROM BaseQuery b

ORDER BY FullDateAlternateKey

What about MDX? We might be tempted to do it recursively in MDX.

(This code is just illustrating the concept – it doesn’t check the current level of the Calendar hierarchy, aggregate up the Calendar hierarchy, etc.)

WITH MEMBER [Measures].[Days Since Last Order] AS

//check if current date member has order count that is empty or 0

IIf( ([Date].[Calendar].CurrentMember,

[Measures].[Internet Order Count]) = 0,

([Date].[Calendar].CurrentMember.PrevMember,

[Measures].[Days Since Last Order]) + 1,

Null

)

SELECT

{[Measures].[Internet Order Count],

[Measures].[Days Since Last Order]} ON 0,

[Date].[Calendar].[Date].Members ON 1

FROM [Adventure Works]

WHERE ([Product].[Color].&[Yellow],

[Date].[Calendar Year].&[2003])

which returns what we want …

No faffing around with dates. We leverage the inherent ordering of members in Analysis Services using .PrevMember.

But you may be aware that recursive MDX is often prone to performance problems. It works OK on AdventureWorks, but take my word for it – it would run like a 3-legged dog on large volumes.

The following MDX version would perform much faster ...

WITH MEMBER [Measures].[Days Since Last Order] AS

Count( Tail( NonEmpty( {Null : [Date].[Calendar].CurrentMember},

[Measures].[Internet Order Count] ),

1).Item(0) : [Date].[Calendar].CurrentMember

) - 1

SELECT

{[Measures].[Internet Order Count],

[Measures].[Days Since Last Order]} ON 0,

[Date].[Calendar].[Date].Members ON 1

FROM [Adventure Works]

WHERE ([Product].[Color].&[Yellow],

[Date].[Calendar Year].&[2003])

and returns the same results (almost)

The above MDX again depends on the inherent ordering of members in Analysis Services, but this time it gets the last non null date (using the Tail function) and then counts the number of members between that and the Calendar current member.

How about DAX? A DAX measure can't call another measure (which seems like a major limitation - maybe that will change as they enhance the language), let alone call itself recursively.

But the following does work. Note: I had to convert the OrderDateKey in FactInternetSales to type datetime to get it to work.

=CALCULATE( COUNTROWS( DimDate ) - 1,

DATESBETWEEN( DimDate[FullDateAlternateKey],

LASTNONBLANK( DATESBETWEEN( DimDate[FullDateAlternateKey],

BLANK(),

LASTDATE( DimDate[FullDateAlternateKey] )

CALCULATE( COUNTROWS( FactInternetSales ) )

LASTDATE( DimDate[FullDateAlternateKey] )

)

I think the DAX version is somewhere in between the 2nd TSQL version and the 2nd MDX version.

Using BLANK() as the 2nd parameter for (the inner) DATESBETWEEN tells PowerPivot to use the first available date (similar behaviour to " Null : " in MDX).
It then gets the last non-empty date using LASTNONEMPTY instead of the MAX function.
But it still has to faf around with dates because cannot depend on member ordering.

Update: here is a simpler version of the DAX calculation.

=LASTDATE( DimDate[FullDateAlternateKey] ) -
LASTNONBLANK( DATESBETWEEN( DimDate[FullDateAlternateKey],
                             BLANK(),
                             LASTDATE( DimDate[FullDateAlternateKey] )
                           ),
               CALCULATE( COUNTROWS( FactInternetSales ) )
             )

It simply subtracts one date from the other and returns the right number. Problem is the default formatting on the PivotTable is of type date, so the user has to change the format manually. This will likely change in the future allowing formatting to be defined in the measure definition.

I have attached a zip of the Excel file to this post (see below) in case you want to take a look at it.

Which is the most elegant? Which is the most intuitive? I’ll let you decide …

Excel Services JSOM - BI Example

christian.wade — Tue, 09 Nov 2010 22:28:00 GMT

As discussed in my previous post, I like Excel Services for a BI portal because it maximizes the opportunity for the end users to influence the content of the site. While most requirements can be met using a simple PerformancePoint filter with Excel Services web parts, there are a few more customized cases that cannot. But JSOM can do it.

As promised, this post provides sample (simplified) JSOM code to …

· Handle a user click event. The user wants to click on a member in one web part and filter another based on that selection.

· Conditionally render a chart based on the filter selection in another web part (e.g. the level of the currently selected filter member).

· Filter a child web part based on filter selection in another web part. We can’t use JSOM on a PerformancePoint page (hence no PerformancePoint filters), but we can use the Excel Services filter from one web part to filter another.

Let’s say you want a Sharepoint page that looks like this:

When the user selects a time period in the Excel Services filter (not PerformancePoint), the days for that time period are displayed in the lower web part.

When the user clicks on a product category, a different chart is shown below. It will show product ratio metrics instead of Internet Sales (as we are at a level in the product hierarchy low enough to do this).

To build this, I have an Excel file with an OLAP Formulas Mode grid (as opposed to a pivot table) as the header web part, and 2 pivot tables.

Header Web Part Grid

Pivot Table 1

Pivot Table 2

I removed the gridlines and column/row headers to make it prettier in the Sharepoint page.

Named Ranges and Parameters

I have some named ranges referring to the Header Web Part grid, each of the pivot tables, and the parameters for the pivot tables.

When publishing to Excel Services, I specified the parameter ranges.

The parameters only work with the cube unique member names. I had to do some jiggerypokery to get the unique member names of the current selections.

Firstly, I created a couple of calculated measures in the cube that return the unique member names of the currently selected members.

CREATE HIDDEN [Measures].[Calendar Unique Member Name] =

[Date].[Calendar].CurrentMember.Unique_Name;

CREATE HIDDEN [Measures].[Product Categories Unique Member Name] =

[Product].[Product Categories].CurrentMember.Unique_Name;

I have two hidden cells that get the unique member names as follows. The formula shown gets the value out of cell B1 and then gets the unique member name from the cube to be used as the parameter.

Note: it is better practice to just refer to cell B1 in the formula, rather than concatenate "[Date].[Calendar].[" + B1 + "]", because the member name is not guaranteed to be unique.

The 2^nd formula refers to cell B2, which by default has “All” in it. When the user clicks on one of the accessories, we can handle the click event in JSOM to populate B2 with either “Bike Racks”, “Bottles and Cages”, etc.

Note: in this case we have to concatenate the member name because unable to set the value of a cell to be a formula in JSOM.

The Webpart1_ByProductCategory_Param parameter in Excel Services refers to cell B2. It exists to allow population of cell B2 and thereby populate cell C2 with the unique member name for the product sub category that the user clicked on. Again, this is done in the JSOM event handler for the user click event.

Sharepoint Page

On the Sharepoint page, I have 2 Excel Web Access web parts. The top web part just displays the OLAP formulas named range (Webpart1); the other refers to the Pivot Table 1 named range by default (Webpart2_AllProducts). Both have the “Display Parameters Task Pane” property unchecked.

I also have a content editor web part at the bottom of the page, which refers to a javascript.js file (stored in a hidden document library).

Here is a little tip, which I’m sure will seem obvious once you do it. You can open Visual Studio, select File > Open > File and select the javascript file straight from the Sharepoint document library. You can edit it, save it, switch back to the page being developed, refresh it and reload the latest javascript (a lot easier than uploading the file every time!).

Here is the content of the javascript file.

// initialize variables

var EWA1 = null;

var EWA2 = null;

var ByProductCategoryParam = "";

var DateParam = "";

var NamedItemToLoad = "Webpart2_AllProducts";

//set page event handler for onload

if (window.attachEvent)

{

window.attachEvent("onload", PageLoad);

}

//proceed to the GetEWA function defined below once the Excel Services JSOM is 'ready'

function PageLoad()

{

Ewa.EwaControl.add_applicationReady(GetEwa);

}

//attach to the individual Excel Web Access (EWA) web parts

function GetEwa()

{

EWA1 = Ewa.EwaControl.getInstances().getItem(0);

EWA2 = Ewa.EwaControl.getInstances().getItem(1);

if (EWA1 && EWA2)

{

// add the event handler for when the workbook changes. We can handle it upon a dimension filter change.

EWA1.add_workbookChanged(workbookChangedHandler);

// add the event handler for when the user selects a cell

EWA1.add_activeSelectionChanged(activeSelectionChangedHandler);

refreshFilters();

}

// Event handler for the ActiveSelectionChanged event - when the user changes the selection in the EWA web part, this event is raised

function activeSelectionChangedHandler(rangeArgs) {

var sel = rangeArgs.getRange();

var values = rangeArgs.getFormattedValues();

var tempParam = "";

// Check to see if the user has clicked on one of the product sub categories

if (sel.getColumn() == 0 && sel.getColumnCount() == 1 && sel.getRow() >= 3 && sel.getRow() <= 14 ) {

tempParam = values[0][0]

NamedItemToLoad = 'Webpart2_ByProductCategory';

}

else {

// Set product category filter to all

tempParam = 'All'

NamedItemToLoad = 'Webpart2_AllProducts';

}

//Instead of setValuesAsync above, use parameter to apply product category

if (ByProductCategoryParam != tempParam)

{

var objCollection = {};

objCollection['Webpart1_ByProductCategory_Param'] = tempParam;

EWA1.getActiveWorkbook().setParametersAsync(objCollection, null, null);

ByProductCategoryParam = tempParam;

}

// Event handler for the WorkbookChanged event - fires upon user changing dimension filter in EWA1

function workbookChangedHandler(rangeArgs) {

refreshFilters();

}

function refreshFilters()

{

// Get the range of (hidden in cells C1:C2) filter values

var filterRange = EWA1.getActiveWorkbook().getRange("wp1", 0, 2, 2, 1);

//"wp1" is the name of the sheet (since beta doesn't work with named range anymore)

//syntax: getRange(parentName, firstRow, firstColumn, rowCount, columnCount) [zero based count; was 1 based in beta]

filterRange.getValuesAsync('unformatted', refreshFiltersAsync, filterRange);

}

// Event handler triggered WorkbookChanged event - to get the values from the filter section

function refreshFiltersAsync(result)

{

var values = result.getReturnValue();

var objCollection = {};

// Check if the filters have changed, otherwise no point in recalculating the whole EWA2 workbook

if (ByProductCategoryParam != values[0][0] || DateParam != values[1][0])

{

DateParam = values[0][0];

ByProductCategoryParam = values[1][0];

// alert("ByProductCategoryParam: " + ByProductCategoryParam);

// alert("DateParam: " + DateParam);

objCollection['Webpart2_AllProducts_Param1'] = ByProductCategoryParam; //must be the unique member name

objCollection['Webpart2_AllProducts_Param2'] = DateParam;

objCollection['Webpart2_ByProductCategory_Param1'] = ByProductCategoryParam;

objCollection['Webpart2_ByProductCategory_Param2'] = DateParam;

EWA2.getActiveWorkbook().setParametersAsync(objCollection, appliedFilter, null);

// Multi value params (user is brave enought to select multiple members)

// var objCollection = {};

// var vals = new Array(1);

// vals[0] = 'Jim';

// vals[1] = 'Jerry';

// vals[2] = 'Pooh';

// objCollection['ByProductCategoryParam']=vals;

// EWA2.getActiveWorkbook().setParametersAsync(objCollection, appliedFilter, null);

}

function appliedFilter(result)

{

// //1 failed - 0 succeeded

// alert(result.getCode());

//Now that filters are refreshed, load named item (otherwise it loads the different named item before applying the filter)

if(NamedItemToLoad != EWA2.getActiveWorkbook().getActiveNamedItem().getName())

{

EWA2.getActiveWorkbook().getNamedItems().getItemByName(NamedItemToLoad).activateAsync(null, null);

}

</script>

Here is a summary of the key methods

· GetEwa

o Sets up the references to the web parts.

o Hooks up the workbookChangedHandler to run every time the user changes a filter member.

o Hooks up the activeSelectionChangedHandler to run when the user clicks on a cell in the top web part.

§ The workbookChangedHandler also runs straight after activeSelectionChangedHandler when the user clicks a cell.

o Runs refreshFilters to ensure the lower web part is filtered by the selection in the top web part.

· activeSelectionChangedHandler

o When the user clicks on a cell, this method just populates cell B2 with the caption from the cell clicked on. It does this only in the top web part (does not touch the lower web part).

· workbookChangedHandler

o Just calls refreshFilters to ensure the lower web part is filtered by the selection in the top web part.

· refreshFilters

o In order to get the parameter values from the top web part, needs to first call getRange passing in a sheet name (the beta supported named ranges as well, but can only get it to work with a sheet name).

o Then call getValuesAsync to trigger refreshFiltersAsync to actually get the values.

· refreshFiltersAsync

o First detects if the filters have changed (it stores the previous selection in page level variables). If they have changed, uses the setParametersAsync method to pass the parameters to the lower web part.

Excel Services Programmability, JSOM aka ECMAScript, Excel as a BI Client Tool

christian.wade — Tue, 09 Nov 2010 21:35:00 GMT

Many posts in the blogosphere start like this: “It’s been a while since my last blog post …” I started writing this post the same way and realized that would be an understatement.

It’s been 4 years since my last blog post. I’m still here. Still the same person as I was before. Just been busy in other areas of my life like spending time with my wonderful wife and kids, working out (a lot), surfing (or trying to), skiing (or trying to), playing church-league softball (it’s nothing like cricket), enjoying life …

I like the focus on Excel as the primary Analytical BI client tool on the Microsoft stack. Excel is the tool the users know and love. They’ve been using it for up to 20 years, making them less averse to it than other tools. Plus it's on every desktop.

If the users author the Excel reports against the cube and save them to Sharepoint, they might still need some help from IT Support to get them published as a web page. Most of the (power) users I know would be comfortable saving a report they created to Sharepoint, but using PerformancePoint to create an Excel Services page with interaction between multiple web parts? Maybe.

Even so, I still anticipate that custom development, implying time-consuming release cycles and dependencies on report developers can go down by up to 80%, compared to say a Reporting Services based portal, which is by definition very development heavy. An end-user oriented, Excel Services focused BI portal will result in greater user involvement in the report-authoring process.

I think about 80% of Excel Services reports can be delivered easily using PerformancePoint for filtering and web-part layout.

· The Sharepoint filters are clunky to say the least; I don’t like them.

· The PerformancePoint (AJAX like) partial page loading provides a nice user experience.

· The ability to base the filter set on MDX gives flexibility. For example show a rolling 1 year window of dates rather than all dates from/to the beginning/end of time (in the cube of course).

What about the remaining 20%? What about when you need that extra bit of user interactivity that doesn’t quite come out of the box? That’s where Excel Services JSOM (aka ECMAScript) comes in. It addresses the remainder quite well, but is Javascript based (no one is perfect).

Here are some use cases that cannot be addressed with PerformancePoint leveraging Excel Services – but can be delivered using JSOM.

· Conditional rendering of chart/grid based on current member selection in the filter. For example, if the user selects a low enough level in the filter, we would like to display a different chart/grid in the child web part that shows different, low-level measures.

· Handling user click event. What if the user wants to click on a member in one web part and filter another based on that selection?

· Multi-webpart filtering when using JSOM. Unfortunately, JSOM doesn’t work on a PerformancePoint page, so you are stuck with the Sharepoint filter (yuck!). Or alternatively, you can use the Excel Services filter from one web part to filter the other – another JSOM use case.

· Formulas mode works nicely with JSOM for customized interactivity.

I was shocked to find there is almost no documentation on the web for JSOM. When evaluating Sharepoint 2010 on the Beta program, I assumed MSDN just hadn’t been updated yet. That is understandable for a Beta product. However, even today, an MSDN JSOM page still looks like this …

Very helpful! Especially when we are talking about a Javascript object model, which is by definition one of the most difficult things to debug, or take a trial/error approach to.

Most of the information out there is currently in a few good blog posts by Shahar Prish – http://blogs.msdn.com/b/cumgranosalis/ I’d like to add a few more examples!

In my next post, I will address each of the use cases listed above with sample code.

Databind ChartFX to OLAP Datasource

christian.wade — Wed, 08 Nov 2006 17:51:00 GMT

I’ve just gone through an informal and limited evaluation process for a charting tool for a BI dashboard. It included some of the RS extension products, ProClarity and ChartFX. Of course I would have been keen to use ProClarity, but in this case the cost was a little high and, to be honest, it would have been a sledgehammer to crack a nut.

ChartFX is the charting engine used by ProClarity. It looks and feels exactly the same as ProClarity charts. In theory, everything you can do with ProClarity charts, you can do with ChartFX – you just have to code it yourself (which is a lot easier than it sounds).

Anyway, when evaluating ChartFX, I initially looked at the OLAP extension to it. Seemed to work great at first and I was very impressed with it. However, when I got to doing anything a little out of the ordinary like multiple Y axes or specifying X axes labeling, the code simply didn’t work! For example, the code at the bottom of the following snippet has no effect.

AdoMultiDimensionalData ADOMD1 = new AdoMultiDimensionalData();

string connstr = "Provider=msolap; Data Source=localhost;Initial Catalog=Adventure Works;";

string ds =

@"SELECT

[Date].[Calendar].[Calendar Year].Members ON 0,

{

[Measures].[Internet Sales Amount],

[Measures].[Internet Freight Cost]

} ON 1

FROM

[Adventure Works];";

ADOMD1.Connect(connstr, ds);

Olap1.DataSource = ADOMD1;

// This code hides the Analysis Bar

Olap1.AnalysisBar = AnalysisBar.Menu;

ToolBar tb = (ToolBar)Olap1.AnalysisObject;

tb.Visible = false;

Chart1.Gallery = Gallery.Bar;

// Code that doesn't work!

AxisY addlAxisY = new AxisY();

addlAxisY.Visible = true;

addlAxisY.Position = AxisPosition.Far;

Chart1.AxesY.Add(addlAxisY);

Chart1.Series[0].AxisY = addlAxisY;

The reason for the code not working is that the OLAP extension uses databinding, which overrides code such as this. Here’s what the chart looks like.

The way I got round this problem was to not use the OLAP extension at all. Instead, I am using the ChartFX API to iterate an ADOMD.NET CellSet and set the chart data. Here is the code; it works fine for MDX with 2 axes. You could of course take it a little further to display crossjoined sets, etc. Here is the code.

using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial Catalog=Adventure Works;"))

{

conn.Open();

//Create a command, using this connection

AdomdCommand cmd = conn.CreateCommand();

cmd.CommandText =

@"SELECT

[Date].[Calendar].[Calendar Year].Members ON 0,

{

[Measures].[Internet Sales Amount],

[Measures].[Internet Freight Cost]

} ON 1

FROM

[Adventure Works];";

//Execute the query, returning a cellset

CellSet cs = cmd.ExecuteCellSet();

//Get back rows representing each series

TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;

//Set the number of series

Chart1.Data.Series = tuplesOnRows.Count;

//Get back columns representing each x axis category

TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;

Chart1.Data.Points = tuplesOnColumns.Count;

//Set the data values and series names

for (int row = 0; row < tuplesOnRows.Count; row++)

{

Chart1.Series[row].Text = tuplesOnRows[row].Members[0].Caption;

for (int col = 0; col < tuplesOnColumns.Count; col++)

{

Chart1.Data[row, col] = Convert.ToDouble(cs.Cells[col, row].Value);

}

//Set the x axis category names

for (int i = 0; i < tuplesOnColumns.Count; i++)

{

Chart1.AxisX.Labels[ i ] = tuplesOnColumns[ i ].Members[0].Caption;

}

conn.Close();

} // using connection

//Set chart type

Chart1.Gallery = Gallery.Bar;

//Create secondary y axes series and its appearance properties

AxisY addlAxisY = new AxisY();

addlAxisY.Visible = true;

addlAxisY.Position = AxisPosition.Far;

Chart1.AxesY.Add(addlAxisY);

Chart1.Series[1].AxisY = addlAxisY;

Here’s what the chart looks like.

We now have an AJAX enabled UI with blistering performance. Changing the slicing of the chart has it refresh instantly. No more RS spinnies!

SQL Server Standard - Recursive Hierarchies to XML

christian.wade — Wed, 20 Sep 2006 14:21:00 GMT

I wrote an article for the September 2006 issue of SQL Server Standard magazine:

The version that was finally published was a very cut down version with minimal code snippets. This was for obvious reasons; they had to fit the content onto small columns in the magazine. For the readers who would prefer the more verbose version, here it is! I guess this is further reference material for the published article; I’m sure my friends at SQL Server Standard won’t mind.

Suppose we have a sizeable recursive hierarchy in our SQL Server 2005 relational database. We want to export it to XML. This could be for a variety of reasons. We may wish to use it as a pre-cached input to a UI control (e.g. a tree control); we may wish to export it to another system using a predefined format. The possibilities are endless.

Here is an example input dataset using the Northwind Employees table.

And this is how we want it represented in XML based on the ReportsTo self-referencing relationship.

</Employee>

</Employee>

On the face of it, this seems like a simple nut to crack. However, there are various design options available to us. This article explores some of the options. Performance is deemed the overriding factor when evaluating the optimal approach.

It is assumed that it is beneficial to convert the data to XML in the database; i.e. in-process with SQL Server. However, the overall evaluation of the different approaches would be the same even if the transformation to the desired format took place in the middle tier.

All the code in this post can be downloaded from here. This is a link to a zip file containing a solution and a SQL Server project. To use it, it is necessary to change the database connection string in the project properties. The TSQL scripts that generate the XML output can be found in the Option1.sql and Option2.sql solution files.

Note: A Northwind database restored to SQL Server 2005 is also required to run the code. It is necessary to build and deploy the above project. A backup of Northwind from SQL Server 2005 can be downloaded from here.

What were the options we had in SQL Server 2000 for exporting relational data to XML? We had the FOR XML clause of the SELECT statement. Incidentally, SQL Server 2005 has a new mode called FOR XML PATH. This mode is powerful because it allows us to use XPath-type syntax to define our XML structure. FOR XML PATH will meet about 95% of the use cases for which we traditionally had to resort to FOR XML EXPLICIT. FOR XML PATH is a lot less cumbersome and easy to use. However, both these options require us to know the depth of the lowest level node when writing the query. For recursive hierarchies, we do not actually know the lowest depth beforehand because it is variable. This means it is not feasible to use FOR XML and directly arrive at our desired XML output. The best we can do with FOR XML is to run the output through an XSLT transform.

Option 1: FOR XML AUTO and XSLT

The SQL Server 2005 functionality for XML provided through XQuery and XML indexing can be considerably enhanced by using SQL CLR to apply XSLT transformations. Here we are using a SQL CLR function to convert the output of a FOR XML AUTO query against our Northwind dataset in order to arrive at the desired output.

DECLARE @input xml, @stylesheet xml

SELECT @input =

(SELECT EmployeeID, ReportsTo, LastName, FirstName

FROM Employees AS Employee

FOR XML AUTO, ROOT('Employees')),

@stylesheet =

CAST(

'<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/Employees" >

<xsl:apply-templates select="Employee[not(@ReportsTo)]" />

</xsl:template>

<xsl:template match="Employee[not(@ReportsTo)]">

<xsl:element name="Employee">

<xsl:attribute name="EmployeeID">

<xsl:value-of select="@EmployeeID" />

</xsl:attribute>

<xsl:attribute name="LastName">

<xsl:value-of select="@LastName" />

</xsl:attribute>

<xsl:attribute name="FirstName">

<xsl:value-of select="@FirstName" />

</xsl:attribute>

<xsl:call-template name="FindChildNodes">

<xsl:with-param name="ReportsTo" select="@EmployeeID" />

</xsl:call-template>

</xsl:element>

</xsl:template>

<xsl:template name="FindChildNodes">

<xsl:param name="ReportsTo" />

<xsl:for-each select="//Employees/Employee[@ReportsTo=$ReportsTo]">

<xsl:variable name="EmployeeID" select="@EmployeeID" />

<xsl:element name="Employee">

<xsl:attribute name="EmployeeID">

<xsl:value-of select="@EmployeeID" />

</xsl:attribute>

<xsl:attribute name="LastName">

<xsl:value-of select="@LastName" />

</xsl:attribute>

<xsl:attribute name="FirstName">

<xsl:value-of select="@FirstName" />

</xsl:attribute>

<xsl:if test="count(//Employees/Employee[@ReportsTo = $EmployeeID]) > 0">

<xsl:call-template name="FindChildNodes">

<xsl:with-param name="ReportsTo" select="@EmployeeID" />

</xsl:call-template>

</xsl:if>

</xsl:element>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

' AS xml)

SELECT dbo.ApplyTransform(@input, @stylesheet)

Further discussion regarding the implementation of the XSLT stylesheet is outside the scope of this article.

Here is an implementation of the ApplyTransform function that receives the stylesheet. It basically just exposes the System.Xml.Xsl.CompiledTransform class’ Transform method to TSQL.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Xml;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Server;

…

[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = false, IsPrecise = false)]

[return: SqlFacet(IsFixedLength = false, MaxSize = -1)]

public static SqlXml ApplyTransform(SqlXml data, SqlXml styleSheet)

{

// Create a stream for the xml output

MemoryStream ms = new MemoryStream();

XmlWriter xw = XmlWriter.Create(ms);

// Load the XML and transform it

XslCompiledTransform ctx = new XslCompiledTransform(false);

ctx.Load(styleSheet.CreateReader());

ctx.Transform(data.CreateReader(), xw);

// return the output

return new SqlXml(ms);

}

Assuming some prerequisite knowledge of XSL stylesheets, this appears to be a good option to achieve our goal. However, let us evaluate others before deciding the optimum choice.

We could of course simply access the relational data in a SQL CLR stored procedure and write it to XML. However, if we don’t access the data in the same order it will appear in the XML, we will have to load the whole structure into the DOM. Loading the whole structure into the DOM means loading it into memory; if we are talking about a million rows, we may well not have enough memory available! The ‘right order’ is as follows. This allows us to write it to XML sequentially, which is vastly more performant and efficient.

Node1

Node2

Node3
Node4

Node5

How can we return the data in this order from a single TSQL query? Probably the best option is to use a recursive Common Table Expression (CTE). CTEs are a new feature in SQL Server 2005. They can be conceptualized similarly to views and derived tables in that they encapsulate an inner query. However, they differentiate themselves from views and derived tables in the way they deal with recursion. The syntax for a recursive CTE is as follows.

WITH RecursiveCTE(<column_list>)

(

-- Anchor Member:

SELECT ...

FROM <some_table(s)>

...

UNION ALL

-- Recursive Member

SELECT ...

FROM <some_table(s)>

JOIN RecursiveCTE

...

)

-- Outer Query

SELECT ...

FROM RecursiveCTE

...

The WITH clause is the definition of the CTE and it precedes the outer query, which refers back to the CTE. Within the WITH clause, the anchor member is a SELECT statement that acts as the seed for recursion. It is merged using the UNION ALL operator to the recursive member, which is a SELECT statement that refers back to the CTE; hence it is recursive.

Note: it was possible to return the data in the ‘right order’ from a single TSQL query in SQL Server 2000 using a recursive table-valued user-defined function. I tried this approach and the performance is far worse than the recursive CTE. For large datasets, the execution time was multiplied by a factor of at least 5! For example code of how this would be done using a recursive table-valued user-defined function, see this old blog post of mine.

Note: this whitepaper describes a way of avoiding using a table-valued UDF. It does however use a scalar UDF, so it is still limited to 32 nested recursions. If you know your hierarchies will never exceed 32 nested recursions, the scalar UDF method is probably a good way to go. However, given the nature of recursive hierarchies, this may be an assumption you cannot make.

Note: recursive CTEs are by default limited to 100 nested recursions. However, this only acts as a safeguard against infinite recursion. This limit can be changed using the MAXRECURSION query hint to anything up to 32,767 nested recursions. Recursive table-valued user-defined functions, on the other hand, are limited to 32 nested recursions.

Note: FOR XML cannot be used by the anchor or recursive member SELECT statements in a recursive CTE. This is why we need a CLR routine to present the data as XML.

Option 2: Recursive CTE and sequential writing of XML

The usp_OrderedEmployeeHierarchy stored procedure below returns the data in the order we require.

CREATE PROCEDURE usp_OrderedEmployeeHierarchy (@Seed int)

;WITH EmployeeCTE(EmployeeID, ReportsTo, LastName, FirstName, Depth, SortCol)

(

SELECT EmployeeID, ReportsTo, LastName, FirstName, 0, CAST(EmployeeID AS varbinary(max))

FROM Employees

WHERE EmployeeID = @Seed

UNION ALL

SELECT E.EmployeeID, E.ReportsTo, E.LastName, E.FirstName, M.Depth+1,

CAST(SortCol + CAST(E.EmployeeID AS binary(4)) AS varbinary(max))

FROM Employees AS E

JOIN EmployeeCTE AS M

ON E.ReportsTo = M.EmployeeID

)

SELECT

EmployeeID, ReportsTo, LastName, FirstName, Depth

--,SortCol

FROM EmployeeCTE

ORDER BY SortCol

When passing a @Seed parameter of 2, the following dataset is returned.

The way the CTE handles ordering the data is through the SortCol column. For the seed of recursion, this column is the EmployeeID cast to varbinary(max). For each subsequent recursive member, it is the existing SortCol value concatenated with the binary representation of the EmployeeID. If we include it in the output, the following dataset is returned.

As shown, the SortCol column provides a useful structure on which we can sort the dataset. The data is thereby retrieved in the required order.

The Depth column is initialized at zero for the seed of the recursion and then incremented by one for each recursion.

This is the SQL CLR stored procedure that writes the XML sequentially.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Xml;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Server;

…

[Microsoft.SqlServer.Server.SqlProcedure(Name = "usp_GetEmployeeHierarchyXml")]

public static void GetEmployeeHierarchyXml(SqlInt32 seed)

{

// Create a stream for the xml output

MemoryStream ms = new MemoryStream();

XmlWriter xw = XmlWriter.Create(ms);

// Depth counters to identify when to close elements

int previousDepth = -1;

int newDepth = 0;

using (SqlConnection conn = new SqlConnection("context connection=true"))

{

conn.Open();

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "usp_OrderedEmployeeHierarchy";

cmd.Parameters.Add(new SqlParameter("@Seed", seed));

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

// Get the depth of the current node

newDepth = Convert.ToInt32(reader["Depth"]);

// Do we need close an open element(s)?

CloseOpenElements(xw, previousDepth, newDepth);

xw.WriteStartElement("Employee");

xw.WriteAttributeString("EmployeeID", Convert.ToString(reader["EmployeeID"]));

xw.WriteAttributeString("LastName", Convert.ToString(reader["LastName"]));

xw.WriteAttributeString("FirstName", Convert.ToString(reader["FirstName"]));

previousDepth = newDepth;

}

// Now we are back at depth 0; do we need close an open element(s)?

newDepth = 0;

CloseOpenElements(xw, previousDepth, newDepth);

xw.Close();

// Return the results to the client

SqlDataRecord record;

record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("EmployeeHierarchy", SqlDbType.Xml) });

// Set the record field.

record.SetSqlXml(0, new SqlXml(ms));

// Return the record to the client.

SqlContext.Pipe.Send(record);

}

private static void CloseOpenElements(XmlWriter xw, int previousDepth, int newDepth)

{

if (newDepth <= previousDepth)

for (int i = 0; i <= previousDepth - newDepth; i++)

xw.WriteEndElement();

}

The GetEmployeeHierarchyXml stored procedure executes the usp_OrderedEmployeeHierarchy stored procedure for data access. A System.Xml.XmlWriter object is used to sequentially write the XML. The Depth column is used to keep track of when to close open XML elements. A Microsoft.SqlServer.Server.SqlDataRecord object is used to return a one-row, one-column, XML-datatype result set to the client. This is done using the static Send method, which is a member of the Microsoft.SqlServer.Server.SqlContext class’ Pipe property. The SqlContext class is a class dedicated solely to in-process data access. It contains other interesting properties and methods, which are outside the scope of this article.

Executing usp_GetEmployeeHierarchyXml returns the XML in the desired format.

Some readers may be asking the question as to why we do not simply write a SQL CLR stored procedure that retrieves a set of child nodes at a time for every single node in the hierarchy. This does mean a lot more TSQL queries being executed, but is conceptually the simplest approach. However, this is not feasible because multiple active resultsets (MARS) is not supported in SQL CLR. We would therefore have to instantiate many connection objects. This also presents a problem because we can only have one open ‘context’ connection at a time. I did attempt this approach processing the resultset in a client-side application and the performance was far worse than Option 1 or 2. This approach has therefore been ruled out.

So, here is the question we’ve all been waiting for: which is the most efficient choice? I tried both approaches on a product hierarchy, which happened to originate from Analysis Services, containing 8,700 members. Here are the findings. Note that Option 2 assumes a cached execution plan for the usp_OrderedEmployeeHierarchy stored procedure.

Option 1: 38 seconds

Option 2: 15 seconds

This is a performance gain of over 60% !!

We can therefore conclude that CTEs provide a well architected method for accessing recursive relational data. A prime example of this is when exporting such data to XML.

Currency Conversion in Analysis Services

christian.wade — Thu, 24 Aug 2006 15:23:00 GMT

This article was originally written for Analysis Services 2005, but everything still applies today even for Analysis Services 2012 in Multidimensional mode.

First thing to do: figure out what type of currency conversion you need. There are 3 types.

MANY-TO-MANY. This is where the facts are stored in multiple currencies – i.e. amounts for the same measure are in various currencies in the same fact table. Also, the users might want to report the total amount in different currencies.
MANY-TO-ONE. The facts are stored in multiple currencies, but this time it is only necessary to ever report the total amount in a single currency – e.g. a corporate currency.
ONE-TO-MANY. The facts are always stored in one currency only. However, the users might want to report the total amount in different currencies.

This post focuses on the different options available for One-to-Many. With One-to-Many, there are 2 options: measure expressions or the currency conversion wizard.

If you create a DSV on top of AdventureWorksDW and include DimTime, FactInternetSales, FactCurrencyRate and DimCurrency, it should look something like this.

The relationship between FactInternetSales and DimCurrency is for the case where your facts are stored in multiple currencies (Many-to-Many and Many-to-One). To look at One-to-Many, we will delete this relationship. Also, we will delete the ShipDate and DueDate relationships between FactInternetSales and DimTime, but leave the OrderDate relationship (this post is about currency conversion, not role-playing dimensions!).

We now have the following DSV diagram.

ONE-TO-MANY USING MEASURE EXPRESSIONS

I created a cube on top of this DSV. Here is a backup of it http://blogs.conchango.com/christianwade/CurrencyConversionBackup.zip You can download, restore and open it using the VisualStudio using the File > Open > Analysis Services Database menu option.

The only measures in it are Sales Amount (from FactInternetSales) and End Of Day Rate (from FactCurrencyRate). Sales Amount has a FormatString of “Currency”. End Of Day Rate has an AggregateFunction property value of “LastNonEmpty”. LastNonEmpty is a semi-additive measure. We want it to sum for all dimensions except Date. For the Date dimension, it will take the last non-empty child. For example, the exchange rate we would want to see for a week member in a report would not be the sum of the exchange rate on Monday + exchange rate on Tuesday + exchange rate on Wednesday, … Instead, we just want the most recent exchange rate (maybe exchange rate on Saturday).

The Date dimension is very simple. The Date attribute, which is the key attribute, has a KeyColumns property of TimeKey (which is the surrogate key of the DimTime table) and a NameColumn of FullDateAlternateKey (what the users see at Date level).

I used the Dimension Wizard to create the Date dimension so that it would be flagged with Type = Time, etc. This is one of the few cases where having these properties set correctly actually affects cube behaviour. For example, semi-additive measures and some MDX functions like YTD won't work without Type = Time.

Here are the mappings in the Dimension Usage tab. They are pretty straightforward. There is a many-to-many relationship between Currency and Fact Internet Sales.

Here is a screenshot of the Currency dimension. The main points about the Currency dimension are as follows.

The KeyColumns property of the Currency attribute is set to CurrencyKey (which is the surrogate key in for the DimCurrency table in AdventureWorksDW).
The NameColumn property of the Currency attribute is set to CurrencyName (which is what we want the users to see).
The Type property of the dimension is set to Currency. The only purpose of this (unlike Type=Time that does affect cube behaviour) is to inform client tools in case they want to display currency dimensions differently to regular dimensions.
The Type property of the Currency attribute is set to CurrencyName. Again, this is just to inform client tools.
The IsAggregatable property of the Currency attribute is set to False. This removes the All level for the attribute. We would after all not want to sum the values of different currencies. 500 pounds sterling plus 100,000 cruzeiros equals 100,500 of what currency? Monopoly money?
Set the DefaultMember property of the Currency attribute to whatever the Sales Amount values are stored in. In the case of AdventureWorksDW, it is US Dollars.
The attribute relationship between Currency and Currency Alternate Key has its Cardinality property set to One. This is because, for a particular Currency, there can be only one Currency Alternate Key – i.e. they have a one-to-one relationship. This improves efficiency in aggregation because the numbers will always be the same (e.g. £500 for “Great Britain Pounds” will always result in £500 for “GBP”). Analysis Services will therefore not bother figuring out the aggregate values for Currency Alternate Key. It will simply re-use those of Currency.
Set the AttributeHierarchyEnabled property of the Currency Alternate Key attribute to False. If it is not set, Analysis Services will not allow deployment of the project because of the attribute relationship with Currency and that Currency has IsAggregatable = False.

Here is the measure expressions bit. The Sales Amount measure has the following measure expression: “[Sales Amount] / [End Of Day Rate]”. The facts are stored in US Dollars and the “pivot currency” is US Dollars. The pivot currency is the currency the exchange rate values convert from. The measure expression is a division rather than a multiplication because this is the way the exchange rates are held. For example, the exchange rate for GBP is roughly 1.5 in the FactCurrencyRate table. Therefore, to convert $15 from the pivot currency to GBP, 15 / 1.5 gives us 10. Doing a multiplication would result in £22.50 (obviously wrong). Note: measure expressions are done at leaf level.

It is worth going back into the Dimension Usage tab and setting the DirectSlice property of the many-to-many relationship. Setting it to “([Currency].[Currency].&[100])” means that, when querying Sales Amounts by the base/pivot currency (US Dollars), Analysis Services will just return the value as it appears in the fact table – i.e. without applying the measure expression. After all, there is no need to convert US Dollars into US Dollars! If we did not set DirectSlice, we would have to ensure that the FactCurrencyRate table has an exchange rate of 1 for converting US Dollars into US Dollars (and for every single day for which we have data). Otherwise, Analysis Services would be looking for an End Of Day Rate to divide by, find null and return null. It should also perform better when querying by USD with DirectSlice populated correctly because it doesn't have to bother with the conversion calculation at all. So what we want is just a tuple with the default member for every enabled attribute in the outer many-to-many dimension. Incidentally, this is what the Root(<dimension_expression>) function would return, but we can't use any MDX functions in the DirectSlice property (hence the Functions pane is disabled), so we have to explicitly list each default member in a tuple.

Lastly, we will insert some MDX in the Calculations tab that sets the Locale ID for currencies. This will avoid displaying 500 pounds sterling as “$500” (goodness gracious!). For a complete list of the Locale IDs, see http://www.microsoft.com/globaldev/reference/lcid-all.mspx

Language([Currency].[Currency].[United Kingdom Pound]) = 2057;
Language([Currency].[Currency].[Brazilian Real]) = 1046;
Language([Currency].[Currency].[US Dollar]) = 1033;

These assignments are for illustration purposes only. The main problem with this approach is maintainability. If we bring in new currencies in the future, we need to modify the MDX script. The ideal would be to store them in the DimCurrency table, expose them using the ValueColumn property of the Currency attribute, and use that for the assignment. Some may prefer to go the member property route, but I think this is a good use case for the ValueColumn property.

Language(([Currency].[Currency].Members, [Measures].[Sales Amount])) =
[Dim Currency].[Currency].CurrentMember.MemberValue;

Let’s build and deploy! Now let’s browse!

On the face of it, these numbers seem OK. 29,358,677 is roughly 1.5 times 19,685,311. But let’s double check. Copying and pasting into Excel reveals that this is not the case. There is actually a discrepancy of £784,195! Why is that? Well, this is actually the behaviour that we want …

If you remember, measure expressions are done at leaf level. This calculation is, as George Spofford would say, “non-commutative”. Addition/subtraction combined with multiplication/division will result in different numbers depending on whether the multiplication/division is done at leaf level or the aggregated level. Analysis Services has done the currency conversion at leaf level – i.e. at the day that each transaction actually took place. This is of course much more accurate.

So let’s test this as well. Drilling into Date reveals the following.

Copying and pasting the Date-level data into Excel and applying the multiplication reveals that the numbers are indeed calculated correctly at leaf level.

For one-to-many currency conversion, I prefer using measure expressions rather than the currency conversion wizard. As shown by this post – http://blogs.conchango.com/christianwade/archive/2006/07/25/4256.aspx – measure expressions are the most performant way of doing this kind of calculation. The wizard generates MDX that does the calculation at runtime, so it is not quite as performant for large volumes. Furthermore, virtually all the work we have just gone through to enable measure-expression currency conversion would also have to be done prior to running the wizard (with the exception of the measure expression itself). Anyway, for completeness, let’s take a look at how the currency conversion wizard does it.

ONE-TO-MANY USING CURRENCY CONVERSION WIZARD

Picking up where we left off, just delete the measure expression and run the wizard. As I said, pretty much all the other stuff we had to do to enable measure expressions has to be done prior to running the wizard anyway.

From the Cube Structure tab, select Cube > Add Business Intelligence.

Select “Define Currency Conversion” and click Next.

Fill in the next page in the wizard as shown here. Specify which measure group contains the exchange rates. The pivot currency, as discussed above, is US Dollars. “1.5 US Dollars per 1 United Kingdom Pound” sounds right, so let’s go with that.

Select Sales Amount as the measure(s) we want to convert.

Specify One-to-many currency conversion.

Now it wants us to specify the “reporting currencies”. It will actually create another currency dimension called “Reporting Currency”, which will be based on this selection. I would imagine that most cubes that only need one-to-many currency conversion will have the base Currency dimension purely for the purpose of viewing the values in foreign currencies! Adding another currency dimension can be a little overkill in my opinion. I selected United Kingdom Pound and Brazilian Real for my reporting currencies.

The last page shows us what changes will be made to the cube/DSV. Click Finish.

Let’s take a look at what it actually did. Firstly, we have a new entity in our DSV called Reporting Currency. It is basically the same as the FactCurrencyRate, but filtered on the pivot currency and the other currencies we selected as reporting currencies. Interestingly, it is not related to any other entity in the DSV.

The wizard also created a dimension called Reporting Currency that is based on the Reporting Currency entity in the DSV. It is very similar to our Currency dimension. The Currency attribute has a DefaultMember property of US Dolllars, its IsAggregatable property is set to False, etc., etc. In fact the only difference of any significance is that it has not set the Cardinality property of the attribute relationship to One.

And here is the MDX script it generated. Note: there is a bug in this script. If you are using a Date dimension that is called something different to the base dimension name (e.g. Order Date vs. Date), you will have to replace “Scope( Leaves([Date])” with “Scope( Leaves([Order Date])”.

// <Currency conversion>

// Currency conversion wizard generated script.

// Currency conversion generated on: 24 August 2006 15:36:38

// by user: Conchango2

// Currency conversion type: OneToMany

// Selected members to be converted: Sales Amount

// Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again.

// All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension

Scope ( { Measures.[Sales Amount]} );

Scope( Leaves([Date]) ,

Except([Reporting Currency].[Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[US Dollar]));

// This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate End Of Day Rate

// LinkMember is used to reference the currency from the source currency dimension in the rate cube.

Scope( { Measures.[Sales Amount]} );

This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

End Scope;

End Scope; // Leaves of time and non pivot currency

End Scope; // Measures

// End of the currency conversion wizard generated script

// </Currency conversion>

Having done some (thorough) scoping, the line of code that is of real interest is

This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;

This line is doing the division in a similar way to the measure expression. However, it is using the LinkMember function to do a runtime link between Currency and Reporting Currency based on the member name. This is effectively just replacing the Currency dimension with the Reporting Currency dimension for reporting purposes.

SUMMARY

As I said, if all you have is a one-to-many currency conversion requirement, I think the measure expressions approach is more elegant. It stores the calculated data on disk rather than calculating at runtime, so it should perform a little better for large volumes (despite the fact that cell assignments in MDX script do perform very well). Also, there is only one currency dimension (instead of Currency and Reporting Currency). For many cubes that just require one-to-many currency conversion, this is a simpler model. Plus a lot of the work you have to do to use the measure expressions approach has to be done anyway in order to run the wizard. This is in contrast to the Time Analysis wizard, which I think adds great value. I think the currency conversion wizard adds more value for many-to-many currency conversion. In this case, you would invariably want the 2 currency dimensions.

Analysis Services Stored Procedure Project

christian.wade — Mon, 14 Aug 2006 13:52:00 GMT

Jon Axon, Chris Webb, Mosha Pasumansky, Greg Galloway, Darren Gosbell (and I although not as much as these guys!) have released some very interesting and useful MDX sprocs. Check out http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures

Enjoy!

Measure expressions: how performant are they?

christian.wade — Tue, 25 Jul 2006 16:36:00 GMT

Oh, and before the anoraks out there mail me saying “did you know that ‘performant’ is not recognized as an adjective by the major dictionaries?”, yes I do know – but I like the word, so I’m using it!

We are modeling the business of the Multi-Dimensional Parcel Service (MDPS).

They ship parcels domestically, internationally (and between dimensions). We are only concerned with the operations of their trucks. The trucks either

· Collect parcels from depots to deliver to customers

· Collect parcels from customers to deliver to depots

The customer pays for the delivery/collection, so revenue can be associated with a customer. The job of collecting the parcels and delivering them has costs associated with it – e.g. petrol. Costs are therefore associated with a job.

It is common for a truck to be part loaded in order to serve multiple customers. In this case there would be multiple delivery/collection addresses for the same job. It is therefore conceivable that we could have the following data in our Revenue table.

The parcels for JobID of 1 were delivered to two customers (those with CustomerIDs of 1 and 2). However, Customer 1 only took up a small amount of space in the truck. The job took place in December and he was shipping Halloween decorations so there wasn’t much demand. Customer 2, on the other hand, was shipping Christmas trees, so he used the vast majority of the truck’s space.

How then should we assign costs to a customer? You have probably already noticed that Customer is a many-to-many dimension with regard to Cost. A customer can have many jobs performed, and a job can be done for more than one customer at a time.

In our example, the total cost of Job 1 (JobID of 1) is £500. With the default behaviour of many-to-many dimensions (i.e. no measure expression or MDX to split the costs), both customers 1 and 2 would be assigned a cost of £500. This seems a little unfair – especially for Customer 1 because he only used a small part of the truck space. It is a business decision as to how to split costs in this type of scenario. In this hypothetical case, it makes sense to do a proportional split based on revenue.

This is what the CostMultiplier field is for. For a given JobID, the sum of CostMultiplier always equals 1. So, for the first row, 500 / 5,000 is 0.1. For the second row, 4,500 / 5,000 is 0.9. For the third row, 3,000 / 3,000 is 1.

Using MDX or a measure expression, we can multiply the cost for a customer by the CostMultiplier. The following screenshot is therefore how an OLAP client tool would present the costs in our example.

Note: doing the proportional split based on revenue results in the data being aggregated using a regular sum. The cost for Customer 1 + Customer 2 + Customer 3 is £800. £800 is also the actual total cost of Job 1 and Job 2. Summing the individual customers using the many-to-many dimension without the proportional split would have resulted in £1,300 (£500 + £500 + £300).

Note: the proportional split has to be done at leaf level. This is because the calculation is, as George Spofford would say, “non-commutative”. It involves addition/subtraction and multiplication/division, so we will get different numbers at the aggregated level depending on whether or not the calculation is done at leaf level.

So, let’s do it! I created a relational database containing the above 4 tables and populated it with (quite a lot of) data. I then created a cube on top of it to allow testing of how performant the different design options are.

You can download a backup of the cube that uses calculated members (Option 1 and 2) from here. For a backup of the cube that uses the measure expression (Option 3), download from here.

Here is the metadata of the cube.

Here are the numbers of rows for each table

Customer – 53,186 rows

Revenue – 4,370,206 rows

Job – 979,921 rows

Cost – 334,342 rows

To populate the CostMultipler field, we can run the following query.

UPDATE Revenue

SET CostMultiplier =

CASE

WHEN dt.Amount = 0 or dt.Amount IS NULL THEN 0

ELSE r.Amount / dt.Amount

END

FROM Revenue r

INNER JOIN

(

SELECT JobID, SUM(Amount) Amount

FROM Revenue r

GROUP BY JobID

) dt ON r.JobID = dt.JobID

Now, when we run the following query, we should get back 1 for every single JobID.

SELECT

JobID,

SUM(CostMultiplier)

FROM

Revenue

GROUP BY

JobID

I set the attribute relationship’s Cardinality and RelationshipType properties to improve performance (see this post). I also of course designed aggregations on the 2 measure group partitions.

Various options are evaluated for performance in getting the costs by customer including the proportional split. It is assumed that we do not have a query cache when testing performance. The AS query cache can be cleared by running an XMLA command as pointed out by Darren Gosbell here.

OPTION 1 – TRADITIONAL STYLE MDX

I created a calculated measure called “Cost – Traditional MDX”. It takes the traditional approach of defining the calculation in the member declaration. See the MDX below. Obviously only the calculated measure would be exposed in the perspectives; not the base measure.

Note: this will only work if the only exposed hierarchy from the [Customer] dimension is [Category – Customer].

CREATE MEMBER CURRENTCUBE.[Measures].[Cost - Traditional MDX]

IIf

(

[Customer].[Category - Customer].CurrentMember.Level IS

[Customer].[Category - Customer].[(All)],

// If at the all level of customer, just return the hidden base measure

[Measures].[Cost],

// Otherwise split the cost at leaf level proportionally to revenue

Sum

(

Descendants

(

[Customer].[Category - Customer].CurrentMember, , LEAVES

) *

Descendants

(

[Job].[Reference].CurrentMember, , LEAVES

([Measures].[Cost] * [Measures].[Cost Multiplier])

)

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Cost] },

VISIBLE = 1;

Guess how long the following query takes (on my dev VPC)? About 1 minute 15 seconds. There are only about 100 rows returned. The reason it takes so long is that it has to go down to leaf level at runtime for all the Job/Customer combinations.

SELECT

[Measures].[Cost - Traditional MDX] ON 0,

{

[Customer].[Category - Customer].[All],

[Customer].[Category - Customer].[All].Children

} ON 1

FROM

[Measure Expressions]

OPTION 2 – MDX SCRIPT

We can improve on Option 1 using MDX script assignments.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost - MDX Script]

[Measures].[Cost],

FORMAT_STRING = "Currency",

NON_EMPTY_BEHAVIOR = { [Cost] },

VISIBLE = 1;

SCOPE ([Measures].[Cost - MDX Script]);

// Assign to all cells in the [Customer].[Category - Customer]

// hierarchy except the [All] member

( [Customer].[Category - Customer].Members(1) : Null ) =

Sum

(

Descendants

(

[Customer].[Category - Customer].CurrentMember, , LEAVES

) *

Descendants

(

[Job].[Reference].CurrentMember, , LEAVES

([Measures].[Cost] * [Measures].[Cost Multiplier])

);

END SCOPE;

This brought the query time down from over 1 minute 15 seconds to about 50 seconds. It does of course return the same data.

UPDATE: LEAF-LEVEL MDX SCRIPT ASSIGNMENT

Some readers may be asking why we didn't execute something like the following MDX script assigment.

SCOPE (Leaves(Customer), Leaves(Job));

[Measures].[Cost] = [Measures].[Cost] * ValidMeasure([Measures].[Cost Multiplier]);

END SCOPE;

This would be taking a similar approach to what is described by Mosha here. The Leaves function returns a set of tuples representing the lowest level of granularity across all attributes in the dimension that is passed in as a parameter. ValidMeasure is used purely as a performance optimisation (see Mosha's post above). As [Measures].[Cost] is a base measure rather than a calculated measure, it should automatically aggregate up the hierarchy (i.e. to customer category level, which is what our query is interested in) right? Wrong! Take a look at Page 388 to 390 of the MDX Solutions book. It states that the outer dimension in a many-to-many relationship (in this case Customer) will not automatically aggregate up the hierarchy. This is similar behaviour to MDX script assigments involving calculated measures. This approach is normally very performant as it doesn't need to use the Descendants function; Analysis Services deals with the aggregation automatically. Unfortunately, we have to rule it out in this case.

OPTION 3 – MEASURE EXPRESSIONS

I created another cube and set up a measure expression for this purpose. It is simply “[Cost]*[Cost Multiplier]”.

Note: when using measure expressions, it is often a good idea to set the DirectSlice property for the dimension relationship (in the Dimension Usage tab). For the case of MDPS, setting the direct slice to Root([Customer]) (as shown by the following screenshot) means that the All member for Job/Customer attributes shows the actual total of costs – not just those which have a corresponding [Cost Multiplier]. If we do not set DirectSlice, Jobs with no revenue would not contribute to the total cost for all Jobs.

The Root(<dimension_expression>) function returns a tuple with the All/Default member of every attribute/hierarchy in the dimension.

To re-iterate, it makes sense to set the DirectSlice as above for the case of MDPS because we want to report the total cost for all jobs (e.g. [Job].[Reference].[All]) irrespective of whether revenue has been received for them.

Using measure expressions brought the query time down to less than 20 seconds.

SUMMARY

Considering that querying Cost by Customer without any MDX or measure expressions (i.e. without the proportional split) takes about 12 seconds (due to sheer volume of data), I’d say measure expressions are indeed pretty performant!