Updating fact tables

We’re making improvements to the nutrition facts table and list of ingredients on food labels based on feedback from Canadians and stakeholders.The food industry has a transition period of 5 years to make these changes.You need a way to add those records to the existing cube in the few minutes it takes to process only the new records.To make matters more complicated, let's say you need to add daily sales records to the cube automatically in a batch program every night.The capacity for an OLAP cube to store and organize colossal quantities of data is the attribute that makes OLAP more valuable for reporting than a standard relational database.Queries that take 20 minutes to return results in a relational database can return the same results in less than 1 minute when the system uses previously calculated aggregate tables.

If you need to perform data audit in existing databases, use ALTER TABLE to extend non-temporal tables to become system-versioned.

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.

So in this example we need master tables of State, City, Property and Property Type and in a middle our fact table will be "Customer" since customer will buy a property. Step 1 : In this step create data destination tables for dimensions and fact we will create 4 dim tables and 1 fact table to load data in datawarehouse coming from source CSV files.

Note : Datawarehouse is SQL SERVER So let's create 4 dimension tables or master tables - State, City, Property and Property Type in our SQL Server Management Studio as shown in below image.