SQL Server

SQL Server 2008 Analysis Services : An Analytics Design Methodology

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
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
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS