Thursday, 19 May 2016

Microstrategy 10.x/10.1/10.2 Visual Insight Dashboard (HTML 5) Performance


As explained in my another post, Visual insights dashboards in HTML 5 (MSTR 10) is the major change from Visual insights dashboard in Flash (MSTR 9.x)

Please refer the below link for more details:

https://www.blogger.com/blogger.g?blogID=8659869250212697370#editor/target=post;postID=119211721674939237;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=1;src=postname

In this post, I am giving few specific items on performance in HTML based Visual insights dashboards. Before get into the details, this may only applicable for projects with large schema. yes it is related with project schema size.

After moving to Microstrategy 10, we may see few differences in performances. In few cases, you will see better performance in MSTR 10 and in few cases, you may see bad performances in MSTR 10.

In this post, we are going to discuss about MSTR 10.x Visual insights dashboard performances and few tuning options

In Visual Insights, few activities where we may see slowdown  in VI dashboard in MSTR 10

Example,

(1) Moving from one layout to another layout in VI dashboard 
(2) Moving to different panels
(3) Applying different filter condition

basically most of the activities inside the VI dashboard. In HTML 5, for every single click, we are seeing the execution icon spinning for secs and some cases, it takes really long time.

Ex: Moving from one layout to another takes a minute - This is not very good

In general, as a nature of HTML 5, for every click or activity, it will execute and process and get the values. if it is within 2-3 seconds or max less than 10 secs then it is acceptable but anything more than that will create very bad user experience. 

Below are few settings or configurations will help to improve the Visual Insights dashboard performance:

(1) Project default Drill Map:

    a) Make sure you have a default project drill map configured in your project. In case it is empty then, MSTR considers/loads the huge chunk of your schema into the memory. You can see the same reflects in the size of your VI dashboard cache. 

Execute the VI dashboard and check the size of your layout HTML cache. if you don't have default project drill map then you will see a big cache size and the same will impact the performance as well

  b) In case your schema is very big with more number of attributes and facing performance issues in VI dashboards. Ex: Moving from one layout to another layout takes around 40-60 secs then Configure an empty drill map in project default drill map. This will reduce the cache size and give better performance. 


(2) VLDB : MCE Join to get data combination:

Project Configuration -> Advanced -> Project Level VLDB ->Metrics

MCE Join to get data Combination : Select "Use Minimum relations to do the join with less memory" 

This will set same behavior for multi cube engine as like MSTR 9.4.1. 

This will improve performances in few cases especially when you use external data set and blending with other datasets in your dashboard. 

Note: Please double check the data


 (3) Join behaviour:

In VI dashboard, go to File ->Dashboard Properties -> Join Behavior -> Uncheck "Allow joins across datasets based on unrelated common attributes"

This option will help to improve the performance in few cases. 

Note: Please double check the data 


 (4) Cube Footprint:

In case you are using Intelligence cube based on external source data (File or external data connection) and seeing performance issue or memory error then please check the cube memory foot print. Compare your MSTR 10 cube foot print with MSTR 941 cube memory foot print. 

For sure you will see some difference. If the difference is within acceptable range then it is fine but in case you see a huge increase in MSTR 10 then you have to consider the below points.

a) Check your schema size. If your project is having huge number of attributes then mostly you will see this large cube memory footprint.  In MSTR 10.x, It considers your whole project schema and keep that in cube memory to improve the blending performance but in case of large schema with small and medium size cube, this will impact the memory and performance drastically.

We don't have any solution available at this point. We can expect some improvements in the future microstrategy releases. 

At this point, below are the different options (few options may not be feasible for all environments),

a) Reduce the project schema size if possible. Reduce the number of attributes

b) Move the respective dashboards and its dependents alone to different new project

c) Increase the server memory and memory allocated to Intelligent Cube


Conclusion:

In case we have project with large schema especially with more number of attributes,you will see an impact in cube memory foot print and VI dashboard performance in MSTR 10 . Few of the above mentioned configurations may help in few scenarios. Anyway we will get the best performance with lean schema in MSTR 10.x


Friday, 13 May 2016

Visual Insight Dashboard - Microstrategy 9.x/9.4.1 (Flash) Vs Microstrategy 10.x/10.1/10.2/10.3 (HTML 5)

In this post, I am writing few important differences between MSTR 941 flash based visual insight dashboard and MSTR 10.2 HTML 5 based visual insight dashboard. 

I am writing this only based on my experience and my understanding. I may be wrong in few cases or few of them may be changed in hot fixes or new versions. 

Starting from version 10, Microstrategy deprecated Flash and introduced HTML 5 based dashboards. this is applicable for both documents and VI based dashboards. 

This is one of the very major and important change in Microstrategy 10. In this post, we are going to see few key characteristic difference because of this change.


1) Rendering :

This is one of the major difference between Flash and HTML 5 based visual insights dashboard. 

In a Flash based VI dashboard, it loads all data and charts in a single shot. When you run a dashboard, it will load everything in your browser in the first run. After that, everything else (moving to different layout, applying filters, moving to panels) is very smooth. No additional process

In HTML 5, It will only load what you see in the first screen. After that, when you move to different layout or panel, it will load at that time. Even in the grid, when you scroll down, it will load again 


2) Caching:

In MSTR 941 Flash based VI dashboard, 

It creates one XML and one Flash based cache file for the entire dashboard. It includes all layout, panels etc

In HTML 5, It creates one HTML 5 cache per layout and panels. In case your dashboards has 5 layouts then it creates 5 HTML 5 cache file. If it is having 5 layouts and 2 panels then it creates 7 HTML 5 cache file

In this case, we can't create all cache by a single cache update subscription. we have to execute each and every layout manually to create cache


3) Property Changes:

Because of the HTML 5 , we are seeing few differences in properties

a) Add & remove  - In MSTR 941 VI grid, we have options to add and remove objects instead of drag and drop from the data set panel

b) Export -  Few important changes here 

  • From MSTR 10, No export support to .mht format so no option for offline interactive dashboard except .mstr format for which we need Microstrategy desktop to open
  • No option to export the particular piece of graph or grid within that layout. Only option is to export the whole layout. 
  • Exporting a big grid from the HTML 5 based dashboard - It will only export the grid data what you are seeing in the screen. It will not export the whole grid
c) External data set 

In HTML 5, when you add external data inside the VI dashboard, by default it will not save the cube automatically instead it will be a server managed cube. 

In this case, we can only publish the cube from the VI dashboard where we used this external cube or data.

But we have option to save this manually. In the VI dashboard, right click the dataset and save it as a dataset


Here in this post, I only highlighted few critical\key elements and differences. 

Apart from this, there are lots of new and awesome features added in Microstrategy 10.x which are really useful and cool. 

Few Examples, 

  • Very clean visuals and dashboards
  • D3 Visualization plugin - we can add D3 charts easily
  • PRIME Cube - Improves the performances for large Cubes
  • Lots of formatting options - Very interesting
And many more interesting items.
 

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 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