Friday, December 31, 2010

.CurrentOrdinal (Iteration in MDX)

Today I was trying to understand one of the queries found in MSDN for currentordinal and was keen to share my understanding. It was good to see how iteration is happening here and how the current co-ordinate values can be checked against other values.


WITH SET [PrdTies] AS 
Filter
 (
       Order
       (
           NonEmpty
           (
              {
                      [Product].[Product Categories].[Product].&[471], --4079
                      [Product].[Product Categories].[Product].&[433], --787
                      [Product].[Product Categories].[Product].&[434], --648
                      [Product].[Product Categories].[Product].&[475]  --787
              } 
              ,[Measures].[Reseller Order Quantity]
       )
      ,[Measures].[Reseller Order Quantity]
      ,BDESC
 ) AS OrdPrds,
 --NOT --commented for now.
 (
       OrdPrds.CurrentOrdinal < OrdPrds.Count 
       AND 
       [Measures].[Reseller Order Quantity]= 
       ( [Measures].[Reseller Order Quantity],OrdPrds.Item(OrdPrds.CurrentOrdinal)) 
       --Compares whether currentmember measure value = measure value for any other member
       --The current member value is compared with value of members after the current coordinate
        --As the member value is compared with members after current coordinate, we do not need to consider  
          the last member
       --Now this gets only 433
 )
OR 
(
        OrdPrds.CurrentOrdinal > 1 
       AND [Measures].[Reseller Order Quantity] = 
       ([Measures].[Reseller Order Quantity], OrdPrds.Item(OrdPrds.CurrentOrdinal-2))
      --but the value of 475 is same as 433. It does not come in first part. but this also qualifies
      --so compare with previous member by doing -2 (-1 compares with self)
)
 )
 
SELECT {[Measures].[Reseller Order Quantity]} ON 0, [PrdTies] ON 1
FROM [Adventure Works]
Additionally you might want to look at this

ADOMD fetches Cube Details

Using ADOMD to fetch details of dimension, roles and Allowed Set in permission

System.Text.StringBuilder result = new System.Text.StringBuilder();
using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial Catalog=Adventure Works DW;Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=False;"))
{
    conn.Open();
    foreach (CubeDef cube in conn.Cubes)
    {
         if (cube.Name.StartsWith("$"))
           continue;

         if (cube.Name == "Adventure Works")
        {
             result.AppendLine(cube.Name);
            foreach (Microsoft.AnalysisServices.AdomdClient.Dimension dim in cube.Dimensions)
            {
                   result.Append("\t");
                   result.AppendLine(dim.Name);
            }
            foreach (Microsoft.AnalysisServices.AdomdClient.NamedSet set in cube.NamedSets)
            {
                   result.Append("\t");
                   result.AppendLine(set.Name);
            }
        }
     }
     conn.Close();
}

Server objServer = new Server();
string strConnection = "Data Source=localhost;Initial Catalog=Adventure Works DW;Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=False;";

if (objServer.Connected)
    objServer.Disconnect();

objServer.Connect(strConnection);
Database db = objServer.Databases["Adventure Works DW"];
for (int i=0;i<db.Roles.Count;i++)
{
    Role role = db.Roles[i];
    result.Append("\t");
    result.AppendLine(role.Name);
}

Cube cube1 = db.Cubes.FindByName("Adventure Works");
foreach (CubeDimensionPermission cubeper in cube1.CubePermissions)
{
        foreach (AttributePermission attrperm in cubeper.AttributePermissions)
        {
          result.Append("\t");
          result.AppendLine(attrperm.AllowedSet[0].ToString());
        }
       
}
MessageBox.Show(result.ToString());

Saturday, November 6, 2010

MDX: Average based on any dimension on row axis

This was an interesting learning i did. There was a requirement were user wishes to calculate the average of sales based on any dimension that he wishes to pass. i.e. the calculated member need to be dynamic enough to calculate based on any dimension passed (not multiple).

To achieve this one need to use the Axis function provided by MDX


WITH MEMBER [Average] AS
      [Measures].[Internet Sales Amount]/
      SUM(Axis(1).Item(0).Item(0).Dimension.DefaultMember,[Measures].[Internet Sales Amount])
      ,FORMAT='PERCENT'
SELECT
      {[Measures].[Internet Sales Amount],[Average]} on COLUMNS,
      NONEMPTY
      (
            [Product].[Product Categories].[Category].MEMBERS,
            [Measures].[Internet Sales Amount]
      ) ON ROWS
FROM
      [Adventure Works]

Friday, November 5, 2010

MDX: Ordered versus Purchased Product by a Customer

WITH
 MEMBER Measures.SoldProducts AS
    DISTINCTCOUNT(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ))
    
SELECT
  {
  [Measures].[Internet Order Quantity],Measures.SoldProducts
  } ON 0
 ,filter( [Customer].[Customer].&[15536],Measures.SoldProducts>0) ON 1
FROM [Adventure Works]

MDX Top N with Remainder every Year

This is some good stuff. I tried a bit on this but was lost in mid way. This was coolly solved by HrvojePiasevoli in the MSDN forums. Thought of sharing this and may be this will be useful for few more.

Learnt Extract, Union and many more.
The requirement here is to Display the Top N sales, remainder and total for each year


with
member [Product].[Product].[AllProducts] AS [Product].[Product].[All Products]
,BACK_COLOR ="&H0000D00D"

MEMBER [Product].[Product].Remainder
AS Aggregate(
        [Product].[Product].[Product].MEMBERS -
        EXTRACT(
                  TOPCOUNT(
                        [Date].[Fiscal Year].CURRENTMEMBER *
                        NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                        3,
                        [Measures].[Internet Sales Amount]
                        )
        ,[Product].[Product])
            ), BACK_COLOR ="&H0000FFFF"
           
set TopSetWithReminder as
Generate(
    [Date].[Fiscal Year].[Fiscal Year].MEMBERS,
    UNION(
            TOPCOUNT
            (
                  [Date].[Fiscal Year].CURRENTMEMBER *
                  NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                  3,
                  [Measures].[Internet Sales Amount]
                  ),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].Remainder),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].[AllProducts] )
            )
    )

SELECT
{
      [Measures].[Internet Sales Amount]
      ,[Measures].[Internet Gross Profit]
} ON 0,
TopSetWithReminder ON 1
FROM 
[Adventure Works]
CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR

LinkWithin

Related Posts with Thumbnails