Transcript SQL Server Accelerator for Business Intelligence (SSABI)

SQL Server Accelerator
for
Business Intelligence
(SSABI)
Deploying an Analytical Application


The Benefits

Improved Information-Sharing and Decision-Making

Higher Revenues and Lower Costs
The Challenges

Long Development Cycles

Extensive Customization for Packaged Solutions
Understanding the Benefits of the Accelerator for BI

Fast Deployment

Extensibility

Cost-Effectiveness

Flexibility

Reusability

Prescriptive Guidance
Mapping the Analytical Application Development
Process
Develop Project Plan
Gather Business Requirements
Select Data Model
Select Clients
Customize Data Model
Configure Client Views
Configure Analytics Builder Workbook
Create Analytical Application
Load and Process Data
Evaluate
Deploy
Introducing Components of the Accelerator for BI

Reference Data Models

Client Views

Analytics Builder Workbook
Reference Data Models

Sales and Marketing Analytics

Retail Analytics

Additional Reference Data Models
Client Views


Customizable Templates

Solution-specific KPIs

Report Collections by Business Role
Multiple Client Tools

ProClarity Analytic Platform 4.0

Open API Supported by Many Third-Party Vendors
Introducing the Analytics Builder Workbook


Front End to Analytics Builder

Data Model Design Interface

Hosted by Excel

Intended for a Single User
Design Features

Standard Data Model

One Worksheet per Design Component

Custom Toolbar

Data Entry Validation Routines
Defining the Logical Architecture

Architecture Overview

Client Views

Analysis Services Database

SQL Server Databases

DTS Packages
Analytical Application Architecture Overview
Source
Systems
ETL
Staging DTS
DTS KPI’s
Db
Cubes
Subject Matter
Db
OLEDB for OLAP
UI
Client Views

Dynamic Configuration

Automatic Generation

Optional Usage

Extensible Content
Analysis Services Database


OLAP Cubes

Facilitates Slice and Dice

Very Fast Query Performance
Predefined Structure

Supports the Client Views

Customizable
SQL Server Databases – Subject Matter

Fact and Dimension Tables Support Cubes

Slowly Changing Dimension Support
SQL Server Databases - Staging

Tables Support Fact and Dimension Loads

Stored Procedures for ETLM
DTS Packages



Load Staging Database

Preconfigured Bulk Insert Package

Requires Cleansed and Organized Source Data
Load Subject Matter Database

Management of Inserts, Updates and Deletes

Supports Slowly Changing Dimensions
Process OLAP Database

Management of Updates to Dimensions and Cubes

Implements Monthly Partitioning
Installing the Accelerator for BI

Minimum Requirements

Installation Procedures
Minimum Requirements
Hardware

Intel Pentium 166 Mhz

256 MB RAM

3 GB hard disk

VGA monitor

Mouse
Software

Microsoft Windows 2000, any edition (SP2)
or Microsoft Windows XP

Microsoft SQL Server 2000, Enterprise
Edition or Developer Edition (SP2)

Microsoft SQL Server 2000 Analysis
Services, Enterprise Edition or Developer
Edition (SP2)

Microsoft Excel 2002 (Office XP)

Microsoft Word 2002

Office XP SP1

Windows Scripting Host version 5.6
Installation Procedures


Installing the Software

Use Local Administrator Credentials

Start SQL Server and MSSQLServerOLAPService

Launch the Executable
Configuring Permissions on Folders and File Shares

Set Permissions for SQL Server Service

Set Permissions for Developer Accounts
Using the Analytics Builder Workbook

Databases

Time

Dimensions

Levels

Physical Cubes

Member Properties

Measures

Virtual Dimensions

Virtual Cubes

Calculated Members

Advanced Sheets

Mappings
Databases
Subject Matter Database Name
Staging
Db
<DB id>_Staging
Analysis Database Name
Cubes
<DB id>
<ADB id>

Server Selection – Local vs. Remote

Database Naming Rules

Connectivity Testing
Time

Schema and Member Values Generated Automatically

Primary Key of All Time Level Tables is SmallDateTime

All Time Hierarchies Based on Dim_Time_Day Table
Schema Object Attributes



ID

Short code for the object

Used in creating relational schema names
Name

User-friendly name for the object

Used in creating OLAP schema names
Label

Longer description of the object

Used in the description property
Dimensions

Unique IDs and Names for Dimensions and Hierarchies Required

One Row Per Hierarchy

Standard, Flat or Parent-Child Dimension Structure

Name of “All” Member

Changing Type

Track History

Restate History

Restate History Seldom / Often
Hierarchical Levels

Unique IDs and Names for Levels Required Within a Dimension
Hierarchy

One Row Per Level in Proper Sequence Within Hierarchy


Two Generated Member Properties

Member code – natural key

Member label – description of member
Choose a Based On Level for Alternate Hierarchies That Share
Levels
Virtual Dimensions & Member Properties



Add Member Properties for Filters and Calculations

Member properties are optional

A column for each member property is added to dimension table
Specify a Virtual Dimensions for Enhanced Analysis

Any member property can be designated as a virtual dimension

A table for each virtual dimension is automatically created
Force Referential Integrity in the Relational Database

Foreign Dimension-Hierarchy

Foreign Level
Physical Cubes

Unique IDs and Names for Cubes Required

Specify Automatic Monthly Partitioning

Select Dimension Granularity in Each Cube

By default, granularity is the leaf level of the hierarchy

The granularity can be any level, except the “All” level

The cube’s schema is automatically structured to remove lower
levels
Measures


Unique IDs and Names for Measures Required

This prevents naming conflicts in virtual cubes

This applies to both real and calculated measures
Specify Data type and Format



“Count” measures are created only in analysis database
“DegenerateDim” measures are created only in the
relational databases
Specify if Measure will be Visible in Cube
Virtual Cubes

Unique IDs and Names for Virtual Cubes Required

Inheritance



All dimensions of component cubes
All measures of components cubes
- Physical
- Calculated
If a level is disabled in each of the component cubes, the level is
hidden in the virtual cube
Calculated Members

Unique IDs and Names for Calculated Members Required

Apply a Calculated Member to a Specific Physical Cube, or a Virtual
Cube

MDX is Not Validated in the Worksheet
Advanced Sheets


Worksheets for Advanced Functionality

Named Sets

Actions

Calculated Cells
Validate Syntax Before Adding to Worksheet
Mappings


You use this sheet to:

Map Default Values to Renamed Components

Remove Default Values for Deleted Components
Analytics Builder uses this sheet to:

Build XML Mapping for Client Generators