Star Schema problem

[Anonymous]

Hi all!

I'm struggling a bit with a data model that we are using at work. I'm trying to implement a nice star schema, but I'm running into some restrictions. 

It is important to know that all our data is separated by something we call a "program id". All of our facts and dimensions contain this programID, it is what our database is partitioned by.

So the problem is the following: 

Let's assume I have a transactions table, a store dimension and a program dimension. Star schema rules say that I connect both store and program to the transactions table.


In my visualization, I use a programID slicer coming from the program dimension, which in my real model contains info about the program, like start time and end time. I then display the total sales, and the number of stores in total, regardless of sales, this is where the problem starts.

Because of the star schema, the Store table is not filtered by the program table, and the answer, 10, is incorrect (10 is the total number of stores from all programs) . So any metric that I derive from Transaction will work fine, but I will never filter other dimensions by this "master dimension" ProgramID, which is what I need it to do.

To Solve this, I can think of 2 ways:

1: Create an inactive relationship between the program table and the store table, and use USERELATIONSHIP. This makes all my metrics quite ugly and harder to work with.

2: Use a variable to determine the MAX of Program[Program ID], and then apply that as a CALCULATE filter for every metric that uses data from Store as its base. Again, Ugly  😞

Is there another solution that I'm not thinking of?

Thanks in advance!

Jaap

[amitchandak]

@Anonymous , if program id to store is one to many join them to create a join program , store, fact.

or Merge program and store

or try

calculate(countx(Transactions,related(store[store_id])))

[Anonymous]

Hey Amitchandak,

Your first suggestion seems to suggest creating a join between 2 dimensions, but my real data model has about 10 dimensions, of which some are 1,5M rows. Don't think that's a good solution right? Or do I misunderstand?

Same for merging, that works nice in my testcase, but not in a proper data model with many dimensions.

The last option seems less performant than my solutions, because it uses an iterator, no? Also, this still relies on transactions, so any stores that didnt have any transactions would not get counted, which is not what I want. I just want a count of the number of stores in the store table for this particular program.

Thanks for thinking along though!

Jaap

[v-yingjl]

Hi @Anonymous ,

You can consider sharing a simple dummy sample file with the star schema like your picture shows for further discussion.

Sample file and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Yingjie Li

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

[Anonymous]

Fair enough, here is the barebones example with the expected result:

https://drive.google.com/file/d/183cMwc8M3xgpXxYaGK_qGA31u_MaZEwa/view?usp=sharing

Just be mindful that my real model has 10+ dimensions with a few having millions of rows.

Thanks!

[hemantsingh]

Hi @Anonymous ,

  For this scenario Crossfilter is your friend.

Try a measure like this :-

Counting_Store_Crossfilter =

CALCULATE(COUNTROWS(Store),CROSSFILTER(Transactions[StoreID],Store[StoreID],Both))

Note:- No need to change the direction of the relationships to both

The result which you want can be achieved with this. You can further explore more on crossfilter. There are a lot of you tue videos too. Hope this helps
Regards,

Hemant

[Anonymous]

Hey both:

"By the way, in your table, there is only three rows about VN06 so the value of VN06 should be 3, how could it be 6..." Arg you are right, that was a typo. Sorry!

Thanks a lot for your suggestions! However, both suggested solutions go via the transactions table, and both rely on us having transactions in the transaction table for each storeID that we want to count. I want to count all the stores regardless of  whether transactions exist for those stores.

I created a new test file that shows this problem a little better. There are 2 stores in VN06 that have 0 transactions. The total number of stores should be 5. 
https://drive.google.com/file/d/183cMwc8M3xgpXxYaGK_qGA31u_MaZEwa/view?usp=sharing

The best solution I have come up with so far is an inactive relation between Store and Program, and then this measure:

Counting_Store_UseRelationship =

CALCULATE(COUNTROWS(Store),USERELATIONSHIP(ProgramID[Program ID], Store[ProgramID]))

[v-yingjl · Accepted solution]

Hi @Anonymous ，

You are right. Currently userelationship is the better way to solve this issue because your relationships between the store table and transaction table is [store id] instead of [programm id]. In this case, the result of VN06 must be 3 rather than 5 unless you choose userelationship to pass [programm] parameter.

Best Regards,
Yingjie Li

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

[Anonymous]

OK thanks,

I was just afraid that there was a better way of doing this. It must be quite rare to have multiple clients with their own transactions etc in a database together 🙂

[v-yingjl]

Hi @Anonymous ,

Try to change to cross filter direction from single to both

By the way, in your table, there is only three rows about VN06 so the value of VN06 should be 3, how could it be 6...

Best Regards,
Yingjie Li

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