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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaidee
Helper II
Helper II

Build correct data foundation - use many tables or one table with many colums?

Hi

 

I am a bit new to Power BI and is now training in Power BI Desktop.

 

I use dummy data I have created in different sheets in one Excel document. I cobine them on customer no. for most.

 

When I go to real data I will get them from a MySQL DB (maybe a view) created for my purpose. I then wonder if there is a difference if a create one table with all date in one row with many colums or it is better to have have one or more fact tables and then some dimensions like this:

http://blog.crossjoin.co.uk/2015/01/01/bidirectional-relationships-and-many-to-many-in-the-power-bi-...

 

As I see it the advantage of having more tables loaded into Power BI is that I have more ways of combining data.

1 ACCEPTED SOLUTION

Your definitely on the right track

 

I would have your orders table as your single fact table and all the others as dimension tables linked on a unique ID

 

You can import a date dimension from somewhere like Azure, have a look here - http://community.powerbi.com/t5/Desktop/How-to-mark-Date-table/td-p/3202

 

Make sure your date fields are all created as date/time type, this is needed for the DAX time intelligence functions (DAX has loads of built in functions for comparing things like sales over different time periods).

View solution in original post

3 REPLIES 3
itchyeyeballs
Impactful Individual
Impactful Individual

It will depend on what your objectives are and the type of data you are working with.

 

If your dataset is straightforward then a single fact table may give you everything you need, although I would nearly always have seperate linked lookup (dimension) tables. Large numbers of columns are not normally seen as a problem in a data warehourse star schema (have a google for articles on database schemas vs data warehouse schemas).

 

If you have complex data then you may need multiple fact tables to account for different granualities or levels in the data, for example you may have sales data recorded by day but budget data recorded by month or quarter. In this case you could have a sales fact table and a budget fact table both linking to a common date dimension table.

 

I would steer clear of trying to emulate many to many joins wherever possible as it difficult to deal with in the BI tools, try to structure your underlying data in a way that makes it as easy as possible to perform analysis with the minimum complexity in measures and formulas. In my experience its worth a bit of extra effort at the outset in designing the structure. 

 

Wherever possible I try to get my source data from dynamic database sources like views rather than direct from tables, in my opinion it gives maximum flexiblity and an extra layer where data can be managed and manipulated before being presented for analysis.

Thanks a lot - it makes sense.

 

I will try to make some tables based on views.

 

Any ideas for inspiration on best practices for modelling and reporting examples regarding my kind of business?

 

It is for a company than holds 20 retailers accros a country with outlets in separate cities. They are selling glasses, so my first thought is:

 

Sales per store

- day, week, month year

- Sales per selected stores and all stores

 

Profit per store

- profit per selected stores and all stores

 

Sales per store compared to last year (same day (corrected), week, month.

 

Sales per customer

- model, price, stores, qty, profit

 

Sales in percent by number of potential buyers (eg. 15-100 years) in a city).

 

Maybe divide customers into 4 segments (for instance age) and use it to see which segment is buying which glasses.

 

 

My plan is that to have one table including orders. One table including Customer info. One table including product info. One table including date dimensions (is a date dimension table something i create manuallu including all dates in the years I want to use?).

 

Any other ideas?

Your definitely on the right track

 

I would have your orders table as your single fact table and all the others as dimension tables linked on a unique ID

 

You can import a date dimension from somewhere like Azure, have a look here - http://community.powerbi.com/t5/Desktop/How-to-mark-Date-table/td-p/3202

 

Make sure your date fields are all created as date/time type, this is needed for the DAX time intelligence functions (DAX has loads of built in functions for comparing things like sales over different time periods).

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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