I'have been reading few posts from the community in order to structure my model. Although have a few questions here.
This is my current model
poc_date_link: contains all FK's from the different tables at date granularity.
In this example, a subscription has a start date and end date. Generated all days (date field) for the user subscription, as a user can not perform any action without having a subscription, (as an annotation, among other tables I did not put in the model cause it would be a bit big). Additionally, for A "user", "subscription", "date" created a unique id. Then I disseminated the ID in the other fact tables.
This allows me then to associate different table models by that particular id, which then grant me access to all fields. Done it this way as my Model has lots of dates (as I think most models) allowing me to play all metrics around a unique date.
Enough introduction, as the rest of the model is kind of the same.
My question is regarding dimensions, facts.
- Shouldn't my fact table be "poc_date_link" as it contains all pertinent keys instead of a dimension table?. I did set up ID as primary KEY. subscription_id is this case is a FK from poc_subscriptions table. Same goes for user_id for poc_users. What concept I'm missing.
- When dragging & placing poc_d2c_movements it automatically converted it to a fact table. What criteria has goodData on do it with out one specifying it.
- poc_book_annotations is also a fact, as It has all records for specific ID is an FK from poc_date_link.
I read on Logical Data Model - Five Pro-Tips for Data Modelling, that relationships works opposite as a normal ERD. In this case are my relationships correctly setup?
thanks in advance!
Best answer by Iva GasparekovaView original
I assume that you are working with our GoodData Cloud, please correct me if I am wrong here.
I will try my best to answer your questions here:
@Iva Gasparekova , apologise for the delay on my response. I've read the articles you mention, seems my model was setup "correctly", as I'm able to play around with all dataset with the designated id.
Thanks in advance for you help !