Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
GulianiG
Helper II
Helper II

Best Practice for creating Static Dimensions Table?

Hello Experts,

I'm working on Dataflows and have a question about creating dimension tables for a fact table with 3 years of event history.

Context:

  • Fact table with events from the past three years.
  • Around 15 dimension tables to map to this fact table.
  • Plan to use dimensional keys in the fact table to join with the primary keys of dimension tables.

My Question: For dimensions that are static over time, like Employee Type (Permanent, Temporary, Contract) and Employee Status (Active, Terminated, On Leave):

  • Is it better to manually enter this data using the "Enter Data" feature in Power BI, or
  • Should I create and import this data from a CSV file? We will pick these CSV files from SharePoint
  • Should I bring fact table then keep dimensional columns and remove duplicates before adding index to get keys
  • Is there an alternate approach like using DAX (SUMMARIZE, VALUES etc.)

I'm looking for the best approach considering performance and maintainability.

Thank you!

 

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Hi @GulianiG 

Both approaches are ok in a dataflow, however in a dataflow you can't relate tables together nor you can use DAX.

I would go with creating dimension tables in Sharepoint hence when someone other than the owner of the Dataflow wants to make changes, he will do it in Sharepoint(only the owner of the dataflow can make changes on it).

Performance wise, Dataflows are sources of truth for semantic models built on top of them, so they should be ok. Rather watch out the performance of the semantic models.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

3 REPLIES 3
GulianiG
Helper II
Helper II

@aj1973 I really like your logic of allowing someone from teh team the ability to change those static dimension values when needed. Thanks
Enter Data approach would restrict it to me being the owner of Dataflow which we don't want
Thanks AJ

aj1973
Community Champion
Community Champion

You are welcome

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

aj1973
Community Champion
Community Champion

Hi @GulianiG 

Both approaches are ok in a dataflow, however in a dataflow you can't relate tables together nor you can use DAX.

I would go with creating dimension tables in Sharepoint hence when someone other than the owner of the Dataflow wants to make changes, he will do it in Sharepoint(only the owner of the dataflow can make changes on it).

Performance wise, Dataflows are sources of truth for semantic models built on top of them, so they should be ok. Rather watch out the performance of the semantic models.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors