Analytical Need
Analyzing information by date can be very useful, but not every source data set provides enough information to do this kind of analysis.
Modeling Challenge
We will need to import a date table, which will act as a reference for all time queries.
Solution
Example- Employment Table
An employment table contains the following columns:
Employee Name, Start Date, End Date
This describes when an employee started and stopped working at a company.
However, if we want to created a line chart that displayed days on the X axis, and charted the number of employees over time, it will not be possible with this data set.
This is because the data set will not contain every individual date (assuming that there were days where the company did not hire or fire anyone). If every date is contained in your data set, use the instructions in
Create a common date selection.
This is why we would need to import a date dimension table.
Date Dimension Excel File
Under Attachments (bottom of this article), you will find the DimDates.xlsx file. This file contains day level information for dates ranging from January 1, 1991 to December 31, 2039 (when you use this Date as a filter, please remember to remove any dates that are lower than your minimum date and higher than your maximum date).
After downloading the file, open the ElastiCube Manager, click Add Data-> Excel File, and select DimDates.xlsx. In the preview, make sure the field Date is imported as a date-time field. If it isn't, check the culture setting below the preview pane, and select English (United States).
Using Custom SQL to Answer the Question
We still need to establish how many employees existed in the company on any given day.
We can do this by creating a custom SQL table that counts the number of employees per day, or we can create a table that has a row for every day that an employee was employed by the company.
We'll create the employee-day table for this example (Check the amount of history and the number of employees before choosing this option, to determine how many rows will get created).
SQL:
SELECT E.EmployeeName,D.DateFROM Employee EINNER JOIN DimDate D ON D.Date>= E.StartDate AND D.Date <= E.EndDate
Note on the join clause:
We use the Dim Dates "Date" field. We can also use the "DateNum" field but for this, we would have to convert the E.StartDate & E.EndDate to the same format. The information on this process can be found under
Numeric Representation of date fields.Now, create a line chart that lists the days from this new table, and create a value that counts the number of employees on that day.
Date file attached below.
