Tag Archives: a

Interview Question #11: History List Messages Stored in a Database

Question

Which of the following is possible only when History List messages are stored in a database?

A. History List Messages are available in a two-tier environment.

B. When deleting History List messages using a schedule, a single session is created on the Intelligence Server.

C. History List messages can be shared across cluster nodes.

D. You can duplicate History List messages on reprompt or refresh.

E. Both B and C.

 

 

 

 

 

Answer

B. When deleting History List messages using a schedule, a single session is created on the Intelligence Server.

Benefits of Using Database-Based History List Storage

  • Access additional information about each History List message, such as message and job execution statistics. As a result, you can effectively monitor and manage the History List based on its actual usage by the users.
  • The database backing of the History List provides greater scalability and improved performance. Instead of accessing a multitude of large files that usually reside on the server machine, you retrieve users’ inbox information from a database.
  • When the administrative task to delete History List messages is triggered, it creates only one session on the Intelligence Server rather than multiple separate sessions for each user.
  • Use the History List Messages monitor to monitor and manage messages for each MicroStrategy user.

 

MicroStrategy Course Where You Will Learn About This Topic

MicroStrategy Administration Course

Using the R Integration functionality, how to perform Text Mining on a MicroStrategy report and display the result

Readers:Jaime Perez

Here is another great post in the MicroStrategy Community from Jaime Perez (photo, right) and his team. A lot of work when into the preparation of this post and it shows some great ways to use the “R” integration with MicroStrategy.

Contributors from Jaime’s team include:

ssonobe  ssonobe

Lili 

Joanne A 

Ohingst  Ohingst

Enjoy!

Michael

Text Mining Using R Integration in MicroStrategy

Users may wish to perform text mining using R on the result of any arbitrary MicroStrategy report and display the result. One of the problems that hinders the users from achieving it is that the number of output elements is not always consistent. For example, a report may have three attributes named ‘Age groups’, ‘Reviewer’, and ‘Survey feedback’ and the report might display four rows of feedback as follows:

01.jpg

If the above report result is sent to R as an input and the R script breaks down each sentence of the feedback into the term frequency that is grouped by the age groups, it will have 18 rows.

02.jpg

Since the number of output elements is greater than the number of the MicroStrategy report rows, the report execution will fail. Using the objects in the Tutorial project, this technical note (TN207734) describes one way to display the result of text mining on a MicroStrategy report, using the R integration functionality.

PREMISE:
– Following the instructions in TN43665, the MicroStrategy R Integration Pack has already been installed on the Intelligence Server.

The Steps Involved

STEP 1: Decide on the input values that need to be sent to R via R metrics
The first step is to decide on which data you wish to perform text mining. In this technical note, the sample report will let users select one year element, the arbitrary number of category elements, and specify the Revenue amount in prompts. The report will then display the value of the normalized TF-IDF (term frequency and inverse document frequency) for every word showing up in the qualified Item attribute elements, grouped by the Category elements.

A user may select the following values for each prompt and the report may look as shown below.

  • Year: 2012
  • Category: Books, Movies, and Music
  • Revenue: greater than $15,000

03.jpg

Eventually, the user may want to see the normalized TF-IDF for every word showing up in the Item attribute elements as shown below:

04.jpg

Since the final output displays each word from the Item attribute and it is grouped by the Category elements, the necessary input values to R are as follows

  • The elements of the Category attribute.
  • The elements of the Item attribute.

 

STEP 2: Create metrics to pass the input values to R

The input values to R from MicroStrategy must be passed via metrics. Hence, on top of the current grid objects, additional metrics need to be created. For this sample report, since the inputs are the elements of two attributes, create two metrics with the following definitions so that the elements are displayed as metrics.

Max(Category@DESC) {~}

Max(Item@DESC) {~}

05.jpg

 

STEP 3: R script – Phase 1: Define input and output variables and write R script to obtain what you wish to display in a MicroStrategy report

In the R script, define (1) a variable that receives the inputs from MicroStrategy and (2) a variable that will be sent back to MicroStrategy as the output as depicted below. Since the number of output elements must match with the number of input elements, it is defined as “output = mstrInput2” to avoid the errors. In other words, this script executes R functions to obtain the data that you wish to display in a MicroStrategy report, but the output is the same as the input. More details about how to display the result in a MicroStrategy report will be followed up later in this technical note.

