I've created a master calendar in my set analysis. I've got few tables with different types of data (i.e. budgeted, realised etc.) but they all have date dimension. I would like to have them all connected to the master calendar. Is this correct and the best solution to that, that I add to Master Calendar a separate row with date to connect invidually to the tables?
I mean if in tables (Orders, WorkTime, Wages) I've got column named YearMonth (YYYYMM), I need to rename them so that they would be different like YearMonthOrd - for orders, YearMonthWT, YearMonthW and then add the same row to master calendar like it's shown below?
I would like to avoid creating new dimensions and have in master calendar only one universal value YearMonth
TempDate AS DocumentDate,
Year(TempDate)& Num(Month(TempDate),'00') AS YearMonthOrd,
Year(TempDate)& Num(Month(TempDate),'00') AS YearMonthWT,
Year(TempDate)& Num(Month(TempDate),'00') AS YearMonthW,
week(TempDate) As Tydzien,
Year(TempDate) As Rok,
Year(TempDate)& Num(Month(TempDate),'00') AS YearMonth,
Month(TempDate) As Miesiac,
Day(TempDate) As Dzien,
Solved! Go to Solution.
Look for common fields in each table.
I had same situation, 4 date fields in single table...
I just converted one of the fields to Date(FieldName) as Dates
And in Master Calendar, Date(TempDate) as Dates.
You don't have to link all dates to master, just one is enough (Fact Table).
If feasible can you post your script or date model screen shot or perhaps a sample qvw?
And of course there are many different ways to do this, but I achieved it in different way and worked perfectly fine for me...
Cus Master will have all Calendar dates for years and your field will have only dates related to your data.
I attach you my qvf file. It is filled with sample data. There are some language differences between thos versions, because dimension names are in Polish language.
Case I've mentioned reffers to tables ZleceniaUliceView, CzasPracy (, WynagrodzeniaWskaznikiView, WynikiSzczegolView. They are connected to master calendar with the fields ...RokMiesiac (YearMonth). The last table (WynikiSzczegolView - fact table) is the only that connects to master calendar by document date (DataDok). First three tables has only information about the year and the month.
I'm not sure of what you mean. I don't have many date dimensions in one table, but many tables with one date dimension.
Can you look at my qvf file?
A combination of multiple master calendars and canonical dates may work in this scenario; See the following blog posts for details:
I've changed my model using hints from the blog You suggested. I;ve created two separate master calendars. But I don't know how to join them into one DataBridge as it was shown in the blog. Could you help me to create the code fragment?
This is the fragment from the load editor:
It's a bit tricky without really knowing your data but from what I understand the Canonical date approach needs a relationship between the dates, like the example in the blog post where all dates tie back to the OrderID and I don't see that common field in GLDataview and SalariesIndicatorsView.
I'm thinking multiple master calendars(without the canonical dates) could work in your scenario.
I looked at your data model in the attachment qvf. In my opinion everything is implemented correctly. To work with the date in the filter can be used any of the available formats. If you want to use a format different from the existing, it will automatically create another field with required format during loading master calendar.
I thought it may be some clue that there is connection betweent CostCenterId in GLDataView and BudgetCostCenterId from SalariesIndicatorsView. They are linked together in StructureView.
Will it be helpful?