Search This Blog

Tuesday, October 28, 2008

Attribute relationship example

Setting proper attribute relationships for the attributes of a dimension is essential for query performance and usability. As Mosha wrote in his blog, it might be the most important advice for cube designers to set the attribute relationship correctly. The full article can be viewed here and it is very detailed.

I still saw some cases recently where the improper use of attribute relationship settings caused wrong query results (which might even be worse than a low performance), so I decided to post a wrap up of this topic here.

First, you need to know that setting attribute relationship has these benefits:

  • Better query performance
  • More efficient methods for the cube to store the data of the hierarchies
  • Easier way to write MDX queries (as the dependant attribute is correctly positioned and can be referenced e.g. by currentmember), see below for an example
  • Dependant attribute may be used as metadata (especially helpful when the dependant attribute has it property "Enable attribute hierarchy" set to "false")
  • Higher ranked attribute may be used as "Order-By"-attribute.

Let me provide an example for the MDX query. Think of a sample cube with a date dimension containing two attributes for month and monthname and think about the following query:

with
member MonthMember as [Dim Time].[Monthname].currentmember.NAME

select MonthMember on rows,
[Dim Time].[Month].[Month] on columns

from [TestCube]

if there is no relationship between the month and monthname-attribute the monthname-attribute is not changed when selecting the month-attribute and therefor resides its default which is 'All'.

image_thumb1

But if there is a relationship, binding the monthname-attribute to the month-attribute, the monthname-attribute is automatically set to the corresponding member:

image_thumb31

This shows how query syntax is influenced by the proper use of attribute relationship. In the first example we had to try something with the linkmember() MDX-function to achieve the same result. So designing the attribute relationship in a proper way leads to easier to understand queries with a better performance.

The data was loaded from a very simple date dimension table like shown below:

image_thumb18

The attribute relationship can be modeled in BIDS when designing a dimension. After creating the dimension using the wizard, all attributes are associated to the key attribute of the dimension as shown below:

image_thumb3

The attribute 'Month' and 'Monthname' have no relationship but are simply tied to the attribute "Dim Time' which is the key attribute for our simple time dimension. If you build a hierarchy, like the YearMonth-hierarchy in the screenshot above, you'll notice a yellow warning sign informing you that there is no relationship between Month and Year. You'll get further information when placing the mouse coursor on the warning sign:

image_thumb7

Whenever you see this yellow warning sign we're talking of a non-natural hierarchy.

The relationship can easily be set by dragging the 'Year' attribute to the 'Month' attribute. Now the same dimension looks like this:

image_thumb5

Notice that the yellow warning sign in the hierarchy title has disappeared. In this case we're talking of a natural hierarchy.

But be warned: This doesn't mean that your dimension is healthy now. For our simple example we actually made a big mistake. If you check the dimension in the dimension browser, you'll see something like this:

image_thumb9

There are no more months for the years 2006 to 2008 anymore! This also means that no fact data will be displayed for those months and that the aggregation will be wrong.

If you model an attribute relationship like Month -> Year you have to be absolutely sure that every month corresponds to exactly one year. You can verify this using a simple SQL query like the one below:

select count(Year)
from dbo.DimTime
group by Month
having count(Year)>1

This query should return no rows at all but in our case it returns

image_thumb10

So every month in our source table is mapped to four years which breaks our attribute relationship above.

If you're having BIDS Helper installed on your machine you can also do a dimension health check from the dimension context menu.

image_thumb11

In our case the result looks like this:

image_thumb17

You can clearly see the rows that break our relationship.

So in this simple example it would have been a bad advice to simply set the attribute relationship as we did above. You should always check the relationship in your source data before designing such a relationship.

And even if the users say that the relationship is unique, you shouldn't rely on that. For example a product might belong to a unique product group. But what happens if the association changes over time and you want to keep track of the changes using a slowly changing dimension of type 2. This can also easily break up your attribute relationship. And as the MDX results might differ after changing the relationship, this could also have an influence on existing reports. So it's a good advise to carefully design your hierarchies before you start creating reports, because it's not difficult turning a non-natural hierarchy into a natural one. I'll post an example for this in the next post.

Tuesday, October 7, 2008

Returning MDX query results in an Excel matrix formula

