Forum Discussion

n99's avatar
n99
New Member
3 years ago

Power BI Data Model Challenge

I am designing the Data Model for POWER BI. I have a Relational Data Source. Few Details of Source Tables are given below.

  1. Case Table-It is a transactional table and it stores the case  level details (Case Id Is Unique)
  2. Case Event Table- Transactional Table.One Case Id Can have multiple Events so grain would be Case(Case Id) & Event (Event Id) .
  3. Case Products Table- Transactional Table .One Case Id Can have multiple Products so grain would be Case(Case Id) & Product(Product Id).
  4. Case Products Dosage Fact:- Transactional Table .One Case Product Can have multiple Dosages.
  5. Submission Table:- Transactional Table.One case can be submitted multiple types with different destination.

In  Reports, we have to show.

  1. Case Aggregation:- Case Products in comma seperated Format, All Case Events in comma seperated format, All Products in Comma separated Format
  2. Count of Serious Case /Non serious cases
  3. Case Product Level Aggregation:- All Product Dosages in comma seperated Format.
  4. Submission Data + Case Level details

Note: We are having millions of records in each table.

 We are designing star schema & becase of this we will end up with muliple PBIX files .One File will have only one Fact Table.And in these fact tables data will get redundant. E.g. One fact tables will have case related data and second fact table case + submission related data. It doesn't look correct to me as data format is not properly fitting into a star schema.(These are like Factless fact tables).

 

Any valuable insight please

 

Thanks in Advance

 

1 Reply

  • We are designing star schema & becase of this we will end up with muliple PBIX files

    That is one option but is not a hard requirement.  You can have multiple large tables/queries in the same dataset. 

    Multi dataset (composite) data models have their own baggage.

     

    What have you tried and where are you stuck?