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