06.jpg

 

In this technical note, after manipulating the input value, we assume that the variable named ‘norm.TF.IDF’ in the R script holds the values of the TF-IDF for each term.

07.jpg

 

STEP 4: Create tables in the data warehouse to store the value of your R output

In order to display the values of the ‘norm.TF.IDF’ defined in a MicroStrategy report, tables to hold the result need to be created in the data warehouse. In other words, additional report will later have to be created in MicroStrategy and it will extract the data from the database tables, which are created in this section.

In this specific example, the variable ‘norm.TF.IDF’ has the elements of words (terms) and categories and the values of the normalized TF-IDF. Considering the types of data, the first two should be displayed as attributes and the values of the normalized TF-IDF should be presented in a metric. Hence, two lookup tables to hold the term and category elements and one fact table need to be created to store all the data. On top of these tables, one relationship table is also required since the relationship between words and categories is many-to-many.

 

STEP 5: R script – Phase 2: Populate the tables in your R script

As previously mentioned, the variable named ‘norm.TF.IDF’ contains the values, which a user wishes to display in a MicroStrategy report as shown below.

07.jpg

 

In this R script, four more variables are defined from ‘norm.TF.IDF’, each of which contains the subset of data that will be inserted into the database tables.

 

tm_Category holds the unique elements of the Category.

10.jpg

 

tm_Word holds the unique elements of the Word (Term).

11.jpg

 

tm_Word_Cat stores the values of the many-to-many relationship.

12.jpg

 

tm_Fact contains the values of TF-IDF for every Word-Category combination.

13.jpg

 

In the R script, populate the database tables with the above four subsets of ‘norm.TF.IDF’.

# Load RODBC
library(RODBC)

# RODBC package: assign ch the connectivity information
ch <- odbcConnect("DSN_name")

# Delete all the rows of the tables
sqlClear(ch, "tm_Category", errors = TRUE)
sqlClear(ch, "tm_Word",     errors = TRUE)
sqlClear(ch, "tm_Word_Cat", errors = TRUE)
sqlClear(ch, "tm_Fact",     errors = TRUE)

# SQL: insert the data into tables; use parameterized query
sqlSave(ch, tm_Category, tablename = "tm_Category", rownames=FALSE, append=TRUE, fast = TRUE)
sqlSave(ch, tm_Word,  tablename = "tm_Word", rownames=FALSE, append=TRUE, fast = TRUE)
sqlSave(ch, tm_Word_Cat, tablename = "tm_Word_Cat", rownames=FALSE, append=TRUE, fast = TRUE)
sqlSave(ch, tm_Fact, tablename = "tm_Fact", rownames=FALSE, append=TRUE, fast = TRUE)

#Close the channel
odbcClose(ch)

 

STEP 6: Create and add an R metric, which implements the R script

The R script is done. It is time to implement this R script from MicroStrategy by creating an R script. In the deployR interface, open the R script and define the input and output that you specify in Step 3 as follows. Since the elements of the Category and Item attributes are characters, choose “String” as its data type. Likewise, since the output is the same as the mstrInput2, its data type is also set to string.

14.jpg

 

Create a stand-alone metric and paste the metric definition of the deployR utility. Then, replace the last parameters by the Category and Item metrics that you created in Step 2.

15.jpg

 

Add the R metric to the report.

15.2.png

 

The report and R will perform the following actions after adding the R metric
i. The report lets users select the prompt answers
ii. MicroStrategy sends the Category and Item elements to R via the R metric
iii. R performs text mining to calculate the TF-IDF based on the inputs
iv. R generates subsets of the TF-IDF
v. R truncates the database tables and populates them with the subset of the TF-IDF
vi. R sends the output(which is actuary the input) to MicroStrategy
vii. The report displays the values of all object including the R metric

 

STEP 7: Create MicroStrategy objects to display the data

From the tables created in Step 4, create the Word and Category attributes and the fact named weight. The object relationship is as depicted below.

08.jpg

09.jpg

 

Now, create a new report with these objects. This report will obtain and display the data from the database tables.

16.jpg

 

STEP 8: Utilize the report level VLDB properties to manipulate the order of the report execution jobs

