How to count the number of rows in a dimension using mdx (SSAS)

1
votes

I am trying to capture the change in the "Number of records" before processing the cube and after processing the cube. I would then want to setup a table in sql server as following.

enter image description here

I was thinking of running an mdx script to count the number of records in a dimension in ssis before and after processing the cube to get the results. Could you please let me know how to count the number of records in a dimension using mdx. Thank you in advance.

2
sql-serverssasmdx

2 Answers

0
votes

Here is the query to count Year members from Date hierarchy:

with member [Count] as
sum(existing [Date].[Calendar Year].MEMBERS
,count(existing [Date].[Calendar Year].CurrentMember))-1
select [Count] on 0
from [Adventure Works]

And proof image: enter image description here

You can use is to get total number of members or even pre-filtered (by user, query or any tool).

0
votes

I found this useful for finding the number of records in Cube for each Dimension

SELECT DISTINCT
    [CATALOG_NAME] AS [Database],
    [CUBE_NAME] AS [Cube],
    [DIMENSION_CAPTION] AS [Dimension],
    [DIMENSION_CARDINALITY] AS [Records] 
FROM $system.MDSchema_Dimensions