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.
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.