Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Measures and calculations in cubes without natural hierarchies

Posted by thomasivarssonmalmo on March 21, 2008

In this part of my attribute relations series I will create a simple cube with only attribute relations to the dimension key and no user or natural hierarchies. After the cube is created I will look how cube measures and calculated measures will be affected.

One obvious reason for creating attribute relations in natural hierarchies is that they are the foundation for creating aggregations and better performance.  There is also the point of not having wrong values in your cube fact measures but this is mainly caused by having wrong attribute relations in natural hierarchies and this will decide the distribution of measures values and not the totals.

I will create two simple dimension tables (Product and Time) and a fact table with a few records. After that we will create a cube without natural hierarchies and attribute relations in these hierarchies. We will only rely on the attribute relations between the dimension key and the attributes.

Run this script on a suitable database in management studio.

CREATE TABLE [dbo].[DimTime](

            [MyDate] [datetime] NULL,

            [MonthNo] [int] NULL,

            [QuarterNo] [int] NULL,

            [YearNo] [int] NULL)

 

GO

 

Insert Into DimTime(MYDate) Values(‘2007-11-20’)

Insert Into DimTime(MYDate) Values(‘2007-11-21’)

Insert Into DimTime(MYDate) Values(‘2007-11-22’)

Insert Into DimTime(MYDate) Values(‘2007-11-23’)

Insert Into DimTime(MYDate) Values(‘2007-11-24’)

Insert Into DimTime(MYDate) Values(‘2007-12-01’)

Insert Into DimTime(MYDate) Values(‘2006-12-01’)

 

GO

 

Update DimTime

Set MonthNo = Month(MyDate),

            QuarterNo = DatePart(qq,MyDate),

            YearNo = Year(MyDate)

 

GO

 

 

CREATE TABLE [dbo].[DimProduct](

            [ProductName] [varchar](15) NULL,

            [ProductGroup] [varchar](15) NULL,

            [ProductSegment] [varchar](15) NULL)

 

GO

 

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Bread’,‘Bakery’,‘Stock food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Milk’,‘Dairy’,‘Fresh food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Apples’,‘Fruit’,‘Fresh food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Orange’,‘Fruit’,‘Fresh food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Spagetti’,‘Other’,‘Stock food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Cake’,‘Bakery’,‘Stock food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Meatballs’,‘Other’,‘Stock food’)

Insert Into DimProduct (ProductName, ProductGroup,ProductSegment) Values(‘Cream’,‘Dairy’,‘Fresh food’)

 

CREATE TABLE [dbo].[MyFact](

            [MyDate] [datetime] NULL,

            [MySales] [decimal](9, 2) NULL,

            [ProductName] [varchar](15) NULL)

 

GO

 

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-11-20’,100,‘Bread’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-11-21’,200,‘Milk’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-11-23’,300,‘Apples’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-11-24’,100,‘Cake’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-12-01’,100,‘Orange’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2006-12-01’,200,‘Spagetti’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-12-01’,100,‘Meatballs’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-11-22’,100,‘Cream’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2007-12-01’,200,‘Spagetti’)

Insert Into MyFact (MyDate, MySales, ProductName)

Values (‘2006-12-01’,200,‘Orange’)

Run this SQL to check the values against what will show up in the cube

Select p.ProductSegment,p.ProductGroup,p.ProductName, SUM(Case when t.YearNo = 2006 then f.MySales Else Null End) as Sales2006,
SUM(Case when t.YearNo = 2007 then f.MySales Else Null End) as Sales2007
From DimProduct p Join MyFact f on p.ProductName = f.ProductName Join
DimTime t on f.Mydate = t.Mydate
Group by p.ProductSegment, p.ProductGroup, p.ProductName
Order By p.ProductSegment, p.ProductGroup

SQL Select

Start Business Intelligence Developer Studio and start a new SSAS cube project. Set up a data connection to the data source where you tables reside. 

 The data source view looks like this:

  Datasource View

Make sure that you set the logical keys and the relations like above.

Build a time dimension like this one. (I have changed the name of the date attribute and the keys for each attribute below year is a collection of the year and quarter/month. Date is unique so you do not have to do anything with the key for each attribute)

Time dimension

We will have no natural hierarchy in the middle, only the attribute hierarchies in the left pane. Check that the type property for each attribute is set correct in the property pane.

 Build a product dimension like this.

