Can someone please help me resolving issue with Linking to common calendar..
Please check attachded test file... based on an example found in this forum, I have tried to link different data sets to common calendar.
However, I am not getting desired result when I use common calendar related field as a dimension. Table in red color shows the total value instead of monthly breakdown.
There's always more than one way to do something, but I'm not seeing how to avoid creating at least a master link table for at least any tables with dates. Otherwise I don't see how to avoid the circular reference. I could of course just be missing something.
For the posted example, it looks like your two tables with dates are MAC_EVENT_MEETING and DEAL_ALLOCATION. These seem to be connected via the MAC_EVENT_MEETING_CLIENT_INFO table. Perhaps you can consider that to be your central fact table - you've already built it, sort of. I'm guessing there's a many to one relationship between INTRMDT_TRD_ID and CMU2_ID. If so, swap out CMU2_ID in that central table with INTRMDT_TRD_ID so your links are at the lowest level. Give every combination of MEETING_ID and INTRMDT_TRD_ID a unique ID, probably with recno() or autonumber(). Left join your dates onto the central fact table from both the supporting tables. Now generate your date linkage table from the unique ID and the date fields on the central table (crosstable). Drop the date fields from the central table, and possibly from the supporting tables as well.
I'm making a whole lot of assumptions about your data, and even if I'm getting it right, think of it as the first pass that I'd try rather than a final solution. But I think that's what I'd be trying.
The problem here is that your calendar is not linked to the rest of your model, you can make the link by modifying the name of your field CalendarName for MEETING_DATE or viceversa
I need to join the calendar with different datasets each have their own unique ids.. apart from that CMU2_ID is common between all the datasets.. (in this example, I am only using 2 datasets)
so now I am running into circular reference issue!
I understand that you have multiple fact tables, that's the problem with your model, I recommend to concatenate/join your fact tables so you can have a star model (one fact table linked to multiple catalogs/dimensions, one of this dimensions should be your calendar).
It's hard for me to see what's going on here, but it does seem like there should be a central fact table of some sort. That central fact table would contain a unique ID for each row, and have all the dates of interest. Then you'd create your single link table off of that central fact table, with the unique ID, calendar date, and date type. You could then drop the original date fields if desired, or keep them if you need them as selections in addition to the master date field.
is there any other way to solve this issue apart from creating a master link table with IDs from all different data sets? All my datasets are currently linked using client's identifier - CMU2_ID
To introduce common calendar with ID columns from different data sets, I have to remove CMU2_ID from most of the places to avoid circular reference..
so wondering if there's any other way to solve this...
There's always more than one way to do something, but I'm not seeing how to avoid creating at least a master link table for at least any tables with dates. Otherwise I don't see how to avoid the circular reference. I could of course just be missing something.
For the posted example, it looks like your two tables with dates are MAC_EVENT_MEETING and DEAL_ALLOCATION. These seem to be connected via the MAC_EVENT_MEETING_CLIENT_INFO table. Perhaps you can consider that to be your central fact table - you've already built it, sort of. I'm guessing there's a many to one relationship between INTRMDT_TRD_ID and CMU2_ID. If so, swap out CMU2_ID in that central table with INTRMDT_TRD_ID so your links are at the lowest level. Give every combination of MEETING_ID and INTRMDT_TRD_ID a unique ID, probably with recno() or autonumber(). Left join your dates onto the central fact table from both the supporting tables. Now generate your date linkage table from the unique ID and the date fields on the central table (crosstable). Drop the date fields from the central table, and possibly from the supporting tables as well.
I'm making a whole lot of assumptions about your data, and even if I'm getting it right, think of it as the first pass that I'd try rather than a final solution. But I think that's what I'd be trying.
thanks for detail reply... and apologies to bring up this topic again...
I did create a link table from different datasets using this logic.. and it seems to be working fine, but ended up creating synthetic keys.. As you can see from datamodel, CMU2_ID is common identifier across all fact tables... there are 3 tables which have "date" concept and linked using composite keys... now even if I create a key per row for those 3 datasets using RowNo(), I still dont see how I can avoid synthetic keys..
I dont mind having synthetic keys, but its affecting performance.. a simple chart which used to refresh under 2 seconds now takes upto 7-10 seconds.. Can you suggest any ways to create common calendar without introducing synthetic keys (or by reducing them)... thanks as always!
Calendar: LET vDateMin = Num(MakeDate(2010,1,1)); LET vDateMax = Num(MakeDate(2011,12,31)); LET vDateToday = Num(Today());
TempCalendar: LOAD $(vDateMin) + RowNo() - 1 AS DateNumber, Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar: LOAD Date(TempDate) AS CalendarDate, Month(TempDate) AS CalendarMonthName, Year(TempDate) AS CalendarYear RESIDENT TempCalendar ORDER BY TempDate ASC;
Link: LOAD CMU2_ID, MEETING_ID, MEETING_DATE as CalendarDate, 'MeetingDate' as DateType RESIDENT [MAC_EVENT_MEETING]; concatenate (Link) LOAD CMU2_ID, MTH_YR, BLOCK_LOSS_DATE as CalendarDate, 'BlockLossDate' as DateType RESIDENT [BlockLoss]; concatenate (Link) LOAD CMU2_ID, INTRMDT_TRD_ID, RPRTNG_DT as CalendarDate, 'DealAllocationDate' as DateType RESIDENT [DEAL_ALLOCATION];
After going through lot of other "link table" and "star schema" related posts, finally was able to resolve the issue... In case someone is interested, below is the updated script to generate key columns and link them together in master link table. Also attached is the updated data model...
[TEMP_MAC_EVENT_MEETING]:
LOAD
*,
Month(MEETING_DATE) as MEETING_MONTH,
if(InYearToDate(EVENT_START_DATE,$(LAST_WORKDAY_VAR),0,1)=-1,1,0) AS EventYTDFlag,
if(InYearToDate(EVENT_START_DATE,$(LAST_WORKDAY_VAR),-1)=-1,1,0) AS EventPrevYTDFlag,
if(InYearToDate(MEETING_DATE,$(LAST_WORKDAY_VAR),0,1)=-1,1,0) AS MeetingYTDFlag,
if(InYearToDate(MEETING_DATE,$(LAST_WORKDAY_VAR),-1)=-1,1,0) AS MeetingPrevYTDFlag,
