Hi, I have a request for a single date selection solution for a dashboard with many date fields specific to the kinds of activities those date fields came from. End users are also fine having dates with no activity factored into averages and such, so if exerciselog has data for 6 of the 7 calendar days, they want those no activity days to deflate the average.
My understanding is that concatenating many tables as much as possible is less demanding than link table when there are millions of records involved. Short story is I have tens of millions of rows, dozens of columns, and maybe 15 tables, and I'm worried concatenating it all into one super table might crash something. If the short answer is 'yes, concatenate like crazy and change their date field to the same field name", I'll go for it. I thought I'd run this idea by the community before spending much time implementing it and crashing things. Much more detail is below.
I have these basic data tables:
Clients - basic info about clients, links to offices on client id
Offices - basic info about offices in clients, connects to clients on client id, to users on office id
Users - contains user id and basic data about users, links to offices on office id
From there I have these activity tables
(and many more)
The activity tables all connect back to users on user id, and their fields are all unique to them (for instance exerciselog has exercisedate and sleeplog has sleepdate).
Master calendar table I'm going to throw in:
min(NUM(ActivityDate)) as minDate,
max(NUM(ActivityDate)) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Date(TempDate) As FileDate,
Year(TempDate) As FileYear,
Month(TempDate) As FileMonth
Order By TempDate ASC;
Drop Table TempCalendar;
My idea is this:
1> change all the activity-specific date fields to be 'activitydate' on all of those tables
2> stick the word concatenate in front of all of the loads after the first table (I'd give up the ability to locate dimensions by table name on the object wizards, but the entire rest of the dashboard is finished so not too worried)
3> make a master calendar that links to above supertable with the activitydate field and the standard run of year(activitydate) as activityyear, month(activitydate) as activity activity month, etcetera.
Will anything explode or cause me problems down the road? I've done this with smaller data sets, but rather fear doing it for millions of records populating dozens of fields.