data modelling help

[Anonymous]

I have taken a sample excel data from google "Financial data"

I have perfrom data modelling to it ....I need your suggestion if this is the right one or not?

[v-alq-msft]

Hi, @Anonymous 

The data model is correct. It is suggested that you use a star schema as follows. Cross filtering both directions work well for a pattern of table relationship like below.

However, cross filtering direction does not work well with a general pattern as below.

If you have a table pattern like this, with loops, then cross filtering can create an ambiguous set of relationships. For instance, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

For further information, you may refer to the following links.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

https://docs.microsoft.com/en-us/power-bi/desktop-relationships-understand

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

some suggest to create a separate Date table what is the use of it and if at all if i create a date table what difference is it going to make in performance and the realationship from new Date table needs to be linked up with which table? @v-alq-msft @Anonymous 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Generally, your data model will contain a calendar table. It is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of  extracting the date parts from a single column of type date or datetime in calculated columns.

There are a few reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly. Defining a separate Date table is a common practice in any star schema. You should use this technique for any model, even if you do not have a star schema as a starting point. Whenever you have a date column you want to analyze, you should create a relationship with a Date table. If you have multiple date columns in a table, you can create multiple inactive relationships to the Date table in addition to a single active one. You can also choose to create a different Date table for each date column.

You may create a calculated table by CALENDAR or CALENDARAUTO function if you do not have a data table in your data source. For further information, you can refer to the following links.

https://docs.microsoft.com/en-us/dax/calendar-function-dax

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes Its Correct only.