Solved

Model query

  • 3 February 2023
  • 2 replies
  • 59 views

  • Participating Frequently
  • 5 replies

Hi folks,

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

 


Overview

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.

 

  1. 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.
  2. 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.
  3. 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!


 

icon

Best answer by Iva Gasparekova 3 February 2023, 16:23

View original

2 replies

Userlevel 3

Hi Martin,

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:

  1. From provided screenshot, it looks like your dataset "Poc data link" contains only the ID attribute, set as a primary key, which seems to be correct to me. What seems to be off with the connection to the dataset "Poc subscription"? You can find some hints about the relationships in this article.
  2. It seems like your dataset contains the Fact "Mrr value gbp", I assume it was recognized from the datatype used for this "column". Is it a numerical data type, pelase?
  3. Ids are usually created as attributes. Why would you rather consider it as a fact, please? More details about facts can be found here.


 

Hi @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 !

Reply