SQL Server Accelerator for Business Intelligence SSABI

1
SQL Server Accelerator for Business
Intelligence(SSABI)
2
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

3
Understanding the Benefits of the Accelerator for
BI

• Fast Deployment
• Extensibility
• Cost-Effectiveness
• Flexibility
• Reusability
• Prescriptive Guidance

4
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
5
Introducing Components of the Accelerator for BI

• Reference Data Models
• Client Views
• Analytics Builder Workbook

6
Reference Data Models

• Sales and Marketing Analytics
• Retail Analytics
• Additional Reference Data Models

7
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

8
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

9
Defining the Logical Architecture

• Architecture Overview
• Client Views
• Analysis Services Database
• SQL Server Databases
• DTS Packages

10
Analytical Application Architecture Overview
Source Systems
Staging Db
UI
KPIs Cubes
ETL
OLEDB for OLAP
Subject MatterDb
11
Client Views

• Dynamic Configuration
• Automatic Generation
• Optional Usage
• Extensible Content

12
Analysis Services Database

• OLAP Cubes
• Facilitates Slice and Dice
• Very Fast Query Performance
• Predefined Structure
• Supports the Client Views
• Customizable

13
SQL Server Databases Subject Matter

• Fact and Dimension Tables Support Cubes
• Slowly Changing Dimension Support

14
SQL Server Databases - Staging

• Tables Support Fact and Dimension Loads
• Stored Procedures for ETLM

15
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

16
Installing the Accelerator for BI

• Minimum Requirements
• Installation Procedures

17
Minimum Requirements

• 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

• Hardware
• Intel Pentium 166 Mhz
• 256 MB RAM
• 3 GB hard disk
• VGA monitor
• Mouse

18
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

19
Using the Analytics Builder Workbook

• Databases
• Time
• Dimensions
• Levels
• Member Properties
• Virtual Dimensions

• Physical Cubes
• Measures
• Virtual Cubes
• Calculated Members
• Advanced Sheets
• Mappings

20
Databases
Subject Matter Database Name
Analysis Database Name
Staging Db
Cubes
ltDB idgt_Staging
ltDB idgt
ltADB idgt

• Server Selection Local vs. Remote
• Database Naming Rules
• Connectivity Testing

21
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

22
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

23
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

24
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

25
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

26
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 cubes schema is automatically structured to
  remove lower levels

27
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

28
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

29
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

30
Advanced Sheets

• Worksheets for Advanced Functionality
• Named Sets
• Actions
• Calculated Cells
• Validate Syntax Before Adding to Worksheet

31
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