There are currently two reports and let each of which to be named R1 and R2 as described below

  • R1: A report which prompts users to specify the report requirements and implements the R script executing text mining
  • R2: This report obtains the result of text mining from the database and display it

 

If the two reports are placed in a document as datasets as shown below, there is one problem: R2 may start its execution before R1 populates the database tables with the result of text mining.

17.jpg

 

In order to force R2 to execute its job after the completion of R1, the VLDB properties PRE/POST statements along with additional database table may be used. The table tm_Flag contains the value of 0 or 1. R2 is triggered when R1 sets the value of completeFlag to 1. The detailed steps are described below with the script for SQL Server.

 

i. Create another table in the database, which holds the value of 1 or 0

CREATE TABLE tm_Flag
(
   completeFlag int
)


INSERT INTO tm_Flag VALUES(0)

 

ii. In the VLDB property ‘Report Post Statement 1” of the R1 report, defines a Transact-SQL statement that changes the value of completeFlag to the value of 1.

DECLARE @query as nvarchar(100)
SET @query = 'UPDATE tm_Flag SET completeFlag = 1'
EXEC sp_executesql @query

 

iii. Define the VLDB property ‘Report Pre Statement 1’ in R2 so that it will check the value of completeFlag every second and loop until it turns to 1. After the loop, it will revert the value of completeFlag back to 0. After this Report Pre Statement, R2 will obtain data from the database, which has been populated by R1.

DECLARE @intFlag INT
SET @intFlag = (select max(completeFlag) from tm_Flag)

WHILE(@intFlag = 0)
BEGIN
	WAITFOR DELAY '00:00:01'
	SET @intFlag = (select max(completeFlag) from tm_Flag)
END

DECLARE @query as nvarchar(100)
SET @query = 'UPDATE tm_Flag SET completeFlag = 0'
EXEC sp_executesql @query

 

Activity Diagram

18_revised.png

 

 

Overall execution flow

  1. Answer prompts

19.png

 

2. Only the text mining result is displayed to users

20.png

 

Third Party Software Installation:

WARNING: The third-party product(s) discussed in this technical note is manufactured by vendors independent of MicroStrategy. MicroStrategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

 

A Must Read: Bryan Brandow’s BI Blog on Evolving BI

Readers:

I have some good news to share with you.

Bryan BrandowBryan Brandow, a Data Engineering Manager, has started (or perhaps restarted) blogging again. His new blog is called Bryan’s BI Blog and the first post is a must read. It is titled Evolving BI and Bryan shares his latest adventures, his thoughts on the current state of BI as he has observed and experienced it, and where he feels we collectively need to go.

Bryan has also migrated all of his old MicroStrategy Blog posts to this blog site and I encourage you to read these as well.

Here is a link to the Evolving BI post.

Best Regards,

Michael

 

Interview Question #3: Converting a Consolidation to a Custom Group

Question

Can you always convert a consolidation to a custom group and vice versa?

Answer

You can always convert a consolidation to a custom group, but you can only convert a custom group to a consolidation when the custom group filters are from the same table or very similar tables.

Bryan Redux #2: Removing Sections of a Report via URL API

Readers:

Here is another blog re-post from my friend, Bryan Brandow’s MicroStrategy site.

If you want to directly visit Bryan old site, the URL is http://www.bryanbrandow.com.

Best Regards,

Michael

Bryan originally posted this on July 22, 2011.

Removing Sections of a Report via URL API

WhBryan Brandowile working with the Web SDK to try to make a small customization, I stumbled on a pretty useful set of URL API codes that allow you to quickly modify the Report Page by removing various elements.  While such customizations are common, I wasn’t aware that they were available out of the box.  I would think that these would be very useful in doing simple linking to reports via an IFrame from another application, such as a Portal.

The trick to get the URL for a Report by right clicking it in Web, choosing Properties, and then copying the Link at the bottom.  Now, locate the src section of the URL:

ASP: &src=Main.aspx.4001 

JSP: &src=mstrweb.4001

and add in one of the transforms just before the .4001.

Available transforms that I’ve found:

reportNoHeader

reportNoHeaderNoFooter

reportNoHeaderNoFooterNoPath

