MDx

Showing Measure values as either ‘0’ or ‘NULL’ in cube ?

Hello all,

It been long time  ,after a long time gap I would love to post this blog which might be helpful to the one who works in SSAS.

While designing a cube everyone simply doesn’t care about the ‘NULL’ or  ‘0’ values within the cube output. So through this blog , I want to share with you guys that how can we show the NULL  or 0 value output within the cube.

Also , if anyone need to show the exact client data either it’s NULL or as 0 within a Cube then for them this blog might be helpful too.

One can handle/Preserve the NULL values of data by following the blog by DevinKnight which is one of nice blog I referred

http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values

Since , BIDS will treat all the NULL values as ‘0’ while calculation and if you have hundreds of calculated measure and you want to show the values as either NULL or 0 as per you need.

And there you need to handle the 4cases mostly NULL/NULL,NULL/0 , 0/0, 0/NULL.

If divisor is zero, then there will be no error, the cell value simply will be positive or negative infinity represented as 1.#INF – but people don’t like this for some reason and prefer to replace it with NULL, which makes the user wonder why there is no data for that calculated Measure.

Let’s say, we have a calculated measure as [Measure].[Cal] and you need to show like this.

[Measure].[Cal] =[Measure].[A]/ [Measure].[B]

case 1 when [Measure].[A]=0 and [Measure].[B]=0, then show 0,

case 2 when [Measure].[A]= NULL and [Measure].[B]=NULL then show NULL,

Case 3 when [Measure].[A]= 0 and  [Measure].[B]=NULL then show 0,viceversa

Case4 when  [Measure].[A]= value1 and  [Measure].[B]=value2 then show value1/value2,

The best approach to calculating [Measures].[Cal] is the following:

 IIF([Measures].[B] = 0, null, [Measures].[A] / [Measures].[B] )

If you return null, NON EMPTY will work and the calculation will be faster as there are optimizations in the engine to handle this specific pattern.

Least  one used:

 IIF([Measures].[B] = 0, 0, [Measures].[A] / [Measures].[B] )

You don’t want to return a 0 in the second argument of the IIF as this will always return a value and will mean that you cannot use NON EMPTY in your queries (as the calculation would never return an empty value).

The above all 4 case can be handle within one nested IIF condition as you only want to display NULL if both A and B are NULL, otherwise it is always 0 or the number.:

IIF(ISEMPTY(A) AND ISEMPTY(B), NULL, IIF(A=0 OR B = 0, 0, A/B)) i.e

IIF(     

  ISEMPTY([MEASURES].[A]) AND ISEMPTY([MEASURES].[B]), NULL,

		IIF([MEASURES].[A] = 0 OR [MEASURES].[B]=0 , 0, [MEASURES].[A]/[MEASURES].[B])

   )
Some more details to handle like this are further describe within Mosha Blog.
http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx

Hope this post will be helpful for all of us in the field of BI mainly in SSAS/MDX and one who is looking for such solution.

Regards,

Anil Maharjan

Generate possible combination of MDx automatically by just pointing the SSAS cube Catalog.

