SQL Server

SQL Server 2008 Analysis Services : An Analytics Design Methodology

12/13/2010 8:50:56 AM
A data warehouse can be built from the top down or from the bottom up. To build a top-down warehouse, you need to form a complete picture or logical data model for the entire organization (or all the subsystems within the scope of the project, such as all financial systems). In contrast, building a warehouse from the bottom up takes a much more departmental or specific business-area focus (for example, a sales order system only). This breaks the task of modeling the data into more manageable chunks. Such a departmental approach produces data marts that are potentially subsets of the overall data warehouse. The bottom-up approach can simplify implementation. It helps get departmental or business-area information to the people who need it, makes it easier to protect sensitive data, and results in better query response times because data marts deal with less data than a voluminous transactional system. The potential risk in the data mart approach is that disparity in data mart implementation can result in a logically disjointed enterprise data warehouse if efforts aren’t carefully coordinated across the organization.

Before you embark on an OLAP database creation effort, the time you spend understanding the underlying requirements is the best time you can give your effort. If scope is set correctly, you will be able to achieve an industrial-strength OLAP design without much difficulty. First, you need to take care of some groundwork:

Carefully assess the scope of what you want to represent in the BI environment. Start small, as the bottom-up approach suggests. For instance, just tackle the sales data facts.

Coordinate your efforts with other related BI efforts. Let people know that you are carving out a specific subject area or departmental data and, when you finish, publish your design to everyone.

Seek out any shared dimensions that might have already been created for other cubes. You want to leverage these as much as possible for the sake of data consistency and nonredundant processing.

Understand your data sources. The OLAP cube you create will be only as good as the data you put into it. It’s best to understand the dirty data issues of what you are about to touch long before you try to build an OLAP cube with it.

An Analytics Mini-Methodology

To successfully build OLAP solutions, you are advised to carefully assess the requirements of your end users in as detailed fashion as is possible. A mini-methodology that focuses on the essential usages and characteristics of an Analytic solution can prove invaluable. The following sections outline a solid approach to nailing down your BI requirements and yielding optimal OLAP designs that solve your end users’ needs.

Assumption: You are building a business area–focused OLAP cube.

Requirements Phase
Identify the processing requirements for this DSS. What analysis do you need to do? Are trend reporting, forecasting, and so on necessary? These can often be represented in use case form (via UML).

  1. Ask each user what business decision questions he or she needs to have answered.

  2. Ask each user how often he or she needs these questions answered and exactly when the questions must be answered.

  3. Ask each user how current the data must be to get accurate answers. (This speaks to data latency.)

Identify the data needed to fulfill these requirements. What data must be touched to provide answers? The best way to capture this type of information is a logical data model. Even a rough model is better than none at all. This is the point where you focus on the facts that need to be analyzed.

Identify all possible hierarchies and level representations (that is, aggregations). This is how the data is used. Most users are likely to tell you that they want to see product data in the product hierarchy structure that has already been set up (for example, product family, product groups).

Identify the time hierarchies that the users need. Because time is usually implicit, it just needs to be clarified in terms of levels of aggregation (for example, years, quarters, months, weeks, days) and whether it needs to be fiscal versus Gregorian calendar, both, or something else.

Understand the data that each user can view from a security point of view.

Design Phase
Analyze which data sources are needed to fulfill the requirements. See whether dimensions or OLAP cubes that already exist can be shared.

Understand what data transformations need to be done to the source data to provide it to the OLAP world. This might include pre-aggregation, reformatting, data integrity verifications, and so on.

Translate these requirements into an OLAP model design:

  1. Translate to MOLAP if your data sources are not going to be leveraged at all and you will be taking full advantage of OLAP storage.

  2. Translate to ROLAP if you are going to leverage an existing relational design and storage.

  3. Translate to HOLAP if you are going to partially utilize the source data storage and partially utilize OLAP storage. This is the most frequently used approach.

Construction Phase
Implement data extraction, transformation, and loading (ETL) logic (via T-SQL, SSIS, or other methods).

Create the data sources to be used.

Create the dimensions.

Create the cube.

Select data measures (that is, the data facts) for the cube.

Design the storage and aggregations.

Process the cube. This brings the data into the OLAP environment.

Verify data integrity.

Implementation Phase
Define the security roles in the cube.

Train the user to use the system.

Process the data into the OLAP environment (from production data sources).

Verify data integrity.

Allow users to use the OLAP cube.

Maintenance Phase
Evaluate access optimization in the OLAP cube via usage analysis.

Do data mining discovery, if desired.

Make schema changes/enhancements, as necessary.

Other -----------------
- SQL Azure : Other Considerations
- SQL Azure : Sample Design - Application SLA Monitoring
- SQL Azure : Combining Patterns
- SQL Server 2008 Analysis Services : Understanding the SSAS Environment Wizards (part 2)
- SQL Server 2008 Analysis Services : Understanding the SSAS Environment Wizards (part 1)
- SQL Server 2008 Analysis Services : Understanding SSAS and OLAP
- SQL Azure : Design Patterns (part 3)
- SQL Azure : Design Patterns (part 2) - Sharding
- SQL Azure : Design Patterns (part 1)
- SQL Azure : Design Factors (part 2)
- SQL Azure : Design Factors (part 1)
- Limitations in SQL Azure
- SQL Server 2008 : Performance Data Collection (part 2)
- SQL Server 2008 : Performance Data Collection (part 1)
- SQL Server 2008 : Performance Tuning - Partitioning
- SQL Server 2008 : Guide to the DYNAMIC Management Views (DMVs)
- SQL Server 2008 : Managing Security - Service Accounts and Permissions
- SQL Server 2008 : Managing Security - Security and SQL Agent
- SQL Server 2008 : Implementing Transactions - Transaction Traps
- SQL Server 2008 : Implementing Transactions - Deadlocking
Most View
- Installing Windows Server 2012 and Server Core : Upgrading to Windows Server 2012
- Windows Server 2008 : Use the Command-Line Server Manager (ServerManagerCmd.exe)
- Building Android Apps : Animation - Adding the Date Panel
- SQL Server 2008: Security and User Administration - Managing Securables
- Windows Phone 7 : Saving Pictures to the Web
- Active Directory Domain Services 2008: Enable the Directory Service Access Auditing Subcategory
- Use the Exchange 2007 Toolbox to Troubleshoot
- Managing Windows Server 2012 Storage and File Systems : Storage Management (part 9) - Managing MBR disk partitions on basic disks - Formatting a partition, logical drive, or volume, Configuring drive
- SharePoint 2010 : Use Wiki Syntax to Link to Existing Content and Create Pages
- Microsoft Dynamic GP 2010 : Payables Management (part 3) - Purchasing E-mail setup, Vendors
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS