Some might say that the ETL should enforce foreign key constraints.

The fact table will have these ID columns as foreign keys, and because the fact table grows daily, reducing the size used for these integer type IDs will help keep the table in check as size can be a problem. You can also use negative values which work well when you have early arriving facts. The int data type will allow 2,000,000+ rows if you use all positive values. Smallint would work for Customer because there will not be more than 32,000 rows. The Category and Subcategory dimensions can use tinyint because there are less than 255 possible rows. This design uses an int data type for the Date dimension key because it uses values like 20170124, 20170125, etc. You could even use Boolean if there was only 2 possible integer values – 0 or 1. There are different uses like bigint, int, smallint and tinyint. Since all the dimension tables have IDs as a primary key, the use of the integer data type needs to be looked at. Figure 3 scales down the model to something simpler.įigure 3 Simpler Internet Sales Data Mart There are a lot of columns that can be very confusing. So, Sales Territory and Geography will get the country along with Group and Region.įigure 2 shows a business database diagram of the full Adventure Works DW with just the fact table for Internet Sales. The Reseller Sales does not have a different customer for this company but Internet Sales does. The Country is not specific, so the question where is the Country located, from Customers or from Sales Territories. Average Sales will be another calculation and is sliced by Country. Products for Sales Line Items will be relate to Subcategory and Category. There is reference to a Category value of Bike and slicing into Subcategories. Sales Line Item Cost is required to compute Gross profit, then Gross Profit Margin. The main numerical value is sales, but there is a calculation for Gross Profit Margin. There will be a Data dimension because of the need for Year, Quarter and Month. Show average sales for subcategories within categories in the USA?Ĭalculate Gross Profit Margin for Internet Sales region by Year, Quarter and Monthįrom these specific inquiries, we can get an idea of the data required.

What happens to profits when you remove the Bike Category? The beginning can be a data mart that might answer the following questions:Ĭompare Sales of Mountain Bikes for years 20 Not the best, but something of value where management will approve and extend other data warehouse projects. The best path from experience is to create the minimum structure that yields a good result. Taking on a complete data warehouse project can be overwhelming. The full version of the Adventure Works DW can be downloaded from CodePlex. This article is going to use a scaled down example of the Adventure Works Data Warehouse. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling.

Comitshared lock in ssas tabular