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

[jaidee]

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.

[itchyeyeballs]

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.

[jaidee]

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?

[itchyeyeballs · 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).