Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem cannot solve and attached a little example.
Data:
3 dimensions : Day, Salesman, Customer (Salesman, Calendar, Custome sheets in xls file)
1 Fact: it refers to all dimension and has 1 measure Price (Data Sell sheet in xls file)
So, the model is very simple.
I want to select one or more salemans (let's say Salesman1) and get:
- the list of all days Salesman1 worked (partial result in Table 1 of my example)
- the list of all Salesmens who worked on same days Salesman1 did (partial result in Table 2 of my example, where there is only a concatenation)
- put Salesman(s) selected, all days Salesman(s) selected worked, all Salesmens who worked on same days the selected Salesmen did in the same cross table
I could get Table 3-1 or Table 3-2 as a partial result but what is missing is the first dimension (Salesman selected).
Actually I want a result like in table 3-2 but adding as first column the list af selected Salesman(s).
I simply tried to add Salesman as dimension but it does not work (see table 4).
So, for example, if I select Salesman1, I would like to see a cross table with:
- 1st colum: Salesman1
- 2nd column: all Salesmen who worked same days Salesman1 did
- 1st row: all days Salesman1 worked (1,5,10,13,20,23,27)
- cell expression: a cross if Saleman actually worked on a day
How can I do?
I know that somhow I have to walk through
Salesman dimension -> Fact table -> Salesman dimension
but I cannot solve thi problem using set analysis.
Jus as a quick and dirty workaround I created another fact table: actully I doubled the fact table. But this solution is not the best solution because in my real application Fact table has millions of records
Thanks in advance
CT
Does anyone have ideas on how to avoid duplication of fact table?
Many thanks