Updating fact tables
If you have identified information that needs data audit, create database tables as temporal system-versioned.
The following simple example illustrates a scenario with Employee information in hypothetical HR database: CREATE TABLE Employee ( [Employee ID] int NOT NULL PRIMARY KEY CLUSTERED , [Name] nvarchar(100) NOT NULL , [Position] varchar(100) NOT NULL , [Department] varchar(100) NOT NULL , [Address] nvarchar(1024) NOT NULL , [Annual Salary] decimal (10,2) NOT NULL , [Valid From] datetime2 (2) GENERATED ALWAYS AS ROW START , [Valid To] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (Valid From, Valid To) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.
Temporal Tables are generally useful in scenarios that require tracking history of data changes.
We recommend you to consider Temporal Tables in the following use cases for major productivity benefits.
You need to incrementally and automatically update the cubes.
This capability is critical to the successful implementation of an enterprise-level cube, yet it is one of the most under-documented and most easily misunderstood processes of OLAP Services.
The right-hand portion of the diagram visualizes row versions on time axis and what are the rows you select with different types of querying on temporal table with or without SYSTEM_TIME clause.Here we first we will load our 4 dimension table and then we will load our fact table.Star Schema where all the dimension tables are directly connected to fact table. Real Time example : We will take up real time example of customer buying a property in #India.There is explanatory text around the outside of the table with arrows pointing to the new changes within the table.Following is a description of the original Nutrition Facts table.
You need a way to add those records to the existing cube in the few minutes it takes to process only the new records.