1 Reply Latest reply: Apr 11, 2012 11:30 AM by Cinzia Tiscar RSS

    Join same tables fact->dimenson->fact

      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