Forum Discussion

Julien_F's avatar
Julien_F
Helper I
6 years ago

Benchmarks table in Star Schema model

Hello, 

 

I am stuck with an issue, I mean not an issue but not enouth skills from my side to solve that one. 

I checked the forum and found some path to solve it but I only solved a part of it 😕

 

Here we go: 

 

- I have a star schema model ( look the picture ) with several dimensions and one fact table. All dimension attribute are not in the fact table, for example language is in the dimension level 3 and not in the fact with a bolean value ( only two language ). Maybe that detail is important for my issue, I don't know. 

- The star schema is working well as I am able to dashboard everything. 

 

Client asked me to add a benchmark table... 

Benchmarck is base on metrics from the fact and some dimension attributes, let 's say one or two attributes by dimension such as : 

Level one: Campaign name and cluster 

Level two: Strategy and publisher 

Level three: target and format 

 

The idea is if I pick a campaign I will be able in the same table to see its becnhmarks because the campaign I want to analyze has the same attirbute ( except the name of course) but same publisher, strategy, target and format. 

 

Also I would like to add seasonality : if the campaign I choose run between 01/01/2020 and 15/01/20 it should give the bench for the attributes I was talking above and the mont JAN as I will group my bench by 6 or 7 dim atribute and month_year. 

 

Then the Rolls Royce could be adding some conditional fromating to say if above bench than GREEN if abose RED, 

 

I have part of the solution by using : 

 

Calculate, summerize, addcolums but sometine it deosn't work or I have to do it with several filters, 

 

--> I am stuck 

 

Thx for your help, 

 

J.

 

 
General Comment
Tips and Tricks
Tutorial Requests

1 Reply

  • Julien_F's avatar
    Julien_F
    Helper I
    6 years ago

    Hi, 

     

    To give extra info, 

     

    I tried with a measure  :

     

    CALCULATE (
    [CPM],
    SUMMARIZE (level3_dimension,level3_dimension[X],level3_dimension[Y],level3_dimension[Z]),
    ALL(level1_dimension[A])
    )
     
    +: It is in the fact
    -:  - only one metric but I could create more than once so it is not a big issue 
       - If I filter the table benchmark is no more accurate , except for campaign because of the (ALL)
       - the ALL function does not accept attributes from other table like once in DIM 2 and 3 
     
    Then I tried with a table : 
     
    Table =
    var combinetable = ADDCOLUMNS('fact',"test",RELATED(level2_dimension[X]),"test2",RELATED(level3_dimension[Y]),"test4",RELATED(level3_dimension[Z]),"test5",RELATED(date_dimension[date_date].[Month]),"test6",related(level2_dimension[w]),"test7",RELATED(level2_dimension[s]))
    return
    SUMMARIZE(combinetable,[test],[test2],[test4],[test5],[test6],[test7],digital_dashboard_facts[click],digital_dashboard_facts[cost],digital_dashboard_facts[impression])
     
    +: I have everything
    - : cannot make a relation with my star schema
    It is possible to filter that tabe based on another table? Like a simple table showing: atribute like test, test2... and filter that table based on the table value? 
     
    Picture below to explain better whet I want as benchmark in my "campaign" results visualisation tool and where is it from:
     

     

     
     
    Thx you,
     
    J.