Merging Common Dimensions

[srlabhe123]

Hi All,

• I am going through a very rare scenario where I have 2 Datasets built from dataflow. The datasets names are DS1 and DS2 which are Direct Query based
• Now I wanted to design a report based on these 2 Datasets pulling measures form both the datasets
• The common dimension between these 2 datasets is a Date
• The measures I wanted to pull are DS1->Customer Count and DS2->No Of Orders
• I have tried pulling these measures in separate visuals and works fine, but when I try to join the common Dimension from 2 datastest and try creating a measure like ATP= Divide(Customer Count, No Of Orders) it throws invalid output
• Also if I try to use use it in Metrics having common dimension DS1.Date as column and put ATP an values, it does not give me right data
• Also the report loads data very slow which is not good user experience at all

Questions :- 

Whether joining common dimensions from 2 Direct Query Datasets based on Dataflow is right approach here?

Also what is best approach to achive right result here ?

Suggestions are welcome, also dont wanted to go with Import as data has millions of records in DS2

We are tryting to create environment for users where they can do these by their own and generate reports.

Suggestions are appreciated here.

[ebeery · Accepted solution]

My recommendations:

• Use a central date dimension table, where the "date key" is related to both DS1 and DS2 with 1-n relationships
• To improve report performance, use Import mode.  "millions" of rows does not really justify using DirectQuery in my opinion.  Import mode should be able to handle millions of rows with no issues, as long as you are disciplined about the number of columns and data types you are importing.

[ebeery]

@srlabhe123 having more info about your model (or a screenshot of the diagram) would be helpful.

Are you using a central date dimension table or are you directly relating between the two tables?  If the latter, what is the cardinality of that relationship?

[srlabhe123]

I am directly relating between 2 tables with cardinality 1-n