How to model a Fact that is Unique for every level in Dimension (FM + Cube)

[karthik.kadambi]

Hi All,

1. I have a scenario for a single Fact that appears to be unique to a particular level. I have 3 Dimensions -> Product (6 levels), Time (3 levels) and Location (3 levels). This fact shouldn't rollup to higher levels of Dimension and hence I had to create multiple fact query subjects with every permutation/combination I can think of.

For Ex: Locationlevel1Productlevel1Timelevel1, Locationlevel1Productlevel2Timelevel1, so on and so forth. (In my model I would have 54 combinations)
Each of these fact query subjects have a single fact that needs to be renamed to something that is unique for that combination.

Now I've been successful creating a cube at the Month level using the Time Dimension wizard. This method gives errors when I try to incorporate Quarter and Year fields since they aren't Date fields, and my scope for this complicated fact is specific to a particular level. For ex: If I have a Year fact query subject for Locationlevel1Productlevel1, this wouldn't contain Month or Quarter query items in the Fact.

Am I right in this regard to go ahead with a Regular Dimension for levels Month, Quarter, Year?

2. Also on the Report side, I'm handling this fact by constructing huge if-else statements depending on what levels of intersection are selected by the User.

For ex: (if(ordinal(level(currentMember(LocationHierarchy)))=1) and (if(ordinal(level(currentMember(ProductHierarchy)))=1) then Fact11 else ((if(ordinal(level(currentMember(LocationHierarchy)))=1) and (if(ordinal(level(currentMember(ProductHierarchy)))=2) then Fact12 else if ((if(ordinal(level(currentMember(LocationHierarchy)))=1) and (if(ordinal(level(currentMember(ProductHierarchy)))=3) then Fact13...............

Is this the right way to go? It's working great so far. But I'm yet to incorporate Year and Quarter and the multiple dimensional level combinations it's associated with. I would like to use a DMR but I'm using Cognos 10.1 and I have a feeling the DMR route without DQM might be really slow.


Thanks in advance,

KK

[bdbits]

This makes no sense to me. Three dimensions and a single fact table would normally be relatively uncomplicated. Your efforts trying to handle intersections appear to be trying to reinvent the Cognos query engine. Imagine doing this for models with dozens of dimensions, each with several levels, and multiple fact tables. It would drive anyone insane.

If your facts do not naturally roll up, it seems to me there is something fundamentally flawed in your model.

Step back and reconsider whether you have accurately modeled your data. Or share more details here and perhaps we can guide you in the right direction.

[karthik.kadambi]

Hi bdbits,

Thanks for your input. This model also has a Main Fact table with metrics such as Cost, Qty and it's uncomplicated since all I'm doing is a simple Dimension -> Fact join at the lowest level of Dimension.

But the complicated scenario concerns a measure called Patient Count that is unique at every level of every Dimension and shouldn't be rolled up. Say for instance the Patient Counts for 3 Months are 20, 40 , and 20. At the Quarter Level this wouldn't be a direct sum but a distinct count of the Patient ID and would be much lesser than the Sum of 80. This goes the same with levels in other Dimension and when intersected with those levels, the Counts need to be distinct. The tables in the backend are also setup that way incorporating all the permutations/combinations.
Another note is that we aren't bringing a Patient ID and doing a distinct count. The counts have already been precooked.

[MFGF]

Hi bdbits,

Thanks for your input. This model also has a Main Fact table with metrics such as Cost, Qty and it's uncomplicated since all I'm doing is a simple Dimension -> Fact join at the lowest level of Dimension.

But the complicated scenario concerns a measure called Patient Count that is unique at every level of every Dimension and shouldn't be rolled up. Say for instance the Patient Counts for 3 Months are 20, 40 , and 20. At the Quarter Level this wouldn't be a direct sum but a distinct count of the Patient ID and would be much lesser than the Sum of 80. This goes the same with levels in other Dimension and when intersected with those levels, the Counts need to be distinct. The tables in the backend are also setup that way incorporating all the permutations/combinations.
Another note is that we aren't bringing a Patient ID and doing a distinct count. The counts have already been precooked.

Hi,

I understand that the patient counts wouldn't sum as you aggregate them, but what is the actual approach you are using? Would the patient count for a quarter be the average of the patient counts for the three months? Would it be the opening count? The closing count? Something else?

Cheers!

MF.

[karthik.kadambi]

It's a distinct count of all the patient ID's by Year and Quarter.

[skeith99]

Karthik,

If patient ID is on your fact table, the calculation would simply be Count( Distinct Patient ID).  Does this not work?  If this is in framework manager you should be ok, even DMR.  Set the aggregate to calculated.  Or use the Count Distinct aggregation option on Patient ID.  I don't see why either of these would not work.

This will not work in transformer most likely because of size issues.  In which case you would have to create a table with every possible combination of levels and use the "Externally Rolled Up Measure" option.

[karthik.kadambi]

I don't have the Patient ID in the fact table as I'm getting the patient counts directly from the base table(s).

I guess 'Externally Rolled Up Measure" option might be the way to go. But, I'll have to research on how to do this.

[cognostechie]

Hi !

I worked for a healthcare company some time back and there was a Patient Count which should be exactly as your requirement. The count would never be additive and it would be always a distinct count which has to be calculated at every level.

First of all, you should bring the Patient ID as a key (numeric) in your Fact table, after this keep it as a Fact in the Database Query subject but mark it as an identifier in the Business Layer and set it to 'Count distinct'. That would take care of everything. Keep in mind that it is working in a production system and it is providing correct count at every level of every dimension.

Secondly, in Transformer there is no need for 'External rollup' for this. I made a cube there and that one is also working fine. There
is an option to set it to 'Category Count' and that will take care of it. It will only be a problem if there are more than 2 million categories in which case you might have to go for an external rollup cube.

[karthik.kadambi]

Thanks cognostechie. How do I find out the category count before I build the cube?

[cognostechie]

Unfortunately, there is no way to find it out in advance. Category Count is not the same as distinct count, it means that for every possible combination in the cube, it will be one category so it depends on how many dimensions/levels/measures you have in the cube.

It usually does not exceed 2 million so try it out for 2/3 yrs data and see how it goes.

[cognostechie]

Actually, you can generate the categories without building the cube . That will tell you how many categories get generated.

[bdbits]

Run > Generate Categories does all the categories. If the category for which you want a count has a single data source (a query or whatever), click that source then pick Run > Generate Categories From Selected Data Source. This will only run and update categories for the one data source.

This has been an interesting thread. I don't think I ever had a situation quite like this. Kudos to the knowledgeable community here. :-)

[karthik.kadambi]

Thanks All,

Cognostechie,

You mentioned that at the Business Layer I need to set Patient ID as Count Distinct. I'm assuming this property will be carried over to the cube under Data Sources. Once I create a Measure for this it would still fall under Default (Sum) as the aggreagation type, right? I don't have to set it as Count Distinct?

[cognostechie]

Two things..

1> You were trying to create a cube because you thought that the solution doesn't exist in FM? If that is the case then you don't need a cube.
2> Any property that you set in FM does not get carried over to the cube (unless it is a DMR) . In the cube, you will need to set it as a Category Count measure so the Count distinct/Sum does not apply to the cube.

Try it in FM first. It would be better than to create a cube only for this purpose.