date bridge for fact table can contain also date from dimension table?
Hi everyone.
My fact table has a lot of dates for which I created a CanonicalDate in a Date Bridge.
A dimension table of interest that I have, has also a date field (let's call it Insertion_Date ) .
First things first: Ideally, I just want to have a SINGLE date field in general in my app.
Then:
For example: Talking about students, when I click on CanonicalDate_Year 2019-2020, I want to get dates for Insertion_Date in that year, without having to select them manually.
Right now My CanonicalDate is one thing and Insertion_Date is another.
Can I make this work somehow either by keeping it that way or by creating a single datefield?
Final Result: count ({<where DateInserted is in CanonicalDate_Year>} distinct Ids)
or maybe count ({<where DateType={'Inserted'} >} distinct Ids) if I had a single date field.
If your dimension table is connected to your fact table, then I imagine you will have a circular reference (loop) if you linked the dimension table to the Date Bridge.
Can you move the Insertion_Date field into the fact table?
@rwunderlich yes I did and yes I had a circular reference.
I can move it with a join. Wouldn't want to do that because I was given the fact table and the dimension tables like that. But if i have no other option... 🤔
you will need to move the insertion date from the dimension table to the fact table if you have many to many relation between dimension and fact table, IF the relation between those two is one to one, then you can simple join those two table and only use primary key and insertion date as mapping field to create a datefield in Calendar.
Sample Database will help, if you have any other queries because its hard to predict errors.
@Gabbar the relation is actually one to one. But my problem still holds, because then I get circular loops with the date bridge. What do you mean that I need to do after I join? I want to be able to select a CanonicalDate_Year and in set analysis to get selected all the Inserted_dates in that particular year.
How do I move the insertion_Date from the dim table to fact table if the relation is many to many (or many to one) if not with a join?
@ioannaiogr as You have mentioned that relation is one to one:- You can join Dimension and fact table for use as mapping table, this will give you a direct relation with primary key and insertion date which you can use as canonical date and you primary key can be the DateKey and Datetype can be Insertion Date.
or you can just join a copy of DimensionTable With primary key and Insertion Date as only fields and then Drop the insertion Date from Dimension Table. And then create a calendar by concatenation Datetypes in Datebridge.
1st method is same as second method, we just use apply map in first to keep table as similar to database as we can. if need help on how to execute please add a sample data base.
I think i might haven't implemented the concept of date bridge correctly. I have opened another thread but I may come back to this later for details of my case. Thank you @Gabbar
found out because when I join my numbers are wrong... looks like something is wrong in my concept.
Hello, can you elaborate with an example on this @Gabbar
" or you can just join a copy of DimensionTable With primary key and Insertion Date as only fields and then Drop the insertion Date from Dimension Table. And then create a calendar by concatenation Datetypes in Datebridge."
I want to see how it would look as a script, why do i need a copy of dimension table and why would not creating a copy not give correct results
And that is why i asked for Sample Database, So this is my last try:- FactTable:- Load Primary_Key; Join DimensionTable_Temp: Load Primary_Key, InsertionDate resident Dimension_Table;
Drop Field InsertionDate From Dimension_Table;
Now while creating Datebridge, Just add Concatenate Load Primary_Key,InsertionDate as DateKey, 'Insertion' as DateType resident Fact_Table;
If still not solved please add a sample database and a screenshot of your script;
