Tag Archives: and

Interview Question #12: Command Manager v9.3.1 and Intelligent Cubes


In Command Manager 9.3.1, you can use scripts to manage Intelligent Cube Caches.










MicroStrategy Course Where You Will Learn About This Topic

MicroStrategy Administration Course

Introducing Adobe Brackets 1.0 and Extract for Brackets (Preview)


We had a Technology Discovery Day the other day at work. I worked my way over to the folks from Adobe to try to get some questions answered. I have been an Adobe Flex and Flash user and wanted to know what my alternatives were going to be going forward with custom mobile report development for Apple products since they do not support Flash (actually, they really do, but don’t want us to know that).

I talked to one of the reps from Adobe and he told me to look at Adobe Brackets. I have downloaded and installed the Brackets editor and have been getting familiar with it.

Here is a brief overview of Adobe Brackets from their Creative Cloud Team. Once I become more proficient, I will blog more about it.

MicroStrategy, Tableau, Qlikview: Are you listening?

Best Regards,



What is Brackets?

Brackets is a modern, open source text editor that understands web design. It was built for web designers and front-end developers working with HTML, CSS, and JavaScript. Adobe not only created Brackets, but were also a key contributor to the project. When Adobe first started Brackets, they wanted to release early and often. They have done both. This will mark the 45th release of Brackets in 3 years; it’s a pace of innovation that our teams are able to maintain now that we’ve moved to Creative Cloud.


Introducing Extract for Brackets (preview)

This release also includes a preview of Extract for Brackets, a new Creative Cloud service that lets you view and get information and assets out of a PSD right from your text editor. Extract for Brackets lets you pull things like colors, fonts, measurement, gradients, and more from a PSD in the form of contextual code hints in CSS and HTML files. You can also extract layers as images, use information from the PSD to define preprocessor variables, and easily get dimensions between objects. Adobe is really excited about how it will improve the process of moving from design to development and speed workflows.


If you haven’t looked at Brackets in a while or are brand new to it, now is a great time to see the awesome stuff Adobe has been working on.

Brackets blends visual tools right into the editor so you get the right amount of help when you want it. In more recent releases, Adobe has added multiple cursors, split view, theme support, and many more fixes and enhancements.

Extract for Brackets (preview) can be downloaded as a standalone extension from the Brackets Extension Registry or included with Brackets 1.0 in a bundle that’s immediately available as a free download from brackets.io.



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


Joanne A 

Ohingst  Ohingst



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:


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.


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.

– 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


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


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) {~}



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.



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.



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.



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.



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



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



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



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

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


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.



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.



Add the R metric to the report.



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.




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



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.



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

   completeFlag int



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.

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

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

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


Activity Diagram




Overall execution flow

  1. Answer prompts



2. Only the text mining result is displayed to users



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.


Steve Heller, Alberto Cairo, and The World in Terms of General Motors

World in Terms of GM Cutout


The other day on Twitter, Albert Cairo tweeted about a great visual map he found in a 1938 issue of Fortune Magazine at Steve Heller’s Moving Sale on Saturday, June 28th, 2014 in New York City.

Alberto Cairo GM Tweet

Daily Heller Moving Sale

Steve Heller

Steve HellerSteven Heller wears many hats (in addition to the New York Yankees): For 33 years he was an art director at the New York Times, originally on the OpEd Page and for almost 30 of those years with the New York Times Book Review. Currently, he is co-chair of the MFA Designer as Author Department, Special Consultant to the President of SVA for New Programs, and writes the Visuals column for the New York Times Book Review.

He is the co-founder and co-chair (with Lita Talarico) of the MFA Designer as Author program at the School of Visual Arts, New York, where he lectures on the history of graphic design. Prior to this, he lectured for 14 years on the history of illustration in the MFA Illustration as Visual Essay program at the School of Visual arts. He also was director for ten years of SVA’s Modernism & Eclecticism: A History of American Graphic Design symposiums.

The World in Terms of General Motors

The visual in the December 1938 issue of Fortune Magazine was called The World in Terms of General Motors. It depicted a sketch map showing the location of (then) GM’s 110 plants. The spheres representing each plant are proportional (in volume) to their normal number of workers. The key numbers of the spheres are indexed on the map. The map does not include those manufacturing plants in which GM has less than 50% stock. The principal ones are Ethyl Gasoline Corp., Bendix Aviation Corp., Kinetic Chemicals, Inc., and North American Aviation, Inc.

Not shown are GM’s many non-manufacturing interests, domestic warehouses, etc.

So, finally, here is the complete map.



[Click on the map image to enlarge]



Wisdom of Crowds 2014: Success with Business Intelligence and Technology Priorities


Howard DresnerLast week, Howard Dresner released the 2014 Edition of his Wisdom of Crowds Business Intelligence Market Study.

