How to build a business user friendly framework manager package for query studio – options

Check list of all posts

The initial detail proposal for building friendly framework manager was published. This document will show three different options of presentation tier with the same result. Which option to use will depend on business users for given environments.

Sample data and relationship

There are Fact 1 and Fact 2 with conformed dimensions D1 and D2, while D3 is not conformed dimension and associated with Fact 1 only.

Presentation option 1

It is typical raw star scheme group, user can have clear idea about relationship with each namespace. However, users somehow need to understand conformed dimension concept, and to select query item from multiple namespace.

Presentation option 2

In order to make sure that fact 1 and fact 2 are mostly used altogether, we have make a new fact table on business layer to present as single fact to business user. However, user still need to aware of non-conformed dimension D3.

Presentation option 3

To emphasis the conformed dimension and cross functionality, we separate dimension and fact as two folders. This is probably most user friendly framework manager, but user still need to make what dimension and what fact can be used together. If all dimensions are confirmed, then this is perfect solution.

Common result from all three different options

It is interesting to see that all three options generate the same result.

Common SQL statements from all three different options

Regardless of what option to use, the same SQL statement is generated behind the scene.

Fact 1 group

select distinct

      D1.D1  as  D1,

      D2.D2  as  D2,

      D3.D3  as  D3,

      F1.M1  as  M1

from

      great_outdoors_sales.GOSALES1020.dbo.D1 D1,

      great_outdoors_sales.GOSALES1020.dbo.D2 D2,

      great_outdoors_sales.GOSALES1020.dbo.D3 D3,

      great_outdoors_sales.GOSALES1020.dbo.F1 F1

where

      (D1.D1 = F1.D1) and

      (D2.D2 = F1.D2) and

      (D3.D3 = F1.D3)

Fact 2 group

select distinct

      D1.D1  as  D1,

      D2.D2  as  D2,

      F2.M2  as  M2

from

      great_outdoors_sales.GOSALES1020.dbo.D1 D1,

      great_outdoors_sales.GOSALES1020.dbo.D2 D2,

      great_outdoors_sales.GOSALES1020.dbo.F2 F2

where

      (D1.D1 = F2.D1) and

      (D2.D2 = F2.D2)

Fact 1 and 2  together

with

D as

   (select

          D1.D1  as  D1,

          D2.D2  as  D2,

          D3.D3  as  D3,

          F1.M1  as  M1,

          RSUM(1  at D1.D1,D2.D2,D3.D3,F1.M1  for D1.D1,D2.D2  order by D1.D1 asc,D2.D2 asc,D3.D3 asc,F1.M1 asc  local)  as  sc

    from

          great_outdoors_sales.GOSALES1020.dbo.D1 D1,

          great_outdoors_sales.GOSALES1020.dbo.D2 D2,

          great_outdoors_sales.GOSALES1020.dbo.D3 D3,

          great_outdoors_sales.GOSALES1020.dbo.F1 F1

    where

          (D1.D1 = F1.D1) and

          (D2.D2 = F1.D2) and

          (D3.D3 = F1.D3)

    group by

          D1.D1,

          D2.D2,

          D3.D3,

          F1.M1

    order by

          D1 asc,

          D2 asc,

          D3 asc,

          M1 asc

   ),

D31 as

   (select

          D1.D1  as  D1,

          D2.D2  as  D2,

          F2.M2  as  M2,

          RSUM(1  at D1.D1,D2.D2,F2.M2  for D1.D1,D2.D2  order by D1.D1 asc,D2.D2 asc,F2.M2 asc  local)  as  sc

    from

          great_outdoors_sales.GOSALES1020.dbo.D1 D1,

          great_outdoors_sales.GOSALES1020.dbo.D2 D2,

          great_outdoors_sales.GOSALES1020.dbo.F2 F2

    where

          (D1.D1 = F2.D1) and

          (D2.D2 = F2.D2)

    group by

          D1.D1,

          D2.D2,

          F2.M2

    order by

          D1 asc,

          D2 asc,

          M2 asc

   )

select

      coalesce(D.D1,D31.D1)  as  D1,

      coalesce(D.D2,D31.D2)  as  D2,

      D.D3  as  D3,

      D.M1  as  M1,

      D31.M2  as  M2

from

      D

       full outer join

      D31

       on (((D.D1 = D31.D1) and (D.D2 = D31.D2)) and (D.sc = D31.sc))