CODEWITHSUNDEEP
sql-serverdimensional-modeling
ben.w
ben.w

Reputation: 43

Grain of factless fact table

Designing a factless fact table in sql server 14. Should be quite simple, yet..: I have the need to check the amount of visits per day/client/team/status. Aside from this amount of visits, i need to track the amount of actions done at every visit. 

            SELECT [VISITS_PK]
                  ,[PERIOD_SK]
                  ,[CLIENT_SK]
                  ,[TEAM_SK]
                  ,[STATUSS_SK]
                  ,[ACTIONS_SK]
              FROM [dbo].[FACT_VISITS]

Will return 

              VISITS_PK PERIOD_SK   CLIENT_SK   TEAM_SK STATUSS_SK  ACTIONS_SK
              1 20160515    1   1   1   1
              2 20160515    1   1   1   2
              3 20160515    1   1   1   3
              4 20160515    2   2   1   1
              5 20160515    2   2   1   2

Summary: 2 visits are done, 5 actions are done in total.

Tracking the amount of actions allows me to use COUNT, yet if i want to not take into account the actions and just see how many visits i got in total, do i need another fact table with another grain? I'd rather use one fact table as the amount of visits is in fact just more aggregated.

Edit: The actions_sk contains a link to a dimension table with detailed informations on the performed actions. the first 3 lines are one visit with 3 actions, the 2 last lines are one visit with 2 performed actions.

Upvotes: 0

Views: 270

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Instead of a row for every action, just have one row per visit, with the SUM of the actions in that visit:

          VISITS_PK PERIOD_SK   CLIENT_SK   TEAM_SK STATUSS ACTIONS
          1         20160515    1           1       1       3
          2         20160515    2           2       1       2

EDIT based on new understanding of your data:

Ok, I would change the table name to Fact_Actions, since that is the lowest level of granularity, and Make visits a SK, like so:

          VISITS_SK PERIOD_SK   CLIENT_SK   TEAM_SK STATUSS_SK  ACTIONS_PK
          1         20160515    1           1       1           1
          1         20160515    1           1       1           2
          1         20160515    1           1       1           3
          2         20160515    2           2       1           4
          2         20160515    2           2       1           5

Now you can count Actions by counting rows, and count Visits by counting DISTINCT Visits_SK values.

Upvotes: 2

Related Questions