If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Alias Shortcuts for Dates

Started by kmedwards76, 03 Sep 2019 05:37:08 PM

Previous topic - Next topic

kmedwards76

Does this look correct? I have an employee dim with two dates, hire and termination date that will need to be used for reporting different time periods. I keep getting confused on when to use alias shortcuts. Is the following acceptable? Term^Date and Time Periods Term^Date are both alias shortcuts from the corresponding Hire^Date and Time Periods Hire^Date.

Thank you,


bus_pass_man

QuoteIs the following acceptable?

Maybe.  It depends on what you're trying to do and what your data and metadata are. I'm not familiar with your data and I don't know what keys you are using to join the tables, and I don't know all the query items in your model.   
 
Concerning the question about the use of aliases (aka role-playing), if you look at the sample models you will see role-playing demonstrated with the time dimension table and some facts (sales fact being the one I remember the most).  In the case of sales fact there are three time dimensions defined, each using a different key - order day, ship date, and close day.   That might help you think through parts of the immediate modelling problem.

It looks like, in your case, that you are trying to join your time dimension to your employee dimension, presumably because employee metrics model doesn't have a key for time and, subsequently, you can't join it directly to the metrics model fact table.   I'm guessing you need to have a window for each record representing an employee and its start and end dates or for fixed time periods (i.e calendar or fiscal year ) for whatever metrics are in the employee metrics table.

I'm a bit curious to understand why you have the outer join for Term^Date to employee model.  It is presumably because some termination date values are null.   You could deal with this by creating an expression which coalesces the termination date and either an arbitrary date such as 2999-12-31 or the current date, using the current date function and use that.  Again, the application and the trade-offs entailed by it would prevail over any formalistic solution.

Another question I would have is, how are the data in hire date and termination date  not adequate to your needs? 

Is employee model a type 2 SCD?

What is fact employee key (in the employee metrics qs)?   

I'm not prepared to comment further without much more understanding of the model but I hope this has given you some assistance.