Monday, 18 April 2016

Idea Exchange - My Ideas Posted in MSTR Community - Better Support for Exporting from Document Cache


2) Better Support for Exporting from Document cache:




MSTR Community Link:


Note: Microstrategy considers our ideas based on the votes. Please cast your vote which will help to get this as a new feature in the future MSTR release

The main purpose and important reason why we create\recommend document cache is to get better performance which gives better user experience. Microstrategy do supports document cache with different output mode but still I feel few improvements needed in the way it handles the data export from the cached document. 
Improvements needed: 
  1. Options to export a single grid or graph (Not a whole document or a panel or a layout) in a document from the cache – Without re-executing the document in the background
  2. If possible connect different cache output modes. Ex: Use excel cache when user tries to export a graph from flash mode (Use\connect excel cache from flash cache) 
Let’s take some example to understand this behavior in a better way, 
We have a heavy weight document which usually executes in 20 mins. We usually creates cache in all possible output modes to give better user experience to our customers. 
Let’s consider, 
  • Document is cached in all output modes and now we are executing the document in flash mode (deprecated flash mode in MSTR 10)
  • We are getting results quickly – Very good
  • Document is having lots of different panels with different visuals and grid. User likes to export one piece of grid\graph into excel for different business reason – sounds very valid & day to day business case
  • User tries to export a particular grid into an excel – here starts the problem
  • In order to export a small piece of grid or a graph from a heavyweight document, it has to execute the complete document and then it exports that particular grid – here it gives very bad user experience and purpose of my document cache is not served completely. MSTR 10 handles this even worst

  • We may have different workarounds or other options to deal with this problem in Microstrategy but most of them are asking a compromise from the user experience which is not good
    • Few options are
      • If you want to export into Excel, then create Excel cache and instead of executing the document in a flash mode, go with direct export to excel option – yes, one way it solve my problem but still I can’t export a piece of graph in the whole document. We can only export the whole document
      • Same approach for PDF. Create a PDF cache and export the whole document in PDF
      • Here for each export options customer has to run a document in different mode – All are not in one place

Idea Exchange - My Ideas posted in Microstrategy community - Edit Mode in Visual Insight Dashboard


1) Edit Mode in Visual Insight Dashboard:

MSTR Community Link:


Note: Microstrategy considers our ideas based on the votes. Please cast your vote which will help to get this as a new feature in the future MSTR release

I feel it will be really useful and convenient to have Edit mode\Offline mode in Visual insights dashboards as like regular document. Edit mode\Offline mode what I mean is to open VI dashboards without executing\processing data. 
Below are few very useful scenarios where we need edit mode,

  • We can open a heavy weight VI dashboards in edit mode easily during the development
  • This will be very useful to debug and fix few errors (corrupted metrics & any corrupted components)

Consider the below example,
 We have few very complex visual insights dashboards where we have few derived metrics\summary metrics defined in the VI dashboard level. Due to some changes in the dataset, few derived metrics are corrupted\goes to invalid state. In this case, when we execute that VI dashboard, it throws error and no way have we can fix\debug in VI dashboard level. Only option is to convert it to a document and then debug and fix it in the document level but even after the fix, there is no way we can recover the VI dashboard. Only option is to recreate 
Imagine a VI dashboard with 6-7 layouts. Everything is good except one derived metrics used in one layout is corrupted\goes to invalid state. This simple one issue spoils the entire VI dashboards. In case the same dataset is shared with multiple VI dashboards then it will be even worse. 
Above is just one example. We have lots of different scenarios which put our VI dashboards to invalid state. Most of them actually need very small fix but because we don’t have any option to open the VI dashboard offline or without processing any data, we are losing the complete dashboard all the time.

Monday, 20 April 2015

Intelligent cubes

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