Gopika's Blog: 11/1/13

Exclude Unknown member from the TopCount in MDX...

While taking the Top or Bottom count using a MDX query or using a Named Set, we may need to exclude Unknown member. We can easily do it by using the FILTER function as below.

TopCount
    (
    FILTER([Item].[Item].[Item].Members,[Item].[Item].CurrentMember.MEMBER_CAPTION<>'Unknown' ),
    100,
    [Measures].[Unit Price]
    )

Same can be applied for the bottom count as well. Also we can filter any other member using the same approach.

Could not load type 'Microsoft.AnalysisServices.SPAddin.ASLinkFilePage' in SharePoint 2013

While trying to create a BI Semantic Model Connection, I got an error as Could not load type 'Microsoft.AnalysisServices.SPAddin.ASLinkFilePage'.
The reason is that the PowerPivot Web Application has not been deployed to the SharePoint Web Application.
We can fix this error by following below steps.

Go to the SharePoint 2013 Central Administration site.
Go to the System Settings section.
Click on Manage Farm Solutions under the Farm Management section.
Click on powerpivotwebapp.wsp
Click Deploy Solution at the top of the screen
In the Deploy To section select your web application from the drop down list and click Ok.

SSAS Cube is not shown in Excel and other client applications...

We faced a situation that the deployed and processed SSAS cube is not shown in Excel and other client applications, but it can be browsed through SQL Server Management Studio.
Actually the reason was somehow the Cube Visible property is set to False. Cube will be visible for Excel and other client applications only if this property is set to True.
But if the cube has large volume of data, it will take more time to deploy and process the cube after changing the visible property. Therefore it will not be practical and will waste time and resources.

Therefore in such scenario we can use XMLA Query to alter the cube visibility property value.
We can easily get the XMLA Query by right clicking the cube and selecting Script Cube as, then ALTER To as shown in the below image.

Then in the query window go the end of the Dimension section and you can see that the Visible property is set to false as shown in the below figure.

Change it to true and run the query. Then browse through the Excel or any other application uses to browse the cube and you will be able to browse it.

Numbering data into specific number of groups using NTILE function...

Sometimes we may need to cluster data into particular group according to the particular value.

As an example as per the SQL Rank... secenario, we may need to distribute rows to 10 groups depending on the AvgRate value.

For that we can use NTILE function as we used the Rank function. The difference in the NTILE function is that we need to provide the number of groups we want.

Sample query is as below

   1: SELECT YearKey, SupplierKey, ItemKey, NTILE(5) OVER (PARTITION BY ItemKey, YearKey ORDER BY AvgRate ASC) AS GroupByAverageRate FROM FactPurchases GROUP BY YearKey, SupplierKey, ItemKey

Distinct Count in SSAS

In some scenarios we may need to get the Distinct Count value. As an example we may need to get the items purchased from a particular supplier. In such scenario we need to get the distinct items from the purchase orders, since the same item will be available in different purchase orders.
For that below expression can be used.

   1: DistinctCount(Nonempty([Item].[Item].[Item].Members, [Measures].[Fact Purchase Count]))

FactPurchase is our Fact table which stores Purchase Order data and Item is the dimension which keeps Item information. By default when we create a Measure Group in the Cube, it automatically adds the Count measure. In the above expression what it does is, it joins the Item Dimension with that Count measure and get the distinct Items from it.

SQL Rank...

There was a situation that Suppliers needs to be ranked by the lowest Rate given for the Items in a particular Year. It can be easily done by using SQL Rank function.
Sample query is as below.

SELECT YearKey,SupplierKey,ItemKey,
RANK() OVER (PARTITION BY ItemKey,YearKey ORDER BY AvgRate ASC) AS RankByAverageRate
  FROM FactPurchases
GROUP BY YearKey,SupplierKey,ItemKey

This will rank the Suppliers using the AvgRate of the Item available for the Year.

Get the Top N records for a result set in MDX

In SQL Query we can easily get the Top N records for a result set. But we cannot use the same syntax in MDX.

Below query will show the Top 10 records of the result set.

SELECT {[Measures].[Unit Price] } ON COLUMNS,NON EMPTY
Head(NONEMPTY(CrossJoin([Item].[Item].[All] ,[Company].[Company].[All].Children) ),10) ON ROWS
FROM [Cube]

TopCount and BottomCount in SSAS

In some scenarios we may want to get the lowest or highest dimension attributes using SSAS Cube. As an example we may need to get the 10 items with lowest unit price and the highest unit price.

In such kind of a scenario we can use the TopCount and BottomCount functions.

To get the 10 items with highest unit price, below expression can be used.

TopCount
    (
    [Item].[Item].[Item].Members,
    10,
    [Measures].[Unit Price]
    )

Likewise I used the BottomCount in the same way to get the 10 items with lowest unit price.

BottomCount
    (
    [Item].[Item].[Item].Members,
    10,
    [Measures].[Unit Price]
    )

But it I filter data for the Month, it only shows the values for the last month. For others no rows were displayed.
Actually this is due to the NULL values and to avoid it, NONEMPTY function was used.

BottomCount
   (NONEMPTY([Item].[Item].[Item].Members,{[Measures].[Unit Price]})
   , 10
   , [Measures].[Unit Price]
   )

This shows the 10 items with lowest unit price for the selected filtering criteria.