Product dimension

Like the time dimension we will only have attribute hierarchies and no natural hierarchies.

After this is finished you build a cube like this.

The cube

Check the relation between the dimension tables and the fact table in the dimension usage tab.

  Dimension usage

Process and deploy the cube. When the cube is processed you can check the result by running this ProClarity generated MDX in management studio.

WITH MEMBER [Product].[ProductSegment].[All].[ Grand Total] AS ‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { { { [Product].[ProductSegment].[All].CHILDREN } * { [Product].[ProductGroup].[All].CHILDREN } * { [Product].[Product].[All].CHILDREN } } }, [Product].[ProductSegment] ) } * { EXTRACT( { { { [Product].[ProductSegment].[All].CHILDREN } * { [Product].[ProductGroup].[All].CHILDREN } * { [Product].[Product].[All].CHILDREN } } }, [Product].[ProductGroup] ) } * { EXTRACT( { { { [Product].[ProductSegment].[All].CHILDREN } * { [Product].[ProductGroup].[All].CHILDREN } * { [Product].[Product].[All].CHILDREN } } }, [Product].[Product] ) } }, { { { [Product].[ProductSegment].[All].CHILDREN } * { [Product].[ProductGroup].[All].CHILDREN } * { [Product].[Product].[All].CHILDREN } } } ) )’, SOLVE_ORDER = 1000  MEMBER [Product].[ProductGroup].[All].[ Grand Total] AS ‘AGGREGATE( { [Product].[ProductGroup].DEFAULTMEMBER } )’ , SOLVE_ORDER = 1000  MEMBER [Product].[Product].[All].[ Grand Total] AS ‘AGGREGATE( { [Product].[Product].DEFAULTMEMBER } )’ , SOLVE_ORDER = 1000  SELECT { [Time].[Year].[All].CHILDREN } ON COLUMNS ,

 

{ { { { [Product].[ProductSegment].[All].CHILDREN } * { [Product].[ProductGroup].[All].CHILDREN } * { [Product].[Product].[All].CHILDREN } } }, ( [Product].[ProductSegment].[All].[ Grand Total], [Product].[ProductGroup].[All].[ Grand Total], [Product].[Product].[All].[ Grand Total] ) } ON ROWS 

 

FROM [AttributeRelations]

 

WHERE ( [Measures].[My Sales] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Most of the MDX is for the Column Grand Total that ProClarity creates.

Cube select

The result will look like above. You can compare this with the TSQL query that we used previously and you will see that they match.  With cube or fact table measures we can establish that SSAS2005 will return correct results even without attribute relations in the “natural” hierarchy that I have placed on the row axis in ProClarity. Remember that ProductSegment, ProductGroup and Product is not a natural hierarchy but 3 separate attributes in the product dimension.  What you see is autoexists between these attributes and they will show up in a tool in the same way they are related in the dimension table.

Finally it is time to create some calculated members and see how their values will be returned.  Start management studio and connect to this cube.

Run this MDX in management studio. Note that I have added the function AddCalculatedMembers to show my calculated measure on rows together with each year.

WITH Member Measures.X As ([Measures].[My Sales])

Select {{[Time].[Year].Children}*AddCalculatedMembers({[Measures].Members})} On Columns,

{{[Product].[ProductSegment].Children}*{[Product].[ProductGroup].Children}*{[Product].[Product].Children}} On Rows

From [AttributeRelations];

If you look at the result you will see that the fact measure and the calculated measure will be the same and correct.

Qube query

From by previous post about attribute relations we can try the example with the CurrentMember-function in a calculated measure.

WITH Member Measures.X As ([Measures].[My Sales], [Product].[ProductGroup].CurrentMember)

Select {Measures.X, [Measures].[My Sales]} On Columns

From [AttributeRelations]

Where ([Product].[Product].&[Spagetti]);

The result will be 500 for measure X and 400 for the fact measure. It seems like measure X points to the value of its product group Other,  that is 500 for all years.

Conclusion: The lack of attribute relations between attribute members except for the dimension key can cause problems for calculated measures that use the CurrentMember function. Without this function the calculated measures returns correct results. 

Advertisements

One Response to “Measures and calculations in cubes without natural hierarchies”

  1. Vidas said

    Link to this article was posted in the SSAS Design category.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: