An Architectural Review Of Integrating MicroStrategy With SAP BW

(1)

An Architectural Review Of Integrating

MicroStrategy With SAP BW

Manish Jindal

MicroStrategy Principal

HCL

(2)

Objectives

 To understand how MicroStrategy integrates with SAP BW

 Discuss various Design Options

 Industry driven Best practices

 Case Study: CPG Customer

 A journey with the challenges faced & resolutions

 Product Enhancements

 Recommendations

(3)

3

Product Architecture & Scope

Systems Manager MicroStrategy

Architect MicroStrategy

Desktop

Web Browsers

Web Services Portals

Design &

Administration

MicroStrategy

Intelligence Server

MicroStrategy OLAP Services MicroStrategy

Report Services

Command Manager

Integrity Manager

Enterprise Manager Object Manager

MicroStrategy Clustering Option

MicroStrategy Office Microsoft

Office

MicroStrategy Mobile iOS/Android

MicroStrategy Narrowcast Server

E-Mail File Printers Servers

MicroStrategy SDK

Relational Data Warehouses

 Oracle

 DB2

 SQL Server

 Sybase

 Teradata

 Informix

 NonStop

 Netezza

Cube Databases

SAP BW

Hyperion Essbase

Microsoft AS Operational DBs and

Non-Relational Data (Excel, CSV, ERP)

MicroStrategy Web

1 Courtesy: An Introduction To Business Intelligence With MicroStrategy

(4)

Evolution of MicroStrategy & SAP BW Integration

 With version 8 MicroStrategy released a new MDX Generation

Engine, accessing SAP BW using SAP’s OLAP BAPI’s

 MicroStrategy Intelligent Server dynamically generates MDX to

execute against SAP BW

 Available with “Multisource” as a feature, enabling data from

SAP BW cube(s) and from non-SAP sources together

(5)

5

ROLAP-MOLAP Design Options

 Option1 – Reporting on Multidimensional Database only

 Option 2 – Multisource, Reporting on both Relational &

Multidimensional databases

 Option 3 – Moving Data from Multidimensional to Relational

Database for Reporting

(6)

Option 1: MOLAP Only

SAP BW MSTR Metadata Repository

MicroStrategy Reporting & Analytics

(Master Data, Aggregates, Transactions)

• Pros

– Quick deployment of Reports and Dashboards

– Supports Drill “anywhere”

• Cons

– Large metadata size due to managed objects concept – No multi-pass

– No Global Hierarchical Prompts – Doesn’t Global Security Filter – Cannot create Global

Prompts/Filters in MSTR

(7)

7

Option 2: Multisource (MOLAP + ROLAP)

SAP BW ETL

