Last week, one of our clients had hugh increase in the sales numbers. Everyone in the BI team gets reports regarding data; data load status, etc as soon as it is over. Local team was a bit amused and prima facia client ath “finally Christmas sale has picked up”. Wow.. but when ETL team looked closely, we came to know that was issue with data provided by mainframe team. Scheduler uploaded two copies of the files in the folder under which all files are loaded blindly into data warehouse. Rarely faced any issue like double file.

I asked my colleague, who is a Narrowcast champ, why can’t we have few schedules/alters for data load errors. He told me that they were already having several emails regarding data load status. Personally, it is a bad idea to receive data load status report on daily basis, as chance of an error is next to nil and you may forget to read automated status report. (Though he manages the status religiously and had instantly pointed out to ETL team regarding this issue that day, I was looking for conditional alerts. My friend told me that conditional alerts are only possible using FF SQL reports, also we need to use Windows scheduler to activate the alerts and need to compare if we require it to send only if some condition is met. Pretty messy and lengthy in Narrowcast server.

Finally, I found a very easy solution using Web subscriptions. Well….. it is possible to create an email subscription which will be executed when a certain conditional is satisfied. To create a conditional email subscription, use following steps.

1. Create reports with base metric and at least one threshold metric.

2. Execute the report.

3. From Right mouse click menu on any metric, select Alerts.

4. Alert editor (Similar to filter editor) opens up. You can specify the criteria.

5. Under Delivery settings, specify Schedule, email address, delivery format and any custom message.

6. Bonus: If you want another report to be sent if this condition is satisfied, you can specify the report name using “Send different report or document”.

In case if anyone wants to do dashboard mockup quickly, here is the link to build instant dashboards using Excel (using multidimensional view data) quickly;

 https://cloud3.microstrategy.com/MicroStrategy/servlet/mstrWeb (Not sure for how long it’s free and public)

Data Refresh Options:

1. Full Refresh: Same as in 9.0.1. Whole report will be refreshed
2. Dynamic Refresh:  Data will be refreshed based on the filter on Cube

Example:

• let’s say cube filter has ‘Today minus 5 days’
• Assume cube published on 18^th of September
• So the Cube will hold data from Sept-13^th to Sept-18
• What happens when cube refreshed on Sept-20^th (Now the cube should hold data from Sept -16^th to Sept-20^th)
• When the cube refreshed, it generates filter condition as follows;

Incremental Data fetch Condition

(a11.DAY_DT between DATE '2011-09-15' and DATE '2011-09-20'  and (not a11.DAY_DT between DATE '2011-09-13' and DATE '2011-09-18'))

So the report fetches data for only two days

Later part of the Cube SQL, it deletes the old data which not required for the ‘Between Today minus 5 days as Sept-20^th’

Data Deletion Condition:

([Day]@[DAY_DT] between 2011-09-13 00:00:00.000 and 2011-09-18 00:00:00.000

 and [Day]@[DAY_DT] not between 2011-09-15 00:00:00.000 and 2011-09-20 00:00:00.000)

• Update Option: Inserts new data and deletes overlapping rows between old and new

If any history gets updated then that can be also updated

• Insert Option: Just inserts the data in the cube memory

SQL pattern for Dynamic Refresh option when cube published for second time (recursive);

What is ‘Define Incremental Refresh Report Option’ (right click on Cube)

 Filter Option:

Let’s say, the cube hold data for 4 weeks of data for countries Canada and USA. Now I want to include a Germany alone for the same 4 weeks. Then instead of modifying the original cube…we can make use of ‘Incremental Refresh Report Options’

• Right click on Cube à select ‘define incremental refresh report options’
• Add Germany to the filter
• Save that as ‘Incremental Refresh’

Note:

• The filter should use the any one of the attribute present in the Cube
• Once incremental refresh is created, Cube should not refreshed only Incremental refresh can be scheduled
• Any number of Incremental refresh can be created against one cube

Report Option: (Haven’t tried much on this option yet)

From MSTR :  This option allows you to define a report that is used for the incremental refresh. When the incremental refresh is executed, the results of the report are compared to the data that is already in the cube, and is updated based on the option you choose in the Incremental Refresh Options, General tab). It must include all the attributes and metrics that are present in the Intelligent Cube. The Report should have at least one metric from the cube.

Dynamic Sourcing:

If a normal grid holds same or few objects of an Cube, then dynamically I-Server determines that report has to hit the DB or Cube memory. Since in this example, grid holds few or same number of objects as in CUBE, the I-Server makes this report to hit Cube

PS:Thanks to my colleague who also helped me in my busy schedule to collect few good points on this topic.