Using a fact item as a measure in a dimension table

[RubenvdLinden]

I have a star scheme with a time dimension table (DIM_DATE), which contains the following attributes:
DATE_KEY, YEAR, QUARTER, MONTH, MONTHNAME, DATE, FLAG_WEEKEND

FLAG_WEEKEND is either a 0 or 1, depending on the weekday (as to be expected, saturdays and sundays are 1). Cognos Framework Manager detects this database field as a fact.

If I publish this model and create a crosstab in Report Studio with FLAG_WEEKEND as a measure, the year as columns and e.g. organisational units as rows, the report returns 104 per year, which is exactly the number of weeks (52) times 2 weekend days.

However, the measure should sum FLAG_WEEKEND for any corresponding rows in the fact table like in this piece of SQL:

select dim_date.year, dim_org_unit.displayname, sum(dim_date.flag_weekend)
from dim_date, dim_org_unit, fact_table
where fact_table.date_key = dim_date.date_key and
fact_table.org_unit_key = dim_org_unit.org_unit_key
group by dim_date.year, dim_org_unit.displayname


What am I missing here? Any contribution to the solution of my problem will be greatly appreciated!

[RubenvdLinden]

One thing I did find out is that if you completely drill down to the lowest organisational unit, the measure is correct. Strange!

[blom0344]

This is quite a common starters  ;) issue. By having a measure at the 1 side of an 1:n cardinality the value will become 'overcounted'/'oversummed' by the exact value of n (52) in your case.
Cognos does not detect facts, it assumes that numerical values are probably facts in order to save the developer from resetting all usages.
A flag is an attribute, no matter if it is a string or numerical. You should use it as one

[RubenvdLinden]

I realised the 1:n issue a few hours after I left the office and I feel real stupid ;D
I will change the flag back to an attribute. However, I still would like to count all incidents on weekend days (FLAG_WEEKEND = 1). I can create a calculation which will change a 0 to 'Working day' and 1 to 'Weekend' and use this attribute in my reports, but actually I do not want to filter all reports for the 'Working day' string. Is there a good practice to solve this in the model instead?

Thank you in advance for your time and patience with my 'starter' issues  ;)

[RubenvdLinden]

Just to be clear, FLAG_WEEKEND is just one example of a (0/1) flag in my model. There are also some 'junk' dimensions in the model with about 7 flags (0/1) each; this way I saved a lot of space in my fact table.  I'm now looking for a good practice to count all flags of one type with value '1'.

[blom0344]

I am not sure why you would abstain from using the flag/ indicator as a filtering option.
In similar cases we let the user decide to omit/embrace the filter by adding a yes/no option on the prompt page. We then use a render variable to show 1 or 2 possible report pages.
Example:
Add a prompt that asks for : 'show only weekend incidents:'
Design your report to have 2 report pages with  associated queries. If only weekend incidents are wanted, then the report with the filter is produced. When all incidents are required the second report page (unfiltered query) is kicked..

Can you cope with this within the model. Yes, you can , but I am not sure you would need it..

[RubenvdLinden]

Thank you again for your response, you're very helpful.

Concerning the flags 'junk' dimensions, I already mentioned I use those to save space in my fact table. To be able to use those flags as boolean measures (and to be able to sum the 1 values), I thought it might be a good idea to change the definition of my fact table query subject and add the flags in the junk dimensions using inner joins.

To give you an example, one of the junk dimensions (DIM_INJURY) looks like this:
INJURY_KEY, FLAG_BRUISE, FLAG_BONE_FRACTURE, FLAG_PAIN, FLAG_SCRAPE (and so on)
This dimension contains every possible combination of flags, so the fact table only needs to contain the INJURY_KEY.

Could you please comment on my idea?

[blom0344]

What an interesting case. [spend some time with a healthcare BI unit , so I am finding this quite interesting]
I assume that in your ETL you are performing some sort of lookup procedure to establish the injury-key? (as this is a meaningless key in your case)

I am trying to anticipate the reports that you may want to build on this model. Say, you get the request to list patients for a certain time-interval that were bruised but had no fractures?
Is the injury key assigned only once (say when a patient is admitted) or do you need to build history etc..

[RubenvdLinden]

In this dimension, the key is not completely meaningless.
I have assigned a calculated unique number to each type of injury. Since every flag has two options (0 or 1), I use to the power of two (^2) to calculate these numbers. For example, bruise is 2^0 (=1), bone fracture is 2^1 (=2) and a scrape is 2^2 (=4). The dimension key can simply be calculated by summing the numbers of registered injuries per incident. In this example, an incident fact with both a bruise and a scrape has 5 as a key (1 + 4). You can try it yourself if you like; every combination of flags will return a unique key!

The granularity of this model is incident, so I keep track of the injury history per incident.
It really is an interesting and fun case, but also difficult at times to keep things flexible! In your example (list patients for a certain time-interval that were bruised but had no fractures), I should use those flags as attributes. This allows me to perform powerful analysis on the combination of injuries. For a simple count of all bruises by a business user, a measure would better.
Is there a way to get the best out of both worlds?

[blom0344]

I would consider building a statistics table (or perhaps a materialized view) to store the measures as an additional reporting table. If that is too inflexible, you could consider creating 2 sections in the model, one for performing analytics (use flag as attribute --> filter) and an additional one for statistic purposes (flag as a counter)

[RubenvdLinden]

Sorry for the delay, but I had to work on a different project for a few days.

Since we don't expect to many rows in the fact table (about 5,000 a year) I tried the latter by simply adding a query object with custom SQL to my model. This query object contains the primary key of the fact table (which is a document guid) and the injury flags in the junk dimension table. Then, I added a 1-1 relationship to the fact table and voila, works like a charm and performs great!

Thanks again for helping me out.