Intelligent cubes in Microstrategy

Intelligent Cubes:

Topics:

1. Intelligent cubes
2. Intelligent cubes Vs Cache
3. Manage and Monitoring cubes
4. Cube Size and Memory usage
5. Accessing Intelligent cubes
6. Dynamic sourcing
7. Intelligent cube & Security filters
8. Intelligent cubes & Multi-tenant Architecture
9. Intelligent cubes & Level Metrics

Intelligent cube is an OLAP feature introduced in Microstrategy 9 version.

 Intelligent cube is a pre aggregated set of data stored in intelligent server memory and it will give very quick access to the data without hitting the data warehouse.

It is very simple to create an intelligent cube, it is same like a report creation.

When we create and publish intelligent cube, it stores all the data in intelligent server memory as well as a copy in file system (secondary storage area -Hard disk). Whenever it reboots, it loads the data to memory from the file

·         Stores data in intelligent server memory and in disk

We have two ways to access the intelligent cube

1.       View reports

2.       Dynamic sourcing

Intelligent Cubes Vs Cache:

Cache:

When you run the report then that single report result stored in a cache file and whenever if you run the same report then it looks for the cache with respect to the report objects and filter condition and then fetches data from the cache file and shows the result. But when you run the subset of that cached report then it won't fetches values from the cache rather it will re execute the report against the DW and create a new cache file.

Ex: You have quarter wise report for year 2012 and respective cache file has been created when we execute the report for the first time.

If you try to run the report for a particular quarter (2012_Q1) in a year 2012 then it will not hit the cache file even though our expected data is available in the cache file

Intelligent cube:

Basically intelligent cube is a report stores a set of data in I server memory as well as in a file system

Then you can create any subset of reports (view reports) from the cube or any report which is equivalent or subset of intelligent cube then it will use the intelligent cube and fetches the data quickly

Create intelligent cube:

1.       New - Intelligent cube - Empty Intelligent Cube

2.       Add all necessary attributes, metrics and filters  - Report creation window  (Refer the Limitations)

3.       Save

Convert Report to Intelligent Cube:

1.       Create Report  or use Existing reports  (Refer the limitations)

2.       Data - Intelligent cube Options - Convert to Intelligent cube

3.       Save

Limitation:

1.       We can't add any prompts in the cube (Normal Prompts, Object prompts etc)

2.       View Filters are not allowed in the cube

3.       Derived Metrics are not allowed in the cube

Configure Intelligent cubes:

Project Level:

1.       File Directory - Where to store the intelligent cube data - Secondary disk space

2.       Maximum RAM Usage : Allocate Maximum Memory usage for Intelligent cube

3.       Maximum number of cubes : Number of cubes supported in project level

4.       Maximum % growth due to index : We have option to apply index to intelligent cubes and in that case it will increase the size of the intelligent cube and we can control that from here

5.       Multi-Tenancy support - Create intelligent cube by connection mapping

6.       Load Intelligent cube on start-up - I server restart will load all Intelligent cubes in the memory

7.       Load Intelligent cube into I server memory upon publish  - This will automatically load the data in the memory when we publish the intelligent cube otherwise it will have data only in the hard disk and when we run the report on top of the Intelligent cube then load the data into memory

8.       Enable Dynamic sourcing - This is allow the report to select the respective cube dynamically

Cube Level:

We can do few of this configurations in Cube Level :  Data - Configure Intelligent Cube

Publish Intelligent Cube:

Creating Intelligent cube will only create the definition in the metadata, It will not add the data in either Intelligent server memory or in physical memory

So we have to publish the Intelligent cube to create file in the physical memory.

Publish the intelligent cube is very simple, Run the intelligent cube

1.       Without publishing the Intelligent cube, we can't use the intelligent cube

2.       Publishing Intelligent cube will create a file in a physical memory

a.       It is not necessary that publishing intelligent cube should load the data in the memory

b.      It is optional configuration

3.       We can publish the intelligent cube with respect to data connection

a.       For each data connection, we need to publish the cube separately

b.      Each data connection it will create a separate file in the physical memory

Load the data in the Memory

1.       Load data in the memory while publishing

2.       Load data in the memory while running reports on top of the Intelligent cube