Hello all,
After such a long time , I would like to post some another blog which might be helpful to the one who works in BI mainly in SSAS/MDx.
I simply need to generate some possible combination of MDx automatically by just pointing the SSAS cube Catalog.
In order to got my solution I had research a lot and also post the request in MSDN as well as BIDN forum and got some help through my MSDN post by Hari .
For getting the solution ,we simply need to create the SSIS package as below.
Following are the steps below.
1. Build new SSIS package and in data flow task.
2. Add script component with Source setting as the first task.
3. Paste the code shown in the code shown below. This code takes as input the SSAS server and Database name. This is iterate through the dimensions and base measures. For each base measure it identifies all the corresponding dimension. For each dimension it will identify the attribute which is mapped to the measure group and attribute directly relating to it. Output of this task is dimension, cube, attribute, basemeasure.
Note: Add the reference ‘ Microsoft.AnalysisServices.dll ’ in your code which can be found in the server where you have SSAS installed .i.e. C:Program FilesMicrosoft SQL Server90SDKAssemblies
String strCube, strDimension, strAttribute, strMeasure, strDatabase;
Server s;
CubeCollection cc;
MeasureGroupCollection mgc;
MeasureGroupDimensionCollection mgdc;
MeasureGroupAttributeCollection mgac;
RegularMeasureGroupDimension rmgd;
ReferenceMeasureGroupDimension rfmgd;
MeasureCollection mc;
strDatabase = “Adventure Works DW 2008”;//add catalog here
s = new Server();
s.Connect(“ANILMAHARJAN”);//add server here
cc = s.Databases[strDatabase].Cubes;
foreach (Cube c in cc)
{
strCube = c.Name;
mgc = c.MeasureGroups;
foreach (MeasureGroup mg in mgc)
{
mgdc = mg.Dimensions;
mc = mg.Measures;
foreach (MeasureGroupDimension mgd in mgdc)
{
strDimension = mgd.CubeDimension.Name;
if (mgd.GetType().Name == “RegularMeasureGroupDimension”)
{
rmgd = (RegularMeasureGroupDimension)mgd;
mgac = rmgd.Attributes;
}
else if (mgd.GetType().Name == “ReferenceMeasureGroupDimension”)
{
rfmgd = (ReferenceMeasureGroupDimension)mgd;
mgac = rfmgd.Attributes;
}
else
break;

foreach (MeasureGroupAttribute mga in mgac)
{
if (mga.Type.ToString() == “Granularity”)
{
strAttribute = mga.Attribute.Name;
foreach (Measure m in mc)
{
strMeasure = m.Name;
AttributeRelationshipCollection arc;
arc = mga.Attribute.AttributeRelationships;
int iFlag = 1;
GetRelatedAttribute(iFlag, strDatabase, strCube, strMeasure, strDimension, mga.Attribute);
}
break;
}
}
}
}
}
}
public void GetRelatedAttribute(int iFlag, String strDatabase, String strCube, String strMeasure, String strDimension, DimensionAttribute att)
{
String strAttribute;
AttributeRelationshipCollection arc;
arc = att.AttributeRelationships;
strAttribute = att.Name;
CubeMetadataBuffer.AddRow();
CubeMetadataBuffer.Database = strDatabase;
CubeMetadataBuffer.Cube = strCube;
CubeMetadataBuffer.Dimension = strDimension;
CubeMetadataBuffer.Attribute = strAttribute;
CubeMetadataBuffer.Measure = strMeasure;
iFlag = 0;

if (arc == null)
{
return;
}
else
{
foreach (AttributeRelationship ar in arc)
{
GetRelatedAttribute(iFlag, strDatabase, strCube, strMeasure, strDimension, ar.Attribute);

}
}

}
}
4.Add second script component with Transformation setting with coding as
String strCube, strMeasure, strDimension, strAttribute, strMDXQuery;
strCube = “[“+Row.Cube+”]”;
strDimension = “[“+Row.Dimension+”]”;
strAttribute = “[“+Row.Attribute+”]”;
strMeasure = “[Measures].”+”[“+Row.Measure+”]”;
strMDXQuery = “select nonempty(” + strDimension + “.” + strAttribute + “.” + strAttribute + “.members,” + strMeasure + “) on rows, {” + strMeasure + “} on columns from ” + strCube + “;” + “rn”; Row.MDXQuery = strMDXQuery;

5.Store those outputs within the table or any file for further propose and also we can add user calculated sets and Members within the MDXQuery. which can be very helpful for other purpose i.e. to capture performance statistics.
Snapshot of SSIS Package

Some output sample MDx Queries
select nonempty([Promotion].[Promotion].[Promotion].members,[Measures].[Internet Sales Amount]) on rows, {[Measures].[Internet Sales Amount]} on columns from [Adventure Works];
select nonempty([Promotion].[Min Quantity].[Min Quantity].members,[Measures].[Internet Sales Amount]) on rows, {[Measures].[Internet Sales Amount]} on columns from [Adventure Works];
select nonempty([Promotion].[End Date].[End Date].members,[Measures].[Internet Sales Amount]) on rows, {[Measures].[Internet Sales Amount]} on columns from [Adventure Works];
select nonempty([Promotion].[Start Date].[Start Date].members,[Measures].[Internet Sales Amount]) on rows, {[Measures].[Internet Sales Amount]} on columns from [Adventure Works];
Hope this post will be useful for all of us in the field of BI.
Regards,
Anil Maharjan