cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HusnaBanu413 Regular Visitor
Regular Visitor

data modelling help

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?

screenshot.PNG

 
1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-alq-msft
Microsoft

Re: data modelling help

Hi, @HusnaBanu413 

 

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.

View solution in original post

4 REPLIES 4
kiranta1 Frequent Visitor
Frequent Visitor

Re: data modelling help

Yes Its Correct only.

Microsoft v-alq-msft
Microsoft

Re: data modelling help

Hi, @HusnaBanu413 

 

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.

a1.png

 

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

a2.png

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.

HusnaBanu413 Regular Visitor
Regular Visitor

Re: data modelling help

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 @kiranta1 

Microsoft v-alq-msft
Microsoft

Re: data modelling help

Hi, @HusnaBanu413 

 

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.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors