Forum Discussion
combining data from multiple sources periodically - date hierarchy no longer works
Anonymous as soon as you have a relationship on date column that hierarchy is not there anymore because internal date tables don't exist anymore, having said that, you can create your own hierarchy in date table and use that and this is the right way to do the model, in other words, you are on right track.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Anonymous , Not very clear. But is your calendar covering both 2019 and 2020.
Yes, my date calendar covers all dates 2019 & 2020.
Anonymous can you share the relationship diagram, if you have marked your date dimension as date table it will not show hierarchy. Provide more detail, how your tables are related, and which date column is not working.
StoreRegionTable (SalesAreaNo, StoreNo,StoreType, StoreAddress)
MainExport2020Table (ActivityNo, ActivityDate, StoreNo, CustomerCode, ActuvityType, ActivityBrand)
DateTable (Date, Year, Qtr, Month, WeekNo, DayNo)
Table above formatted as excel table
Above three tables have a relationship one date in date table to many dates in main
One store in store table to on store in main
Each row in main is one activity and the aim is to show the number of activities per day , week , month for each store and then a moving 12 week and daily average per store by brand.
Imported2019DataTable (ActivityNo, ActivityDate, StoreNo, CustomerCode, ActuvityType, ActivityBrand)
-
This table has been combined using 4 excel sheets to onetable using get data and then then appended to the current MainExport2020Data table. This import is large.
The idea was that any new data will be added to a file, imported and then appeneded to main where the relationships.
Anonymous as soon as you have a relationship on date column that hierarchy is not there anymore because internal date tables don't exist anymore, having said that, you can create your own hierarchy in date table and use that and this is the right way to do the model, in other words, you are on right track.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!