CODEWITHSUNDEEP
excelssasbusiness-intelligenceolap-cube
user2859298
user2859298

Reputation: 1443

SSAS - Data Warehouse structure and the Unknown value

I have a table that shows summed monthly values grouped by different analysis codes

TableId    Month    Value    Analysis1ID    Analysis2ID
1             1      100         1             NULL
2             1       50        NULL            3
3             1       50         2             NULL
4             1       50         3             NULL

I have set the above as a fact table (also have a dimension for the analysis values).
As you can see the table has a new row for each unique ID for the analysis column.

We are then analysing the data in excel, Simply summing the Value column and grouping by Analyis1ID, Month

This give us :

AnalysisID1 1 = 100 
AnalysisID1 2 = 50 
AnalysisID1 3 = 50 
Unknown       = 50 
Total = 250

This all looks ok apart from the Unknown, which is summed total of NULL....

I have tried excluding the NULL Value in the Dimension by setting the UnknownMember to "Hidden".

This does work but it does not exclude the amount from the total. How can i exclude it from the total value?

I am guessing that the table structure is not correct for that data, I'm unsure though how else to structure it?

Any help or guidance would be appreciated

Upvotes: 0

Views: 575

Answers (2)

Ben McLean
Ben McLean

Reputation: 95

I would not have NULL values in dimension members, in the past i've always used an Unallocated Member with a -1 ID.

You could then use Cube Security to filter out the Unknown or Unallocated members.

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15037

I would Filter that row out using Excel. Right-click on the cell labelled 'Unknown' and you can choose Filter / Hide Selected Items.

Upvotes: 0

Related Questions