Avoid role play dimension in DW

Hi All ,

I'm new in this forum and this will be my first post , hope put my query in a right place:)
I have 3 Fact tables all have different Date and time fields , Priority and Address  

- Fact A ( DateTime 1, DatTime 2, DateTime 3,Priority1 , Priority2 ,Priority3,CountRowNumA )
- Fact B ( DateTime 4 , DateTime 5,Address1,CountRowNumB)
- Fact C  (DateTime7, Priority4,Address2,Address3,CountRowNumC)

So have 4 Dimension tables:
-Date ( keeping Date , Year , Month , week and etc.)
-Time ( keep 1 hours , 30 minutes , 10 minutes Interval )
-Priority  ( Priority ID )
-Address ( Suburb Name )

The main query of these table would be something like :
- Show CountRowNumA ,CountRowNumB,CountRowNumC for 1/2/2014 and Priority ID equal to 2 and Suburb Name = 'BBBBB'

If I create dimensions with role play then users will forced to pass parameters for each date/time/Priority /Address and it should be only one single query entry .

Would it be advisable way for cover these type of queries ?

Thanks .

Please let me know if it in not clear .

When I'm looking for rows where priority = 'ABC' in fact A, do I need to to query all three priority relationships? If so, then what you really have is a multi-valued column that is better suited to a bridge table.

Dimensional roles are basically just DB aliases. If you reference the same table more than once in a query then you have to alias it (this is not specific to querying star schemas). Assuming that a query parameter is used in multiple places in the query, if you put the query in a Stored Procedure or use a BI Tool you can probably get away with only entering a parameter value once (as you are hiding the complexity from the users) but if you let your users execute raw SQL (which is not a good idea - but that's another conversation) then they would have to enter the values multiple times

BoxesAndLines and  nick_white thank you for replying .

What Data Analyst wants to see in report is Priority as dimension and when put each of measures against it , see the amount of measured that picked from related Fact Tables . So as an example when he pick Priority  AA,BB and CC then


" />

Your design of Fact Table A doesn't make it easy to produce this sort of report but it can be done using multiple SQL statements:

SELECT PRIORITY1, 'FactA-Priority1', SUM(CountRowNumA)
FROM FactA
WHERE PRIORITY1 IN ('AAA','BBB','CCC')
UNION
SELECT PRIORITY2, 'FactA-Priority2', SUM(CountRowNumA)
FROM FactA
WHERE PRIORITY2 IN ('AAA','BBB','CCC')
UNION
SELECT PRIORITY3, 'FactA-Priority3', SUM(CountRowNumA)
FROM FactA
WHERE PRIORITY3 IN ('AAA','BBB','CCC')
UNION
SELECT PRIORITY4, 'FactC-Priority4', SUM(CountRowNumC)
FROM FactC
WHERE PRIORITY4 IN ('AAA','BBB','CCC')

To make it easier to produce this type of report you probably need to re-design (or create a new) fact table that has a single reference to the Priority Dim and also includes a Priority Type Dim (or include Priority Type in the Priority Dim as a hierarchy)