Reporting & Analytics Repository (Metadata, Partial Master Data

(Partial Master Data, Aggregates,

Transactions)

• Pros

– Global Hierarchical Prompts can be can be created for select set

– Supports Global Security via Security Filters

– Ability to create Global Prompts/Filters in MSTR – Use Master Data Query Cube – Transactional data comes from

SAP BW

• Cons

– Performance degradation with large data set

– Requires maintaining multiple report caches for various prompt conditions – Extensive Memory Utilization

(8)

Option 3: From MOLAP to ROLAP

SAP BW ETL

Reporting & Analytics Repository

(MSTR Metadata, Master Data, Aggregates, Transactions)

• Pros

– Increased Reporting & Analytics Performance

– Object Level Security in MSTR

– Nested Hierarchical prompts/filters – Ability to use Global Security filters – Increased Mobile Intelligence

Performance

• Cons

– Increase in Batch Window to load data

– Increase in Maintenance process &

cost

– Delays in report availability due to iCube publishing cycle

(9)

9

Case Study

Customer

A leading North American Consumer Package Good company with a

distribution network primarily in USA, Canada & Mexico. It has over 45

products and collecting data from over 1600 Bottlers. Reporting

requirements to review data from By Chain & By Month to By SKU, By Store

& By Day (BS-BS-BD)

Challenges

 Transition of old fashioned legacy reports to self-service & web based

MicroStrategy based ad hoc reports on top of SAP BW as the database

 With BS-BS-BD, Big Data challenges to implement reports on multiple

SAP BW Cubes with up to 1.2 billion records

 Reporting based on daily and monthly data feeds asking for a dual master

data for reporting purposes

Technologies Involved

MicroStrategy 9.3.x, MicroStrategy Cloud 1.0, SAP BW, Informatica, Oracle

11g

(10)

Infrastructure: Cloud Environment

(11)

11

Challenge: Hierarchical Prompts & Global Security

Requirement: Hierarchical prompts on the report

 SAP BW (MOLAP) flattens the hierarchical relationships between dimensions

 In order to bring the hierarchical relationship between attributes it was required to maintain in a relational database

 In Datamart, hierarchical relationship between attributes were defined as per the Logical Data Model

 Using MicroStrategy Architect attributes were pointed to their respective lookup tables to inherit the parent-child relationship

 Using MDX Warehouse Catalog, MDX managed objects and Schema objects (Attributes) were mapped to each other

 Arranged attributes in nested prompts to achieve the requirement Requirement: Global Security outside SAP BW

 Required data level security based on “System” dimension on SAP BW

 Mapped “System” from SAP BW with “System” attribute in MicroStrategy

 Defined a security filter on “System” to achieve the security requirement

Country

System

Zone

Region

Bottler

(12)

Contd. Hierarchical Prompts & Global Security

SAP BW (Query Cube)

Datamart LookUps

Global Attributes

MDX Warehouse Catalog

(13)

13

Challenge: Dual (Time-based) Master Data

Requirement: SAP BW Close Process for hierarchical dimensions for two different time instances

Preclose Q-Cube Closed Q-Cube

Bottler Bottler CL

Preclose Reports Closed Reports

 SAP BW system was designed to support two time instances – Preclose (reconciliation

stage for data) and Closed (post reconciliation) Cubes

 Lack of time dimension, snapshots of Master Data were maintained by using LookUps for Preclose and Materialized Views (MVs) based master data for reporting needs

 Separate database connections (DSNs) were created maintain distinction between the

attributes

 Different (Preclose vs. Closed) sets of attributes were created in MicroStrategy to support the requirement

 Appropriate attributes were used for Preclose

& Closed reports, respectively

Lookups MVs

(14)

Challenge: Change in MDX Object Definition

M DX Re p o rt De v e lo p m e n t Cy cl e

(15)

15

Contd. Change in MDX Object Definition

Ch a n g e Re q u e st De v e lo p m e n t

(16)

Contd. Change in MDX Object Definition

Re -d e v e lo p m e n t o f Re p o rts

(17)

17

Challenge: Ad hoc Requirements & Dataset Size

Requirement: Report allow users to drag & drop more than 30 attributes for drilling, in-field analytics and ad hoc functionality

 High count of attributes make the dataset really big and fetch data for over 500,000 rows x 22 columns (attributes & metrics)

 iCubes don’t support nested hierarchical prompts

 Dataset layer is a MUST to have hierarchical prompts

Why Dataset?

Prompts

Consequences: Performance degradation on reports due to big dataset sizes

Resolution: Converted all report prompts to filter selectors on the report to remove the dataset layer and hit the iCube directly.

Performance from 2 minutes to 2-3 seconds

Filters

(18)

Challenge: BEx Query Performance Issue

Issue: SQL time-out errors from SAP BW

for large queries & session time-outs

OpenHub

Fact Table Report

 Executing large reports/queries may

results in session time-outs

 Loaded data into SAP OpenHub

tables

 Move data from SAP OpenHub table

to Relational Database based FACT

Table using an ETL tool

 Created MicroStrategy Reports by

moving transactional data into

Relational database and building

iCubes

(19)

19

Challenge: Multi-panel for Time-frame Metrics

Requirement: To allow users to select Unit of

Measure (UOM) as an attribute selector and change data based upon user selections

 Due to high volume of data and number of records, SAP BW provided all UOMs as separate Key

Figures (aka Metrics)

 Reports were designed to use Panel Stack

arrangement to display each metric on a separate panel

 Panel Stack selectors were used to give an affect like Attribute selector

(20)

Contd. Multi-panel for Time-frame Metrics

(21)

21

9.3-9.3.1 Product Enhancements

Requirement: Create Custom Group on MDX Report using combination of 2 or more attributes in multi-source (Design Option 2)

 Required building a custom group on 2 or more ROLAP attributes and MOLAP metrics

 MSTR 9.3.1 wasn’t allowing to create a Custom Group with a combination of 2 or more attributes; “Index out of range … “ error

 MSTR Technology Product team released a Hot fix to resolve the issue and requirement was successfully implemented

Requirement: Change in MDX Managed Object definition

 Change in definition for MDX Managed Object resulted in Warehouse catalog mappings

 Recommendation was to Unmap ROLAP Attribute and MDX Managed Object before changing the definition

 MSTR 9.3.1 didn’t have the feature to unmap

 A temporary workaround was to create a “Dummy” attribute and map the old definition with it and create a new ROLAP attribute for mapping

 MSTR Technology Product team released a Hot fix by unveiling “Unmap: feature and resolve the issue

(22)

Contd. 9.3-9.3.1 Product Enhancements

Issue: Unable to migrate Multisource Objects from MSTR DEV to QA environment

 Migrating MDX based Multisource reports was not picking up the MDX (Q-Cube) from DEV to QA environment

 The issue was found that MSTR 9.3.1 was not binding MDX (Q-Cube) in the migration package along with other report objects

 As a work-around, we followed a dual step migration process:

 Package & migrate all multisource objects (report, document, etc.) from DEV to QA

 Package & migrate MDX (Q-Cube)

 Hot fix patch was applied to 9.3.1 to allow migration without creating two packages Issue: 9.3 Report Prompt applied as Filter Selector on a multisource report

 Report with Month and Year prompt was not only passing the prompt condition to MDX Cube, but also filtering the result sets to display data only for the passed Month and Year combination

 Resulted in same month data in time-frame (MTD, YTD, 12RM etc.) metrics

 Was a known issue in MSTR v 9.3; upgrade to 9.3.1 to resolve the issue

(23)

23

Case Study Conclusion

Platform Consolidation

 Converted legacy reports into dynamic and self-service reports using

MicroStrategy

 Hybrid approach to leverage all design options: MOLAP, MOLAP & ROLAP,

MOLAP to ROLAP

Accomplishments

 Implementing 25+ Reports and Dashboards with a mix of MDX Reports,

Multisource Reports, iCube Reports, Visual Insights & Dashboards

 Availability of iCubes in Cloud to access the data practically from anywhere in

the world

 Single path development for both web and mobile users to save dual

development and improve the efficiency of customer

Benefits

 Easier and in-the-Field Decision Making

 Quick and easy access to key KPIs and Dashboards to CXOs, Finance,

Marketing & Promotions, and Field Sales operations

(24)

Recommendations

Design Advantages Disadvantages

MDX Report • High performance like BEx

• More efficient way of drill “anywhere”

without loosing performance

• No need to “fill-in” datasets or cache reports

• No Global Security Filters

• Requires multiple versions of same report for metrics for different

dimensions (Time-frames, UOMs etc.)

Multisource Report • Global Security Filters

• Nested Hierarchical Prompts

• Cached documents

• Slow response due to caching requirements before report comes

• Requires high number of document caches

• High usage of IServer memory

• High maintenance in refreshing caches

BEx Query based iCube Report

• Higher performance than Multisource and MDX Reports

• SAP BW time-out restrictions

• Not a viable solution for large volume of data (~500 millions records)

• Rendering issues due to high number of graphical objects SAP BW OpenHub

based iCube Report

• Highest performance due to availability of complete dataset in Relational database

• Data redundancy

• Increase in maintenance cost to built iCubes

(25)

25