In my last post I had an example for a function to return a single value from an MDX query which has certain performance drawbacks when being used in many Excel fields.

Since Excel supports matrix formulas to do a computation on multiple values, this can also be used for MDX query results. Another benefit for this approach is, that dimension members that are added later on, are also displayed in the query result.

Before I post the function code, here's the result. For my test, I used this MDX query on the Adventure Works cube:

SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity],
[Measures].[Internet Gross Profit]
} ON 0,
[Product].[Category].[Category] ON 1
FROM [Adventure Works]

The result, when being called from SQL Server Management Studio, looks like this:

image4

The following screenshot shows how this result looks like in Excel using the matrix MDX formula:

image9

Now, what are the #N/A entries about? The matrix formula measures the size of the resulting cell area and returns a two dimensional array of exactly that size. When entering the formula I chose a larger area on the worksheet in order to be prepared for additional entries on both axes. Of course you could think of returning a larger matrix from the function in order to properly fill out the unused cell. The best approach would be to supply width and height as parameters.

So, this is how the formula is to be entered:

image_thumb14

Be sure to press CTRL+ALT+ENTER when entering the formula in order to turn it into a matrix formula.

The MDXGetMDX function takes five parameters:

Parameter Description Example
Server Hostname of the SSAS server localhost
InitalCatalog Name of the SSAS database Adventure Works DW
Cube Name of the SSAS cube Adventure Works
mdx MDX code of the query select ... on 0,
... on 1
from cubename
WithCaption When true, captions are printed true

Finally, here's the code I used for the MDGetMDX(...) function:

Function MDGetMDX(Server As String, InitialCatalog As String, Cube As String, mdx As String, WithCaption As Boolean) As Variant
On Error GoTo errorhandler:
Dim cset As New ADOMD.Cellset
Dim conn As New ADODB.connection
Dim x As Variant
Dim i As Integer, j As Integer
Dim i0 As Integer, j0 As Integer ' begin of the data area
Dim i1 As Integer, j1 As Integer ' size of the data area
conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""
cset.Open mdx, conn
If cset.Axes.Count > 2 Then
MDGetMDX = "More than 2 axes are not allowed!"
Exit Function
End If
If cset.Axes.Count > 0 Then i1 = cset.Axes(0).Positions.Count Else j1 = 0
If cset.Axes.Count > 1 Then j1 = cset.Axes(1).Positions.Count Else j1 = 0
If WithCaption Then
' column headings are displayed as rows
If cset.Axes.Count > 1 Then i0 = cset.Axes(1).DimensionCount Else i0 = 0
' row headings are displayed as columns
If cset.Axes.Count > 0 Then j0 = cset.Axes(0).DimensionCount Else j0 = 0
Else
i0 = 0
j0 = 0
End If
If cset.Axes.Count = 2 Then
ReDim x(j0 + j1 - 1, i0 + i1 - 1)
ElseIf cset.Axes.Count = 1 Then
ReDim x(j0, i0 + i1 - 1)
Else
ReDim x(1, 1)
End If
For i = 0 To UBound(x, 2)
For j = 0 To UBound(x, 1)
x(j, i) = ""
Next
Next
' Show caption:
If WithCaption Then
For i = 0 To i1 - 1
For j = 0 To cset.Axes(0).Positions(i).Members.Count - 1
x(j, i + i0) = cset.Axes(0).Positions(i).Members(j).Caption
Next
Next
For j = 0 To j1 - 1
For i = 0 To cset.Axes(1).Positions(j).Members.Count - 1
x(j + j0, i) = cset.Axes(1).Positions(j).Members(i).Caption
Next
Next
End If
If cset.Axes.Count = 2 Then
For i = 0 To i1 - 1
For j = 0 To j1 - 1
x(j + j0, i + i0) = nz(cset(i, j).Value, 0)
Next
Next
ElseIf cset.Axes.Count = 1 Then
For i = 0 To i1 - 1
x(j0, i + i0) = nz(cset(i).Value, "")
Next
Else
x(0, 0) = cset(0).Value
End If
MDGetMDX = x
cset.Close
conn.Close
Exit Function
errorhandler:
MDGetMDX = Err.Description
End Function

Function nz(x As Variant, other As Variant) As Variant
If Not IsNull(x) Then
nz = x
Else
nz = other
End If
End Function