3 Replies Latest reply: Apr 17, 2013 9:19 AM by Byron Van Wyk RSS

    Multiple Fact Tables

      Hi everyone,

       

      I've got a document with 4 facts table. There are dependent from each others.

       

      Table 1 = Requests.

      Each request can generate one or more Trip (Table 2).

      A trip can be comosed of one or more services (Table 3) (but services can also exist without being linked to a trip neither to a request).

      A Service can have one or more assignment.

       

      I've already managed to make a single fact table but I don't know how to manage each mesures.

       

      My Full fact table seems like

       

      Request ID     Request Date     Trip ID     Trip Date           Service ID      Service Date     Assign.ID       Assign Date      Measure Service

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      1                    01/01/2012          1          02/01/2012          1                 02/01/2012       1                    02/01/2012          10

      1                    01/01/2012          1          02/01/2012          2                 02/01/2012       2                    02/01/2012          20

      1                    01/01/2012          1          02/01/2012          2                 02/01/2012       3                    02/01/2012          20

      -                     -                         -          -                         3                 02/01/2012       4                    02/01/2012          30

      2                    01/01/2012          2          02/01/2012          4                 02/01/2012       5                    02/01/2012          40

       

      My question is for the request 1 - service 2.

      If  i make sum on the service measure I wil have 50. What I want is 30 because 2 is the same service and it is duplicate because it is composed of 2 assignements but it should be counted only once. 

       

      How do you manage that?

      I think using Aggr but I don't really understand how it works and I could use some help.

       

      Thanks

      caro.

        • Re: Multiple Fact Tables
          Jason Michaelides

          Try:

           

          Aggr(Sum([Measure Service]),[Request ID]) or you may need the DISTINCT qualifier in there:

          Aggr(DISTINCT Sum([Measure Service]),[Request ID])

           

          Hope this helps,

           

          Jason

            • Re: Multiple Fact Tables

              Hi Jason,

               

              I don't really understand how the aggr works.

              Could you explain it to me?

               

              I know the distinct. Without the aggr(), I could use it for [Request ID] 1 and it would work but then if I don't select the request ID and I have 2 time the same measures for 2 different request ID, then I don't want the distinct on it.

              Can Aggr solve this?

               

              Thanks for your help

               

              Caro.

                • Re: Multiple Fact Tables
                  Byron Van Wyk

                  I will explain it in my cowboy way

                   

                  Aggr ignores dimensions. So if you want an expression to calulate using dimensions that aren't present in your table or you want to calculate something using only the first 2 dimensions in your report and not all 3, you would use aggr. Essentially you can build an entire straight table with no dimensions using aggr and still get the values you want. It is a resource intensive function, so use with caution on 'big' data sets

                   

                  To answer your second question and using the above explanation

                   

                  Aggr(DISTINCT Sum([Measure Service]),[Request ID],[Serivce ID]). Literally just add the dimension that has the logic you want to apply when summing the values you deem distinct

                   

                   

                  Cheers,

                  Byron