Facts with infinite measures

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";;'

fact table design question

SSAS 2005 - I have the following 3 tables:

T1: dimA_id, dimB_id, dimC_id, prod_id, dollar_amt_1

T2: dimA_id, dimB_id, dimC_id, cat_id, dollar_amt_2

T3: cat_id, prod_id.

cat_id and prod_id has a parent to child relationship.

In this case, I have to build two fact tables, right? There is no way to combine T1 and T2 into one fact table because the dollar_amt 1 and 2 are at a different level of prod_id and cat_id. I just wanted to make sure that I am doing the right thing. It seems that there are repeated data (dimA_id, dimB_id, dimC_id) in both tables.

Thanks.

But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.|||Good point. I will give it a try. Would this be a better appoach than the two fact-table one?|||Should be more straightforward, at least - but large parent-child dimensions can cause performance problems. If this dimension is small, it may not be a concern.|||The cat table has more than 1/2 million records and the prod table is 2-3 times bigger than the cat table. Is this considered large?|||

According to the AS 2005 Performance Guide, that is large, but you could try it and check:

Parent-child hierarchies

Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.

If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels.