Why use dimensional modeling design technique in building a DW/BI system?

1)      To capture business process requirements as accurately as possible

2)      To present information to users as simply as possible

3)      To return query results to the users as quickly as possible

***My DW Bible and favorite reference materials during my POC – RALPH KIMBALL BOOKS, they are definitely worth the investment***

1.       The Data Warehouse Lifecycle Toolkit 2^nd Edition (2008)

2.       The Data WarehouseETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data (2004)

3.       The Microsoft Data Warehouse Toolkit (2006)

***Download sample interview summaries and a cool dimensional modeling spreadsheet that has built-in macros which generates SQL scripts that create the model as database tables in SQL Server - http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm

STEP-BY-STEP PROCESS IN DIMENSIONAL MODELING:

1)      Gathering and documenting business requirements

a.       Conduct Business and IT Interviews

b.      Write up Summaries and Get Feedback from Users and IT

c.       Build initial bus matrix – a bus matrix maps the business process to the entities/objects that participates in the business process

d.       

2)      Dimensional Modeling Process – a “highly” iterative and dynamic process, had 3 iterations before I got end-user sign-off  J

a.       Data Profiling and Research

b.      High-Level Dimensional Model Design Session – “The 4-step process”

                                                               i.       Identify the business process – listen for “action” words i.e. purchasing, shipping, invoicing, paying, etc…

                                                             ii.      (FACT TABLE) Declare the grain of the business process – describing and specifying exactly what an individual transaction is  i.e. a line item on a customer’s receipt as captured by a scanner device

*** For the Adventure Works Cycle Sample Database – the grain of "orders" business process is clearly at the order line item level

                                                            iii.      (DIMENSION) Identify the business entities for each business process – listen for “nouns” i.e. product, customer, date, etc… Look for hidden foreign key relationships, normalized

                                                           iv.      (MEASURES) Identify the facts or measures generated by the business process. Should answer the question “What are we measuring or analyzing?” All candidate facts in a design must be true to the grain defined in step 2, otherwise it should be in a separate fact table.

DIMENSIONAL MODELING CONCEPTS

·         Star Schema – dimensional model stored in relational database

·         Cubes – dimensional model stored in multidimensional OLAP structure

·         Surrogate Keys – are meaningless integers that are assigned sequentially while populating the dimension. Why use surrogate keys – (1) PERFORMANCE (compared to bulky alphanumeric “natural”/operational keys, (2) ISOLATION - due to limited retention period, operational keys maybe reused; or different data sources may use different “natural” keys for the same entity; or some transactions may have NULL natural key, and lastly (3) To track changes in dimension attribute values by creating a new dimension row representing the new attribute, using the next available surrogate key as its primary key

·         Degenerate Dimension Key – is the actual transaction number, sitting in the fact table without joining to any dimension. It is the “glue” that holds the line item rows together i.e. order number, invoice number or ticket number. There is no point in creating a corresponding dimension table because it would contain nothing but a transaction number anyway.

·         Slowly Changing Dimension – dimension table attributes seldom changes, and are more stable and static as compared to fact table values. However there are some instances where there will be some changes, and the dimensional model must be able to accommodate these changes. There are 3 fundamental techniques for handling SCDs

o   Type 1: Overwrite the Dimension Attribute

o   Type 2: Add a New Dimension Row

o   Type 3: Add a New Dimension Attribute (i.e. adding a “current” or “not current” column indicator)

·         Role-Playing Dimensions – when the same physical table plays distinct logical roles in a dimensional model, the most common example is the DATE dimension (order date, ship date, etc…) Role-playing can also occur in a variety of dimensions aside from the date dimension i.e. CUSTOMER (ship to, bill to and sold to); FACILITY or PORT (origin, destination); PROVIDER (referring, performing); REPRESENTATIVE (seller or servicer)

·         Junk Dimension – flags or indicators often found in transaction tables; Study the flags and text attributes carefully and you can place them in one or more JUNK dimension, rather than adding more clutter to the fact table i.e. invoice indicator, payment terms, order mode, ship mode, etc…

·         Bridge Table – technique to solve "many-valued" dimension where in each fact measurement can be associated with multiple dimension values...an example in kimball’s book is when a patient have multiple diagnosis. recommended solution is to create a "Diagnosis" Bridge Table which has the fact table key and diagnosis key (which is linked to the diagnosis dimension table), and a weighing factor (which sums to one for each diagnosis group)

·         Snowflaking and Outriggers – snow flaking is generally not recommended for dimensional models because of the resulting complexity and performance, but there are some situations when it is appropriate or advantageous…this is when we call it "outriggering". Example is a date dimension snowflaked off a primary dimension, such as customer’s date of birth or the product’s introduction date. This only make sense if business needs to filter and group the data by non-calendar date attributes otherwise just treat the date attribute as a standard date format field.

1) Why use Microsoft SSIS Connector by Attunity --- performance, support for 64-bit environment and oracle numeric type (precision)

