Thomas.I Microsoft BI & Analytics

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

Archive for the ‘End users dilemma with SSAS structures’ Category

Dynamic YearMonth Axis With Current Flags and the Importance of Attribute Relations in BISM MD

Posted by thomasivarssonmalmo on March 26, 2015

In my previous post I had a TSQL script to move the Adventure Works DW database to current dates. The second idea was to create attribute members that point to CurrentDate, CurrentMonth and Current12Months. The reason for this is that named sets in BISM Multidimensional(BISM MD) are not supported in Tabular and Power Pivot. Named Sets are also not part of the DAXMD that integrates Power View for SharePoint with an external BISM MD model. So why not try to create attributes that might succced Named Sets?

Create the Current Members With TSQL

From my previous blog post I have made a little change and added distinct to the view. The reason is that we had a leap year in 2008 that will create two copies of 2013-02-28. One from the real date and one from the leap year date. Here I move the date dimension five years forward.

CREATE View [dbo].[DateDimensionUpdate]
As
(
Select Distinct
DATEADD(YEAR,5,FullDateAlternateKeY) as FulldateAlternateKey5Y,
DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY),GetDate()) as MonthSequence,
DateName(m,DATEADD(YEAR,5,FullDateAlternateKeY)) As MontName,
DatePart(m,DATEADD(YEAR,5,FullDateAlternateKeY)) As MontNbr,
Year(DATEADD(YEAR,5,FullDateAlternateKeY)) as Year5,
Year(DATEADD(YEAR,5,FullDateAlternateKeY))*100 + Month(DATEADD(YEAR,5,FullDateAlternateKeY)) As YearMonth5,
Year(DATEADD(YEAR,5,FullDateAlternateKeY))*100 + Datepart(qq,DATEADD(YEAR,5,FullDateAlternateKeY)) As yearQty5,
Case When DATEADD(YEAR,5,FullDateAlternateKeY)  = Convert(varchar(8),GetDate(),112) Then ‘Y’ Else ‘N’ End As CurrentDate,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY),GetDate()) = 0 Then ‘Y’ Else ‘N’ End as CurrentMonth,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 2 Then ‘Y’ Else ‘N’ End as CurrentThreeMonths,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 5 Then ‘Y’ Else ‘N’ End as CurrentSixMonths,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 11 Then ‘Y’ Else ‘N’ End as Current12Months,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 23 Then ‘Y’ Else ‘N’ End as Current24Months,
Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 11 and 23 Then ‘Y’ Else ‘N’ End as Current12MonthsLY
From [dbo].[DimDate]
)

The second view is required to move one of the fact tables 7 years ahead.

Create View [dbo].[FactInternetSalesUpdate]
AS
(
Select
    CustomerKey,
    ProductKey,
    DATEADD(YEAR,7,OrderDate) As OrderDate,
    DATEADD(YEAR,7,ShipDate) As ShipDate,
    DATEADD(YEAR,7,DueDate) As DueDate,
    OrderQuantity
from [dbo].[FactInternetSales])

I assume that you know how to add these views to a new BISM MD project.

The BISM MD model

Here is the structure of the model that is a simplified version of the Adventure Works cube with only one fact table and with only Order Quantity as the fact table measure.

Simple Cube

Here is the dimension usage tab for the cube.

Dimension Usage

 

The Date Dimension

Here are the attributes and a hierarchy.  I started with this and added the attribute relations for the hierarchy. I assume that you know about how to map each attribute to type properties and value column properties.

The Date Dimension

 

The Mistake

After building the cube with the dimensions and the simple fact table I thought I was close to a working solution where I can use the current flags to do selections of YearMonth members and that MDX calculations will work. I started in Management Studio to create some session based calculations with time intelligence.

WITH Member Measures.ParallelPeriodOrderQty AS
SUM(ParallelPeriod([Order Date].[Year Month5].[Year Month5],12,[Order Date].[Year Month5]),[Measures].[Order Quantity])

Select {[Measures].[Order Quantity],Measures.ParallelPeriodOrderQty} On 0,
[Order Date].[Year Month5].[Year Month5] On 1
From [AdventureWorksDW2012Simple]
Where ([Order Date].[Current12 Months].&[Y])

The problem with this MDX query is that it works fine with all parts except if you include the slicer part in the Where clause. The ParallelPeriodOrderQty will be null with slicer. The correct months will appear on the row axis however. So what did I forget to add in the cube?

Attribute Relations

Remember the date dimension picture above. We had  a hierarchy and the matching attribute relations for the hierarchy. Here is what solved my problem. The solution was to add the current attributes as attribute relations to the Year Month5 attribute as you can see in the picure.

 

AttributeRelations

 

After adding these attribute relations to the date dimension I runned this query with this result:

TheSolution

Problem solved. Attribute relations are very important in BISM MD. Also you have dynamic flags that will update each time you process the cube and you do not have to use named sets. This solution will also work in Power Pivot as you will se in the next blog post.

Advertisements

Posted in End users dilemma with SSAS structures | Tagged: , | Leave a Comment »

BottomCount, Filter and Null in SSAS2005 and ProClarity

Posted by thomasivarssonmalmo on August 14, 2007

One common business analytical problem is to find customers with low sales. In MDX we have the BottomCount function for that but compared to the TopCount function, that return the best customers,  BottomCount can return less clear results.

If you run this MDX Select in management studio, on the Adventure Works cube, you will see the 50 customers with least Internet Sales Amount in 2003 and what they have bought in 2004.  The MDX select is generated in ProClarity Professional 6.3 and I have used the filter functionality in the client.

SELECT { [Date].[Calendar].[Calendar Year].&[2003], [Date].[Calendar].[Calendar Year].&[2004] } ON COLUMNS ,

 

NON EMPTY { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Date].[Calendar].[Calendar Year].&[2003],

[Measures].[Internet Sales Amount] ) ) } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Internet Sales Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL ;

The problem with this result is that I have probably received 50 customers that not were customers in 2003 but started buying in 2004.  Also note that I have put the Non Empty keyword before BottomCount and it will only remove empty rows after BottomCount have returned the bottom 50 customers. Because all these customers have bought something in 2004, no rows will be removed.

Now I will show you an even more strange result with BottomCount:

SELECT { [Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003] } ON COLUMNS ,

 

NON EMPTY { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Date].[Calendar].[Calendar Year].&[2003],

[Measures].[Internet Sales Amount] ) ) } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Internet Sales Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL ;

This MDX query will return only seven records. The only difference with the first select is that I have changed the year 2004 to 2002, on columns, but the BottomCount function is unchanged. I have no explanation for this behavior.

Internet Sales Amount in the AdventureWorks cube comes from the SalesAmount column in the FactInternetSales tables in the AdventureWorksDW database. If you run sql-statements on that column and check for, is null and = 0 , it will show that no rows in that table have null or 0 in that column. Conclusion: the nulls or empties in the cube do not come from the fact table in the data source.

If you open the Adventure Works cube project and check the measure [Internet Sales Amount] you can see that it uses an advanced property called measure expressions to calculate this value with a currency rate. Measure expressions are not the key problem here so I will leave that subject.

So back to the problem of removing the nonexistent customer that appears in the cube but not in the fact table, how do we deal with this problem?

After reading MDX-solutions I knew that SSAS2005 have a little problem with null.  With measures a null value can both be zero and null so you can you can use both values to search for these values and remove them.  In MDX the Filter function will do the job of removing members will cell values according to a criteria.

I searched in the SSAS2005 newsgroup on BottomCount and only found 7 posts, so this problem only concerns me and a few others. In one of these posts there was an answer that worked.

Select {[Measures].[Internet Sales Amount]} On Columns,

{Order(BottomCount(Filter([Customer].[Customer Geography].[Customer].members,

[Measures].[Internet Sales Amount] <> Null),50,

[Measures].[Internet Sales Amount]),[Measures].[Internet Sales Amount],BDESC)} On Rows

From [Adventure Works]

Where ([Date].[Calendar].[Calendar Year].&[2002])

This solution put a filter in the BottomCount function and disregards cells with null values. You can change the null in the filter to zero with the same result. I use the slicer (the Where-clause) to point at the year I am interested in. The slicer is the first part of an MDX-select that is evaluated and it points the BottomCount function to right year without having to enter it in to that function.

I also quickly checked that these values were correct by querying the fact table with the following TSQL-Select:

Select Min(fs.SalesAmount)

From FactInternetSales fs Join dimTime t On

fs.OrderDateKey       = t.TimeKey

Where Year(t.FullDateAlterNateKey) = 2004

In ProClarity Professional 6.3 I have combined the BottomCount function with a second filter that should remove values less than 0, 00  . You can see this in the following MDX Select but it will not work. It is a little bit sad that this business problem did not get more attention and was not implemented correctly.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,

{ EXCEPT( { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Measures].[Internet Sales Amount] ) ) },

{ FILTER( { BOTTOMCOUNT( { DESCENDANTS( [Customer].[Customer Geography].[All Customers],

[Customer].[Customer Geography].[Customer] ) }, 50, ( [Measures].[Internet Sales Amount] ) ) },

( [Measures].[Internet Sales Amount] ) < 0.00000000000000 ) } ) } ON ROWS 

FROM [Adventure Works]

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

 

Edit: My point here is to show the possibilities with MDX to solve a business problem but also point to that some client implementation of this were not good enough. Still ProClarity have been succesful and good enough in most areas but this important part was missed.

Posted in End users dilemma with SSAS structures | 4 Comments »

ProClarity totals and MDX AGGREGATE()

Posted by thomasivarssonmalmo on July 13, 2007

In several AS2000 cubes a customer of mine had totals calculated for columns by ProClarity’s feature, Totals.
This worked great even with calculated members. My customer had reports with several budget and actual sales values on the columns, both measures and calculated member, and the dates of the last month on the rows in a table. All these days were then summed on the last row with column totals in ProClarity Professional.
When we migrated the cubes from AS2000 and AS2005 I did not think that these ProClarity Totals would be a problem but I was wrong. ProClarity also did a change with Totals between earlier releases(before SSAS2005) and started using the AGGREGATE function instead of SUM for the totals.  Using AGGREGATE was very good news because we could build totals even on calculated measures with ratios, provided that you had the SolveOrder set correctly.
 
But in SSAS2005 we got this problem with totals returning null.
To recreate the problem you will have to enter these three calculated members in the calculations tab in the cube editor of the Adventure Works cube project. The calculations are not exactly like the one my client used but the general idea is the same. I have added YTD only to check if PARALLELPERIOD was the problem.
CREATE MEMBER CURRENTCUBE.[MEASURES].[ParallelPeriodSalesAggregate]
AS Aggregate(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar]),
[Measures].[Internet Sales Amount]), FORMAT_STRING = "### ### ### ###",
VISIBLE = 1 ;
 
CREATE MEMBER CURRENTCUBE.[MEASURES].[ParallelPeriodSalesSum]
AS SUM(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar]),
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "### ### ### ###",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[YTDSalesSum]
AS SUM(YTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "### ### ### ###",
VISIBLE = 1;
 
Process the cube (default) and open ProClarity Professional or Management Studio. After this is finished you can copy and paste the following MDX into anyone of these two client tools.
 
WITH MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS
‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } ) )’,
 SOLVE_ORDER = 1000 
 
SELECT { [Measures].[Internet Sales Amount], [Measures].[ParallelPeriodSalesAggregate],
[Measures].[ParallelPeriodSalesSum], [Measures].[YTDSalesSum] } ON COLUMNS ,
{ { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) },
( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON ROWS 
FROM [Adventure Works]
It is the MDX created in ProClarity Professional 6.3 when you use the column grand totals feature but it will also work in Management Studio.
You will see that the ProClarity Total ([Date].[Calendar].[All Periods].[ Grand Total]) will work for the measures, Internet Sales Amount but not for the calculated members that we have entered previously. You can see this in the second picture.
 
When you look at the calculated member created by the ProClarity column totals, it is a mess of advanced functions that do not make a sence. One way to learn more about what is going on is to recreate parts of the code as named sets and calculated members in the cude editors calculations tab.
 
You can create this as a named set:
 
EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } )
 
What you will see is the same months that are part of the members I have choosen in ProClarity(2004 months). I think that most of this MDX is created to secure that only unique months will be included in the calculation for totals.
 
Next, you can create this as a calculated member with [Date].[Calendar].[Calendar Year] as the parent in the calculations tab.
 
AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } ) )
 
Here you will see a member under [Date].[Calendar].[Calendar Year] that you can use together with the 2004 months on the row axis and it will return correct totals for the Internet Sales Amount Measure but nulls for the ParallelPeriod calculations that we started with.
 
This is the way I debug MDX. Break it down into parts and see what happens to these parts.
I have posted this problem to the Analysis Services newsgroup more than year ago and first I did not receive any answers except on less important problems in the MDX. After a while the always helpful Deepak Puri posted an answer that solved parts of the problem.
Like Deepak told me you can change to SUM instead of AGGREGATE in the calculated member that ProClarity creates ( [Grand Total])  and you will get correct totals and not null. You can see this in the first picture.
So be aware of this limitation in ProClarity Professional totals and that AGGREGATE do not work correctly when you have calculated members with time functions like YTD or PARALLELPERIOD. Other calculated members without time functions will work correctly.
 
So why can you create totals with (AGGREGATE) calculated members that do not include MDX time functions? Why does it work with SUM?
 
I hope that this behaviour will change in Katmai!

Posted in End users dilemma with SSAS structures | 5 Comments »

This error 1.#INF is not only related to division by zero

Posted by thomasivarssonmalmo on July 9, 2007

How many times have you not found this error message in a cell when you have created a calculated member. This is not a hard problem to handle but remember that it can appear in both division by zero errors and when you refer to a member that does not exist, like the parent of the All member in a dimension hierarchy.
 
If you search on MDX and 1.#INF you will mostly see references to the division by zero problem.
 
Run this MDX-select in management studio and see that the All Products member at the top shows this error. I simply calculate the child percentage Internet Sales Amount of the parent.
 

With Member Measures.ParentRatio As [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent),Format_String = ‘###.#%’ 

