0

My fact table contains details for clients who attend a course. To ensure i can get a list of clients registered on any particular day, I have not related the date dimension to the fact table.

Instead i created a measure that does basic between logic (where startDate <= selectedDate && endDate >=SelectedDate)

This allows me to find all clients registered on one single selected day. There are a few drawback to this however:

-I have to ensure the report user only selects a single day, i.e. they cannot select a date range.

-I cant easily do counts for samePeriodLastMonth or Year.

Is there a better design i should consider that will still allow me to see counts of registered clients on any given day, along with allowing me to use SamePeriodLastMonth/Year functionality?

wilson_smyth
  • 1,202
  • 1
  • 14
  • 39
  • 1
    Please post the structure of you fact table and of the time dimension. – Marmite Bomber Apr 10 '17 at 18:46
  • 1
    Is the problem with date ranges, and period counts, that you double count the clients? This is easily solved by using a distinct count on the unqiue client code. – Nick.Mc Apr 11 '17 at 01:51

1 Answers1

0

Would you mind uploading the structure of your fact and dim tables?

Just a thought bubble: if you would like to measure counts for a program over calendar years, I believe you would definitely need to create a Date dimension. Also depending on your reporting needs you might want to consider whether you need an Accumulating Snapshot Fact table.

Please find further details on this: http://www.kimballgroup.com/2012/05/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/

Cheers Nithin

NITHIN B
  • 214
  • 1
  • 9