Load data in the memory while publishing:

This is the default configuration, as per this configuration, it will create a file in physical memory and load the data in I server memory while publishing (Run \Refresh the Cube)

Load data in the memory while running reports on top of the Intelligent cube

In this case while publishing the cube, it will only create file in the physical memory and it won't load the data in the memory. When you run any report on top of this cube then it will load the data first in the memory and then access the data.

Refresh Intelligent cube:

We have two options to refresh the intelligent cube

1.       Full Refresh \Complete refresh

2.       Incremental refresh

Full Refresh:

Re run the cube completely against the data warehouse.

It delete all the data stored in the cube file and then re run the complete cube against the DW , fetch the data and store in the file

Incremental Refresh:

In this case, it no need run the complete cube against the DW each time when we do cube refresh

Here you need to define the incremental condition. It will fetch data only based on the incremental condition and then compare the data with your existing cube data

You have four different options to tell the Intelligent server what to do after comparing the data

1) Update - Insert new rows from the report and replace any overlapping data.

 This would be the most common method of Incremental Refresh.  If you've got a cube with daily data for the last year, you may choose to schedule your Incremental Refresh to load the last 3 days of data each day.  This way you get the new day, and potentially a little overlap in case there were ETL issues or source system changes you didn't previously catch.

2) Insert - Only insert non-overlapping rows from the data.

This would be used if you were certain that you didn't need to change data and want to ignore any potential overlaps.

3) Delete - Remove overlapping rows from the data.

This would be used to trim your cube to prevent growth past what you're interested in.  If you've got 10yrs of data in your warehouse, you may only want to load a rolling 3yrs of data in your cube.  You could do that rolling at the month level, so once a month you'd delete the last month from 3yrs ago.  Defining a report that returns that data and running in Delete mode would remove those rows from the Cube to give you a manageable size.

4) Update Only - Only overwrite overlapping data from the report.

This would probably only be used for patching data, say from an ETL error or source system fix.  If data was retroactively changed, you could update just that portion without having to reprocess the entire cube.

 Schedule Cube Refresh:

We have schedule the cube refresh. This is normal concept of schedule and subscription

1.       Time based

2.       Event based

Managing and Monitoring cubes:

We can manage and monitor cubes through Cube monitor

Monitoring cubes:

Through cube monitor, we can find below informations about our cube

1.       Status

2.       Size

3.       File Path

4.       DB Connection

5.       Hit Count

6.       File name

7.       Owner

8.       Project

9.       Last update time

10.   Creation time

11.  Data Language

Managing Cubes:

Right click the cube from cube monitor and do the respective action items

Load : Moves intelligent cube data from secondary memory to I server Memory

Unload :  Remove Intelligent cube data from the memory and still the intelligent cube is active

Saves to Disk : Saves the updated information in the memory to disk

Update  : Re executes and publish the intelligent cube

Deactivate : Removes Intelligent cube data from I server memory and intelligent cube will be inactive and cannot be accessible by the reports

Activate: Activate the deactivated Intelligent cube

Cube Size and Memory Usage:

1.       Cube Size is depends on attributes, Metrics and attribute forms added in the cube

2.       In order to publish the cube, we need 2.5 times of memory with respect to cube size

a.       Ex: To publish 1GB Cube, we need 2.5 GB of memory

b.      This is only for the publish operation

3.       After Publish, cube will use the same memory as its size

Best Practices:

1.       Use numeric Columns for Attribute ID Forms

2.       Add only required attribute forms in the report

3.       Avoid compound and smart metrics, rather achieve same as a derived metric in the report

 Accessing Intelligent cube\Using Intelligent Cube:

We can access or use intelligent cubes in two ways

1.         View Reports

2.         Dynamic Sourcing

View Reports:

View reports are reports created on a particular Intelligent cube.

Right Click intelligent cube -> Create report

It can only point to that particular intelligent cube and it can only use the objects available in that intelligent cube. We can't add any outside objects to this view reports.

Advantages:

1.       View report works fine with Fact Extensions

2.       View report works fine with Pass through functions

3.       View report works fine with level and conditional metrics

Limitation:

1.       We can't create report filters in the view report, we can only use view filters

2.       Very Sensitive Security filters on Compound metrics

Dynamic Sourcing:

When you create and run any report, it will dynamically search for any available intelligent cube which satisfies this reporting scenario and hit that intelligent cube.  if it doesn't finds any intelligent cube which satisfy this reporting scenario then it will hit the DW and fetches the data

·         We have to enable dynamic sourcing

·         Few Related VLDB settings are available - We can enable this to get more control on this

Limitations of Dynamic Sourcing:

Features in Intelligent Cubes that are not supported for Dynamic Sourcing:

·         The following set qualifications in the Intelligent Cube filter:

o    Metric qualifications

o    Relationship qualifications

·         OR, AND NOT, OR NOT logical operator in the report filter.

·         Use of Pass-through functions such as ApplySimple in metrics.

·         Report as filter used in the Intelligent Cube filter.

·         Report limit.

·         Metrics that use facts with fact degradation or fact extension.

·         Conditional metrics

·         Compound metrics not defined as smart.

·         Consolidations

·         Custom Group

·         Query Builder or Free Form SQL reports cannot be used.

·         MDX reports using SAP BI, Microsoft Analysis Services or Hyperion Essbase.

·         The following set qualifications in the report filter:

o    Relationship qualifications that do not include a filter in their definition, or that use a different Relate By option than Use System Default.

o    Metric qualification.

·         Metrics that use pass through functions like ApplySimple.

·         Metrics that use facts with fact extension or degradation.

·         Data marts

·         Report as filter used in the report filter.

·         Using any of the following VLDB properties:

VLDB Property	Options that prevent report from using Dynamic Sourcing
Downward outer join	Preserve all rows for metrics higher than template level without report filter
	Do not do downward outer join for database that support full outer join
Preserve all the lookup table elements	Preserve lookup table elements joined to final pass result table based on fact table keys
	Preserve lookup table elements joined to final pass result table based on template attributes without filter
	Preserve lookup table elements joined to final pass result table based on template attributes with filter
OLAP function support	Preserver backward compatibility with 8.x and earlier.

Intelligent Cubes & Security filters:

·         Cube will not apply any security filters while fetching values from DW

·         While Running view report on top of the cube, it fetches the values from the cube and then apply security filters

·         In order to apply security filters on cube data, it is having few restrictions

o   All Security Filter objects should be present in the cube   - Else View report display "No Data"

o   Security Filter object is not present in the Cube but related objects are present in the cube

§  Report gives values but Metric values will be blank

Cubes and our multi-Tenancy architecture:

·         We should create cubes based on connection mapping

·         We can create one cube but we need publish the cube separately for each customer

o   Login any one user of a particular customer and then we need to publish

o   Each user from that particular customer should have access to publish the cube

o   Create a separate security role to support intelligent cube configurations (publish, schedule)

o   We do have option to publish the cube from web environment but currently it is not required

·         If we have only one DW which holds all customer data then

o   Only one cube is sufficient

o   Only one time publish is also sufficient

§  Reason is cube will not apply any security filter and when you create view report then that time it will apply security filter

·         Loading Intelligent cube data into memory

o   Load data into memory while publishing

o   Load data into memory only while running the view report

RAM & Physical Memory:

·         We should get a sufficient hard disk space to store all the cube files

·         We should get sufficient RAM to load the cube data into memory

Avg Cube Size  =650 KB

For 50 customers =650 * 50 KB =3000 KB

To perform publish operation (2.5 times of cube size)  = 650* 2.5 = 1625 KB

ACL settings:

·         We can modify ACL settings as like other objects accordingly

·         By default cube will not be visible in Web

·         So we can maintain cubes in public objects , in a hidden folder

o   So that, it will be easy to publish as a user

Refresh Cubes:

·         We can refresh cubes with time based and event based schedules

·         We can go for a event based schedule and refresh the cube

·         We can trigger the events through command manager  after completion of our ETL

o   It is very much same as our cache refresh

o   After ETL completion, trigger the command manager with all event trigger scripts (which is stored in a file)

·         Incremental refresh

o   Incremental refresh condition should decided with respect to report and report filter condition

o   As a basic option, we can consider ETL Mod time

§  We need to implement ETL Mod time object in our MSTR Schema