Select {[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns,NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003];

 

I work with more BI products than SSAS so  my head is not always filled with MDX. Since this problem will appear from time to time, frequently, I thought I could share the solution for all you that have the same knowledge of MDX as me.
 
The error(1.#INF) appears because I am calling the parent of the All Product that does not exist. In the solution, taken from the MDX book, MDX Solutions, I check if the parent is null or not.
 
With Member Measures.ParentRatio As IIF([Product].[Product Categories].CurrentMember.Parent IS NULL, Null, [Measures].[Internet Sales Amount]/([Measures].[Internet Sales Amount],[Product].[Product Categories].CurrentMember.Parent)), Format_String = ‘###.#%’

Select

{[Measures].[Internet Sales Amount],Measures.ParentRatio} On Columns, NON EMPTY Descendants([Product].[Product Categories],[Product].[Product Categories].[Subcategory],Self_And_Before) On Rows From [Adventure Works]Where [Date].[Calendar].[Calendar Year].&[2003];

The 1.#INF will be replaced by null.  Can katmai show nulls automatically for this error and the division by zero error, so that we can avoid putting a lot of code to solve these issues? Keeping run time checks to a minimum should be good for performance.

 

Posted in End users dilemma with SSAS structures | 4 Comments »

AS2005, granularity and end users

Posted by thomasivarssonmalmo on July 1, 2007

This problem first appeared to me in a project where we have actual sales values on the month level and budget values on a higher level, quarter. In this project we have one measure group for actuals and one for budget in the same cube and we share most dimensions between the measure groups.
 
Below the quarter level we could see repeting values appear on the month members even if the measure group only have budget values for quarters as the leaf level.
 
It was nice to see that the Adventure Works cube have the same relation between sales tagets and the date.calendar dimension. You can check this relation and the granularity attribute in the dimension usage tab in the cube editor.
 
You can run this MDX select statement in management studio or ProClarity Professional 6.3, on the Adventure Works cube and see the problem:
 

SELECT { [Employee].[Employee Department].DEFAULTMEMBER } ON COLUMNS , { [Date].[Calendar].[Calendar Year].&[2001], [Date].[Calendar].[Calendar Semester].&[2001]&[2], [Date].[Calendar].[Calendar Quarter].&[2001]&[3], [Date].[Calendar].[Calendar Quarter].&[2001]&[4], [Date].[Calendar].[Month].&[2001]&[10], [Date].[Calendar].[Month].&[2001]&[11], [Date].[Calendar].[Month].&[2001]&[12] } ON ROWS FROM [Sales Targets] WHERE ( [Measures].[Sales Amount Quota] )

I had an idea that the property IgnoreUnrelatedDimensions, for the measure group in the cube editor, would help with this problem but it did not. I would need a property like IgnoreUnrelatedDimensionLevels but it does not exist in SSAS2005.

What finally helped was adding a MDX script in the calculations tab in the cube editor. Here the cube will set the levels on Calendar.Month and below to null instead of showing values that end users do not understand.

Scope ( Descendants([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month],SELF_AND_AFTER), [Measures].[Sales Amount Quota] ) ; This = Null ; End Scope ;

 

 

Posted in End users dilemma with SSAS structures | Leave a Comment »

AS2005 and Totals

Posted by thomasivarssonmalmo on June 29, 2007

I continue my discussion of the relation between the AS2005 clients and the rules of MDX. There is an interesting limitation in AS2005 regarding totals. It is mentioned in a whitepaper by the ProClarity team: "Using ProClarity with SQL Server 2005" released at the same time as SQL Server 2005.
 
You can copy and run this MDX i management studio:

WITH MEMBER [Measures].[ Grand Total] AS ‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] }, [Measures] ) } },

{ [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] } ) )’, SOLVE_ORDER = 1000

 
MEMBER [Product].[Product Model Categories].[All Products].[ Grand Total] AS ‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Product].[Product Model Categories].[All Products].CHILDREN }, [Product].[Product Model Categories] ) } }, { [Product].[Product Model Categories].[All Products].CHILDREN } ) )’, SOLVE_ORDER = 1000
 
SELECT { { [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] }, ( [Measures].[ Grand Total] ) } ON COLUMNS ,{ { [Product].[Product Model Categories].[All Products].CHILDREN }, ( [Product].[Product Model Categories].[All Products].[ Grand Total] ) } ON ROWS FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
 
It is a simple table with product model categories on the rows and two measures on columns.
 
This MDX is generated from ProClarity Professional 6.3 and I use a feature, Totals(that you see in the two calculated members), that does not work for rows. At the same time it make perfect sense to add Internet Sales Amount and Internet Tax Amount. But the row totals will be empty in ProClarity Professional and in management studio you will se #ERROR in the cells for row totals. In management studio you will also see the error message "Aggregate functions cannot be used on Calculated Members in the Measures dimension" 
 
If you build the same table in Excel2007 you will see that the row totals are gone. They are usually switched on by default and the pivot tables also have the default of not showing empty rows or columns.

The conclusion in the whitepaper is that this is by design and the workaround is to build a calculated member that sums the Internet Sales Amount with the Internet Tax Amount.

 

 

Posted in End users dilemma with SSAS structures | Leave a Comment »

Testing the MDX filter function

Posted by thomasivarssonmalmo on June 24, 2007

This is another short MDX blog entry in order to improve my MDX skills and to present end users problems of interaction with SSAS cubes.
 
The Filter function have sometimes returned resultsets that can be tricky to explain to a customer. It is used in several clients like ProClarity and Excel 2007. I do not have all answeres yet so feel free to add a comment.
 
Filter in MDX removes members from an axis that do not fullfill the terms i the function. The Where clause in MDX, also called a slicer, do not remove members only cell values in a resultset. Be aware that the Filter function condition is always about complete rows or columns, never about single cells.
 
In the first example I do not have an explicit time member in the filter clause so I am probably pointing to the all member of the TimeCalendar dimension. With this in mind I can remove members(product subcategory) on the row axis :
 
Select CrossJoin({[Date].[Calendar].[Calendar Year].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({[Product].[Product Categories].[Subcategory].Members},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works];
 
The second example breaks the rules of MDX and do not work. It says that the calandar hierarchy already exists on the Axis0 axis. I have no problem with that.
Select CrossJoin({[Date].[Calendar].[Calendar Year].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({([Product].[Product Categories].[Subcategory].Members,
[Date].[Calendar].[Calendar Year].&[2003])},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works];
 
In the third example , if I change the crossjoin to CustomerCountry and the measures, the filter clause will still work as expected, and add that year, in the filter, to the row axis, even if I have not explicitly asked for it. I was actually wrong here before but now I see that this example works as the first. It is the quantity for all countries, for a subcategory, that decide if the record will disappear or not.
Select CrossJoin({[Customer].[Customer Geography].[Country].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({([Product].[Product Categories].[Subcategory].Members,
[Date].[Calendar].[Calendar Year].&[2003])},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works]
 
The interesting thing with the last example is that I cannot filter like this in ProClarity Professional 6.3 , on the [Internet Order Quantity] for the total product subcategory group. I must filter on one of the column members as well, like USA. Still all the the other countries will be left in the grid and that is not strange because they are part of row that includes USA (Tires and Tubes if you set the limit to over 2000).
 
So the filter behaviour i Management Studio is not the same as in ProClarity Professional 6.3 . My opinion is that ProClarity’s way of implementing filters is more limited.
 
 
This is the first part of my test of the Filter function and I have not tried enough examples yet to be really sure of how it works in different scenarios. I will update this blog entry when I find more interesting examples. 

Posted in End users dilemma with SSAS structures | 2 Comments »

Assymetric Selects in MDX

Posted by thomasivarssonmalmo on June 24, 2007

After a few months away from AS2005 cubes and MDX, because I have been working mainly with Reporting Services projects, I thought of improving my MDX skills. Also my copy of "MDX Solutions"is falling apart so why not start with translating the code samples in that book to useful scripts with the Adventure Works cube. On page 18 in that book I found a MDX select that I changed to:
 
Select {([Date].[Calendar].[Calendar Year].&[2002],[Measures].[Internet Sales Amount]),
([Date].[Calendar].[Month].&[2002]&[7],[Measures].[Internet Order Quantity]),
([Date].[Calendar].[Calendar Quarter].&[2003]&[3],[Measures].[Internet Tax Amount]),
([Date].[Calendar].[Month].&[2002]&[1],[Measures].[Internet Freight Cost])} On Columns,
[Product].[Product Model Categories].[Category] On Rows
From [Adventure Works]

 
The interesting thing with this script is that you get what the book calls an assymetric result. Each measure will not be repeated for every time member. When you click on members in a client tool like ProClarity Professional 6.3 you will never get this result because ProClarity will always do crossjoins on dimension members that you put on rows or columns. It is the same with Excel 2007.
 
Assymetric results is new to me. I have created calculated members to produce this results earlier and I had no idea that I could write queries to achive this. Perhaps I have been to lazy and never thought of that most SSAS2005 clients do crossjoins of everything you put on an axis and ignores the other possiblity.
 
When I pasted the Select into ProClarity Professional it created the same assymetrical result as in management studio.
 
Should not these clients be able to switch on or off crossjoin because most reports are assymetrical?
 

Posted in End users dilemma with SSAS structures | 2 Comments »