reportNoHeaderNoFooterNoPathNoToolbar

Example:

ASP: &src=Main.aspx.reportNoHeader.4001

JSP: &src=mstrweb.reportNoHeader.4001

Unfortunately, these are actual transforms and not flags, so you can’t mix and match to fit your needs (for example, if you only want to hide the toolbar, reportNoToolbar will NOT work).  Those are the only 4 that you can use that I’ve found, but they may be handy in a pinch and best of all, not require customization work to use.

Update:

Another method of doing this that does let you pick and choose and supports documents as well:

&hiddenSections=header,footer,path,dockTop

Place that code in the URL, for example (Note: I have shown this on multiple lines. You should paste this in as one long line with no line breaks):

http://webserver/Microstrategy/asp/Main.aspx?Server=ISERVER&Project=PROJECT&Port=0&evt=2048001
&hiddenSections=header,footer,path,
dockTop&src=Main.aspx.2048001
&visMode=0&documentID=04AACFE445AD69198676C8AD56245118
&currentViewMedia=2

List of options (case-sensitive):

  • header
  • path
  • dockTop
  • dockLeft
  • dockRight
  • error
  • content
  • dockBottom
  • footer

Bryan’s Blog Entry Link:  http://www.bryanbrandow.com/2011/07/removing-sections-of-report-via-url-api.html

A Must Have Tool: The Data Visualisation Catalogue

Data Visualisation Catalogue

Readers:

This is something I find to be very worthwhile and a great tool to have available when you have data, but can’t decide on which visualization is best to use.

The Data Visualisation Catalogue is currently an ongoing project developed by Severino Ribecca.

Originally, Severino started this project as a way to develop his own knowledge of data visualisation and to create a reference tool for him to use in the future for his own work. Fortunately for us, Severino thought it would also be useful tool to not only other designers, but also anyone in a field that requires the use of data visualisation regularly (economists, scientists, statisticians etc).

Severino website is very comprehensive, detailed and can help you decide the right method for your needs.

He plans on adding in new visualisation methods, bit-by-bit, as he continues to research each method to find the best way to explain how it works and what it is best suited for.

The project itself is in the developmental stages at the moment.

All news and website updates can be found on Twitter.

I also encourage you to donate to this cause. I just donated today.

Best regards,

Michael

Below is an example of how Severino has catalogued the Bar Chart

dataviz catalogue 1 dataviz catalogue 2 dataviz catalogue 3

Tips & Tricks #1: How to filter more than one substring in the Find search box when selecting a long list of elements from a dynamic prompt list

There are cases in which a dynamic prompt is used to qualify attributes selected to be part of a report, but the attribute list is very long.

Attributes like ‘Street Address’ may contain keywords like ‘St’, ‘Ave’, ‘Dr’ that can be used to reduce the list of selected elements.

It is possible to filter the list using the find box and to include one or more substrings that can be logically ‘ORed’ (Will be included if any of the substrings is found) or logically ‘ANDed’ (Will be included if all substrings).

Pattern delimiters are ‘ ‘ (space), ‘%’ and ‘_ ‘

Space or blank  = will be used for logical ‘OR’

‘%’  =  Will be used for logical ‘AND’  usually in pairs (begin-end of string)

‘_’   =  Will be used as wildcard in lieu of space (blank)

 CASE 1: Filter all attributes that contains ‘z’ or ‘x’ strings:

Case1

In this example, all listed addresses contains string ‘z’ OR string ‘x’ in any position of the description.

CASE 2: Filter all attributes that contains first the string ‘Old’ AND then ‘Hwy’:

Case2

Observe that substring ‘Old’ and ‘Hwy’ can be part of other string and can start in any position.  However, using %Hwy%%Old% will produce a different result.

In this example, this filter will produce an empty list as there is no address that have substring ‘Hwy’ before ‘Old’.

CASE 3: Filter all attributes that contains the substring ‘Old Hwy’:

Case3

Special character  ‘_’ should be used to represent a single space. Cannot be duplicated for representing multiple space.

In this example, if the filter were ‘_Old_Hwy’, the last two elements will not be shown as they do not begin with spaces.

NOTE: Examples shown in MicroStrategy Desktop, but it apply also to the Web interface when using the find box in dynamic prompts.