Hello!
I was wondering if there exists convenient ways to handel infinite measures in SSAS.
In my exmple there are facts describing software licenses. Normally, licenses grant the right for a specific amount of installations. However, as for something like a site or enterprise license, the allowed number of installations is unlimited.
Of course this can workarounded by a separate measure counting those unlimited licenses but it adds more complexity and inconveniance to analysis.
Thanks in advance for ideas,
Regards, Alex
Sorry - but I couldn't understand from your description what exactly you need, and what would "infinite measures" solve.|||Hello Mosha,
I will do my very best to make it more clear.
Here is an example source table "Licenses" from the data warehouse:
DimOwner | DimSoftware | [Allowed Installations] | [Allowed Unlimited]
Department1 | MySoftware | 10 | false
Department2 | MySoftware | null | true
What ever might happen in the cube, as a result a user wants to analyse the available licenses in PivotTables that look like the following.
Analyzing on company level:
DimSoftware | Allowed Installations
MySoftware | <infinite> (some symbol)
With added DimOwner dimension:
DimOwner | DimSoftware | [Allowed Installations]
Department1 | MySoftware | 10
Department2 | MySoftware | <infinite> (some symbol)
Possible solution I currently can imagin:
DimOwner | DimSoftware | [Allowed Installations] | [Unlimited license]
Department1 | MySoftware | 10 | 0
Department2 | MySoftware | empty | 1
But doing so would double quite a lot of measures I currently have and therefore add complexity to analysis that I would like to hide from users.
Best regards, Alex
|||OK, I see. I think the best solution is to have that additional measure indicator of unlimited licences. You can define it as boolean or integer and choose aggregation function MAX. Since this measure will ever get only one of two values, the AS will automatically compress it down to 1 bit per record. So overhead on partition size should be minimal. You then will hide both Allowed Installations and Unlimited license measures from the user, and create the following calculated measure:
CREATE Installations = IIF ( Measures.[Unlimited license], -1, Measures.[Allowed Installations] );
Format_String(Measures.Installations) = ';I\nf\i\nite;;'
Basically, I mark infinite number of installations with -1, and then use formatting to display it as "Infinite" to the end user.
HTH,
Mosha.
|||Thanks a lot! Nice trick with the MAX and Format_string :)
Best regards,
Alex
|||Tiny addition: Excel 2k7 prefers the following notation of Format_String for some reason:
Format_String(Measures.Installations) = ';"Infinite";;'
