Historical Data

Question 1

T/ F only Caboodle tables have a change type, not columns.

Answer 1

False. Columns and tables each have a change type depending on whether or not they track historical data in the source.

Question 2

A tables change type is listed under what section in the Dictionary?

Answer 2

Properties section.

Question 3

T/F in the Dictionary, Columns not labeled as type 2 are Type 1

Answer 3

True

Question 4

Type 2 tables have additional columns to help make sense of historical data in the table. What are they?

Answer 4

DurableKey, StartDate, EndDate, IsCurrent

Question 5

The FullAccess schema contains a special type of view for Type 2 tables with only the current data. How are they named?

Answer 5

The name of the table with “_Current” appended.

Ex:

Question 6

What table and column would you use to generate a count of patients in SlicerDicer (Patients DM)?

Answer 6

PatientDim . PatientDurableKey because it’s Distinct.

Question 7

What is a durable key?

Answer 7

DurableKey’s identify an entity. Type 2 tables have a DurableKey column which stores the same value for all rows in that table that correspond to the same entity.

This allows for grouping or summarizing data by the entity.

Question 8

T/F All columns in a Type 1 table are type 1

Answer 8

True

Question 9

T/F SELECT DurableKey FROM will only run if the table is Type 2

Answer 9

True

Question 10

T/F In the query SELECT DurableKey FROM the value in the DurableKey column could repeat in multiple rows of the results.

Answer 10

True

Question 11

If the DurableKey identifies an entity, what does the PrimaryKey represent?

Answer 11

The entity at a moment in time.

Question 12

How does data get into Caboodle?

Answer 12

SSIS packages

Question 13

Should you use a TargetKey or a Type 1 fact table to report on dates and times? why?

Answer 13

Type 1 Fact tables are the better source. A TargetKey will hold the date/time that Caboodle found out about it.