Mr. Dresner is Chief Research Officer of Dresner Advisory Services, LLC, an independent advisory firm and a well-known authority and author in the areas of Business Intelligence and Performance Management.

Howard has 32 years of IT industry experience with 24 years in the Business Intelligence market.

He spent 13 years at Gartner, where he was a Research Fellow and Lead Analyst for BI. He also served as Chief Strategy Officer at Hyperion Solutions prior to forming Dresner Advisory Services in 2007.

Howard is a frequent speaker around the globe and has published two books on the subject – including: Profiles in Performance – Business Intelligence Journeys and the Roadmap for Change (John Wiley & Sons, November 2009) and The Performance Management Revolution: Business Results through Insight and Action (John Wiley & Sons, November 2007).

Through the Wisdom of Crowds Business Intelligence market research reports, Howard engages with a global community to redefine how research is created and shared.

I thought I would share a peek into his report with you on the topic of Success with Business Intelligence and Technology Priorities.

Mr. Dresner notes that with a few exceptions, reports of success with BI deviate little based on specific technology priorities. Those claiming success are slightly more likely to favor in-memory analysis and data mining and advanced algorithms. Those that are less successful were more likely to favor big data, analytical applications, software-as-a-service / cloud and location intelligence.

As part of his survey, when asked for reasons why business intelligence fails, respondents point to shortfalls and constraints surrounding data that include “tools” and “time,” but also “business,” “organization” and “management”.

Primary reasons for failure include: a lack of management understanding or appreciation of BI, a predominant focus upon technology vs. solving business problems and a lack of skills and resources to deliver solutions.

To visualize the data associated with success and not being successful, Mr. Dresner uses a radar chart (see chart below). A radar chart is a graphical method of displaying multivariate data in the form of a two-dimensional chart of three or more quantitative variables represented on axes starting from the same point. The relative position and angle of the axes is typically uninformative.

The radar chart is also known as web chart, spider chart, star chart, star plot, cobweb chart, irregular polygon, polar chart, or kiviat diagram.

As I delve deeper into the report, I will share other insights with you.

Best regards,


Wisdom of Crowds - Success

An Introduction to Data Blending – Part 3 (Benefits of Blending Data)


In Part 2 of this series on data blending, we delved deeper into understanding what data blending is. We also examined how data blending is used in Hans Rosling’s well-known Gapminder application.

Today, in Part 3 of this series, we will dig even deeper by examining the benefits of blending data.

Again, much of Parts 1, 2 and 3 are based on a research paper written by Kristi Morton from The University of Washington (and others) [1].

You can learn more about Ms. Morton’s research as well as other resources used to create this blog post by referring to the References at the end of the blog post.

Best Regards,


Benefits of Blending Data

In this section, we will examine the advantages of using the data blending feature for integrating datasets. Additionally, we will review another illustrative example of data blending using Tableau.

Integrating Data Using Tableau

In Ms. Morton’s research, Tableau was equipped with two ways of integrating data. First, in the case where the data sets are collocated (or can be collocated), Tableau formulates a query that joins them to produce a visualization. However, in the case where the data sets are not collocated (or cannot be collocated), Tableau federates queries to each data source, and creates a dynamic, blended view that consists of the joined result sets of the queries. For the purpose of exploratory visual analytics, Ms. Morton (et al) found that data blending is a complementary technology to the standard collocated approach with the following benefits:

  • Resolves many data granularity problems
  • Resolves collocation problems
  • Adapts to needs of exploratory visual analytics

Figure 1 - Company Tables

Image: Kristi Morton, Ross Bunker, Jock Mackinlay, Robert Morton, and Chris Stolte, Dynamic Workload Driven Data Integration in Tableau. [1]

Resolving Data Granularity Problems

Often times a user wants to combine data that may not be at the same granularity (i.e. they have different primary keys). For example, let’s say that an employee at company A wants to compare the yearly growth of sales to a competitor company B. The dataset for company B (see Figure 1 above) contains a detailed quarterly growth of sales for B (quarter, year is the primary key), while company A’s dataset only includes the yearly sales (year is the primary key). If the employee simply joins these two datasets on yearly earnings, then each row from A will be duplicated for each quarter in B for a given year resulting in an inaccurate overestimate of A’s yearly earnings.

This duplication problem can be avoided if for example, company B’s sales dataset were first aggregated to the level of year, then joined with company A’s dataset. In this case, data blending detects that the data sets are at different granularities by examining their primary keys and notes that in order to join them, the common field is year. In order to join them on year, an aggregation query is issued to company B’s dataset, which returns the sales aggregated up to the yearly level as shown in Figure 1. This result is blended with company A’s dataset to produce the desired visualization of yearly sales for companies A and B.

The blending feature does all of this on-the-fly without user-intervention.

Resolves Collocation Problems