***Sample Codes and Best Practices for my 4 POC Scenarios***

POC Scenario 1: Consuming and Exposing Web Services

Using Web Services in Biztalk

Sample Design Patterns for Consuming and Exposing Web Services from an Orchestration

POC Scenario 2: SQL Server Adapter

Sample Loan Application Using SQL Adapter

Best Practices for the SQL Adapter

POC Scenario 3: Windows Sharepoint Services Adapter

WSS Adapter Walkthrough

POC Scenario 4: SAP Adapter

Biztalk Adapter Sample Packs

SAP Adapter Tutorials

FOR FURTHER READING: (Special thanks to Quoc Bui, APAC Biztalk Ranger, got the following links from his Architecture Design Review Paper)

The Microsoft BizTalk Server Operations Guide contains best practices and guidelines on how configuring, managing, monitoring and maintaining an enterprise level BizTalk Server solution for best performance, stability, high-availability and resilience.

The Microsoft BizTalk Server Performance Optimization Guide provides prescriptive guidance on the best practices and techniques that should be followed to optimize BizTalk Server performance and introduces the methodology adopted by the BizTalk Customer Advisory Team to conduct a successful performance lab.

The BizTalk Server Database Optimization article on MSDN  details techniques for optimizing the infrastructure of a BizTalk Server system with the final goal to increase the throughput and reduce the latency of the system, maximizing return on invested hardware and software capital. 

The BizTalk Server 2006 R2 Hyper-V Guide provides guidance for using Microsoft BizTalk Server 2006 R2 with Microsoft Windows Server 2008 Hyper-V. The emphasis of this document is on BizTalk Server, but the performance evaluation methods and performance testing scenarios are useful for analyzing the performance of virtualized server applications in general. This guidance will be of interest to both the IT Pro and Developer communities.

The Developing Integration Solutions using BizTalk Server 2006 and Team Foundation Server document provides developers with techniques for designing, developing, and deploying solutions within Microsoft® BizTalk® Server 2006 (R2) in conjunction with Team Foundation Server. This paper was based on Developing Integration Solutions with BizTalk Server and updated for BizTalk Server 2006 (R2) and Team Foundation Server.

The Using the Windows Communication Foundation (WCF) Adapters in BizTalk Server whitepaper describes the use of the WCF adapters in BizTalk Server.

The Consuming and Hosting WCF Services with Custom Bindings in BizTalk Server whitepaper provides an in-depth explanation on how to use the BizTalk Server WCF adapters for hosting and consuming Windows Communication Foundation (WCF) services with custom bindings. The paper compiles a series of lessons learned based on a real-world implementation of integrating a custom WCF binding with the WCF adapters. Furthermore, key industrial infrastructure concerns such as transaction management and security are discussed in the context of integrating custom WCF bindings and behaviors with the BizTalk Messaging Engine. Also emphasized are a few pragmatic paradigms such as the use of dynamic ports in consuming WCF services. Finally, some key integration challenges are discussed to streamline the correct use of the adapters for solving complex business problems.