As mentioned in Part 1, managed repository is expensive and untenable. In other cases, the data repository may have rigid structure, as with cubes, to ensure performance, support security or protect data quality. Furthermore, it is often unclear if it is worth the effort of integrating an external data set that has uncertain value. The user may not know until she has started exploring the data if it has enough value to justify spending the time to integrate and load it into her repository.

Thus, one of the paramount benefits of data blending is that it allows the user to quickly start exploring their data, and as they explore the integration happens automatically as a natural part of the analysis cycle.

An interesting final benefit of the blending approach is that it enables users to seamlessly integrate across different types of data (which usually exist in separate repositories) such as relational, cubes, text files, spreadsheets, etc.

Adapts to Needs of Exploratory Visual Analytics

A key benefit of data blending is its flexibility; it gives the user the freedom to view their blended data at different granularities and control how data is integrated on-the-fly. The blended views are dynamically created as the user is visually exploring the datasets. For example, the user can drill-down, roll-up, pivot, or filter any blended view as needed during her exploratory analysis. This feature is useful for data exploration and what-if analysis.

Another Illustrative Example of Data Blending

Figure 2 (below) illustrates the possible outcomes of an election for District 2 Supervisor of San Francisco. With this type of visualization, the user can select different election styles and see how their choice affects the outcome of the election.

What’s interesting from a blending standpoint is that this is an example of a many-to-one relationship between the primary and secondary datasets. This means that the fields being left-joined in by the secondary data sources match multiple rows from the primary dataset and results in these values being duplicated. Thus any subsequent aggregation operations would reflect this duplicate data, resulting in overestimates. The blending feature, however, prevents this scenario from occurring by performing all aggregation prior to duplicating data during the left-join.

Figure 2 - San Francisco Election

 Image: Kristi Morton, Ross Bunker, Jock Mackinlay, Robert Morton, and Chris Stolte, Dynamic Workload Driven Data Integration in Tableau. [1]

Next: Data Blending Design Principles



[1] Kristi Morton, Ross Bunker, Jock Mackinlay, Robert Morton, and Chris Stolte, Dynamic Workload Driven Data Integration in Tableau, University of Washington and Tableau Software, Seattle, Washington, March 2012, http://homes.cs.washington.edu/~kmorton/modi221-mortonA.pdf.

[2] Hans Rosling, Wealth & Health of Nations, Gapminder.org, http://www.gapminder.org/world/.

Gartner Releases 2014 Magic Quadrant for BI and Analytics Platforms

Gartner Magic Quadrant BI 2014


Gartner has just released its 2014 Magic Quadrant for Business Intelligence and Analytics Platforms.

I need a few days to soak this in and better comment on it. But, for now, I thought I would share the Magic Quadrant with you.

You can see the entire report by clicking here.

Best regards,


Gartner describes and defines the market as follows.

The BI and analytics platform market is in the middle of an accelerated transformation from BI systems used primarily for measurement and reporting to those that also support analysis, prediction, forecasting and optimization. Because of the growing importance of advanced analytics for descriptive, prescriptive and predictive modeling, forecasting, simulation and optimization (see “Extend Your Portfolio of Analytics Capabilities”) in the BI and information management applications and infrastructure that companies are building — often with different buyers driving purchasing and different vendors offering solutions — this year Gartner has also published a Magic Quadrant exclusively on predictive and prescriptive analytics platforms (see Note 1). Vendors offering both sets of capabilities are featured in both Magic Quadrants.

The BI platform market is forecast to have grown into a $14.1 billion market in 2013, largely through companies investing in IT-led consolidation projects to standardize on IT-centric BI platforms for large-scale systems-of-record reporting (see “Forecast: Enterprise Software Markets, Worldwide, 2010-2017, 3Q13 Update”). These have tended to be highly governed and centralized, where IT production reports were pushed out to inform a broad array of information consumers and analysts. While analytical capabilities were deployed, such as parameterized reports, online analytical processing (OLAP) and ad hoc query, they were never fully embraced by the majority of business users, managers and analysts, primarily because most considered these too difficult to use for many analytical use cases. As a result, and continuing a five-year trend, these installed platforms are routinely being complemented, and in 2013 were increasingly displaced, in new sales situations by new investments, and requirements were more skewed toward business-user-driven data discovery techniques to make analytics beyond traditional reporting more accessible and pervasive to a broader range of users and use cases.

Also in support of wider adoption, companies and independent software vendors are increasingly embedding both traditional reporting, dashboards and interactive analysis, in addition to more advanced and prescriptive analytics built from statistical functions and algorithms available within the BI platform into business processes or applications. The intent is to expand the use of analytics to a broad range of consumers and nontraditional BI users, increasingly on mobile devices. Moreover, companies are increasingly building analytics applications, leveraging new data types and new types of analysis, such as location intelligence and analytics on multistructured data stored in NoSQL data repositories.