Introducing Adobe Brackets 1.0 and Extract for Brackets (Preview)

Readers:

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,

Michael

brackets_logo_flat

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.

ExtractBrackets_2

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.

ExtractBrackets_3

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.

————————————————————

Sources:

NEW! MicroStrategy Tips & Tricks Quick Reference Page

Readers:

To make it easier to find all of the MicroStrategy Tips & Tricks I posted, I have added a new page that contains a quick reference to all of them (see screenshot below).

I will update this page after every five new tips & tricks have been added.

Best Regards,

Michael

Tips and Tricks Quick Reference

Information is Beautiful Awards 2014 Announced

IIB_Awards_942_FB

 

Last Wednesday, November 12th, 2014, the third annual Information is Beautiful Awards celebrated data visualization at its best. Hundreds of entries were trimmed to an elite set of outstandingly illuminating infographics, over which the judges deliberated long and hard. Now, with thanks to their generous sponsors Kantar, here are the winners.

Data Visualization

rappers_459

Gold – Rappers, Sorted by Size of Vocabulary by Matthew Daniels

Silver – Weather Radials Poster by Timm Kekeritz

Bronze – The Depth of the Problem by The Washington Post

Special mention – The Analytical Tourism Map of Piedmont by Marco Bernardi, Federica Fragapane and Francesco Majno

 

Infographic

creativeroutines_459

Gold – Creative Routines by RJ Andrews

Silver – Game of Thrones Decoded by Heather Jones

Bronze – The Graphic Continuum by Jonathan Schwabish and Severino Ribecca

 

Interactive

The Refugee Project by Hyperakt and Ekene Ijeoma

Gold – The Refugee Project by Hyperakt and Ekene Ijeoma

Silver – How Americans Die by Bloomberg Visual Data

Joint Bronze – Commonwealth War Dead: First World War Visualised by James Offer

Joint Bronze – World Food Clock by Luke Twyman

 

Motion infographic

nyctaxis_459

Gold – NYC Taxis: A Day in the Life by Chris Whong

Silver – Beyond Bytes by Maral Pourkazemi

Bronze – Everything You Need to Know about Planet Earth by Kurzgesagt

Special mention – Energy by Adam Nieman

 

Website

selfie_459

Gold – Selfiecity by Moritz Stefaner

Silver – OECD Regional Well-Being by Moritz Stefaner

Bronze – After Babylon by Sofia Girelli, Eleonora Grotto, Pietro Lodi, Daniele Lupatini and Emilio Patuzzo

 

Tool

raw_459

Gold – RAW by Density Design Research Lab

Silver – Kennedy by Brendan Dawes

Bronze – Figure it Out by Friedrich Riha

 

Student

Sam Slover, Wrap Genius

Sam Slover, Wrap Genius

 

Individual

Brendan Dawes, Kennedy

Brendan Dawes, Kennedy

 

Studio

womeninscience_459

FFunction, Women in Science and HP What Matters

 

Corporate

biobased_459

Schwandt Infographics, Biobased Economy

 

Community

The Rite of Spring by Stephen Malinowski and Jay Bacal

The Rite of Spring by Stephen Malinowski and Jay Bacal

 

Most Beautiful

raw_459

RAW by Density Design Research Lab

Interview Question #10: Placing Hierarchies in Report Template in MicroStrategy

Question

Which of the following about placing hierarchies in the report template is true?

A. You can only place one hierarchy in the report template.

B. The report level is always resolved to the lowest level attribute in the hierarchy.

C. You can use user hierarchies as well as the System Hierarchy on the template.

D. For the SQL Engine to resolve the level of the report, one or more attributes from the same hierarchy should be referenced in the report filter.

E. Both B and D.

 

 

 

 

 

Answer

D. For the SQL Engine to resolve the level of the report, one or more attributes from the same hierarchy should be referenced in the report filter.

 

Why the others are false

A. You can only place one hierarchy in the report template. You can place any number on the report.

B. The report level is always resolved to the lowest level attribute in the hierarchy. Since the hierarchy contains several attributes, the SQL Engine does not automatically know which attribute to place on the result set.

C. You can use user hierarchies as well as the System Hierarchy on the template. Cannot use System Hierarchy.

E. Both B and D. B is False.

MicroStrategy Course Where You Will Learn About This Topic

MicroStrategy Engine Essentials Course

HBR Blog Review: The Core Incompetencies of the Corporation

HBR-logo

Readers:

Gary HamelI am taking the day off of blogging to share with you a very thoughtful and insightful blog from The Harvard Business Review Blog Network written by Gary Hamel (photo right). Mr. Hamel blogged this on October 31, 2014 and it it titled The Core Incompetencies of the Corporation. Mr. Hamel is an influential business thinker, cofounder of Strategos and director of the Management Lab. He latest book is The Future of Management.

I hope you enjoy his blog as much as I did.

Best Regards,

Michael

The Core Incompetencies of the Corporation

Large organizations of all types suffer from an assortment of congenital disabilities that no amount of incremental therapy can cure. First, they are inertial. They are frequently caught out by the future and seldom change in the absence of a crisis. Deep change, when it happens, is belated and convulsive, and typically requires an overhaul of the leadership team. Absent the bloodshed, the dynamics of change in the world’s largest companies aren’t much different from what one sees in a poorly-governed, authoritarian regime – and for the same reason: there are few, if any, mechanisms that facilitate proactive bottom-up renewal.

Second, large organizations are incremental. Despite their resource advantages, incumbents are seldom the authors of game-changing innovation. It’s not that veteran CEOs discount the value of innovation; rather, they’ve inherited organizational structures and processes that are inherently toxic to break-out thinking and relentless experimentation. Strangely, most CEOs seem resigned to this fact, since few, if any, have tackled the challenge of innovation with the sort of zeal and persistence they’ve devoted to the pursuit of operational efficiency. Their preferred strategy seems to be to acquire young companies that haven’t yet lost their own innovation mojo (but upon acquisition most likely will).

And finally, large organizations are emotionally insipid. Managers know how to command obedience and diligence, but most are clueless when it comes to galvanizing the sort of volunteerism that animates life on the social web. Initiative, imagination, and passion can’t be commanded—they’re gifts. Every day, employees choose whether to bring those gifts to work or not, and the evidence suggests they usually leave them at home. In Gallup’s latest 142-country survey on the State of the Global Workplace, only 13% of employees were truly engaged in their work. Imagine, if you will, a car engine so woefully inefficient that only 13% of the gas it consumes actually combusts. That’s the sort of waste we’re talking about. Large organizations squander more human capability than they use.

Inertial. Incremental. Insipid. As the winds of creative destruction continue to strengthen, these infirmities will become even more debilitating. Few companies, though, have made much progress in eradicating them. Most of the recommended remedies—idea wikis, business incubators, online collaboration, design thinking, “authentic” leadership, et al—are no more than minor tweaks. They are unlikely to be any more effective than the dozens of “fixes” that came before them. Remember T-groups, total quality management, skunk works, high performance teams, “intrapreneurship,” re-engineering, the learning organization, communities of practice, knowledge management, and customer centricity? All of these were timely, and a few genuinely helpful, but none of them rendered organizations fundamentally more adaptable, innovative, or engaging. Band-Aids, braces, and bariatric surgery don’t fix genetic disorders.

To build organizations that are fit for the future, we have to go deeper, much deeper. When confronted by unprecedented challenges, like an inflection in the pace of change, the most important things to think about are the things we never think about—the taken-for-granted assumptions that are to us as unremarkable as water is to fish. The performance of any social system (be it a government, a religious denomination or a corporation), is ultimately limited by the paradigmatic beliefs of its members; by the core tenets that have been encapsulated in creeds and reified in structures.

Reflect for a moment on the development of constitutional democracy. Ancient and medieval societies were predicated on the “divine right of kings.” The sovereign was answerable only to God and royal edicts could not be countermanded. Society was ordered in descending ranks of royal privilege and everyone from dukes to peasants “knew their place.” To most of those who lived in this pre-democratic world, the idea of self-government would have been ludicrous, if it could have been imagined at all. Thankfully, a few brave souls like William Penn, Thomas Paine, and Patrick Henry not only imagined self-government, but devoted their lives to making it a reality. Today it’s the imperial alternative that’s unthinkable.

Until we challenge our foundational beliefs, we won’t be able to build organizations that are substantially more capable than the ones we have today. We will fail to build organizations that are as nimble as change itself. We will fail to make innovation an instinctual and intrinsic capability. We will fail to inspire extraordinary contributions from our colleagues and employees.

Most organizations are still feudal at their core, with a raft of institutionalized distinctions between thinkers and doers—between the executive class and everyone else. And most leaders still over-value alignment and conformance and under-value heterodoxy and heresy. Until this changes, our organizations will be substantially less capable than they might be.

This post is part of a series leading up to the 2014 Global Drucker Forum, taking place November 13-14 in Vienna, Austria. See the rest of the series here.

DataViz as Art: The Berlin Wall Endures as a Symbol of Freedom in the USA

Readers:

On the Saturday before Memorial Day last year, Ray Stanjevich decided to attend an auction of an unusual collectible: an 8,000-pound section of the Berlin Wall.

He did not plan to bid on the concrete slab at the sale outside Atlanta last year. Where would he put it? What would he do with it? But he agreed to sign up for an auction paddle. And once bidding began, he joined in. When it was over, he’d bought a large piece of history for $23,500. “I was just gonna watch,” he says. “Sometimes you get overexcited.”

Americans have been excited about the Berlin Wall for a long time. When erected in 1961 to divide democratic West Berlin from communist East, it stood for oppression. After it was brought down in 1989 — 25 years ago this week, amid the crumbling of the Soviet bloc — the wall’s meaning reversed. In pieces, it came to represent freedom.

Today, large sections of the 96-mile-long Berlin Wall are on display in scores of places across the USA. They are in seven presidential libraries, a Chicago Transit Authority station and a Las Vegas casino men’s room. They’re at CIA headquarters, the U.N., and the Hard Rock Cafe Orlando. A slab stands in the shadow of One World Trade Center.

Here are photos of some of the interesting places that hold a part of the symbol of freedom.

Best Regards,

Michael

Source: Rick Hampson, Berlin Wall endures as a symbol of freedom in the USA, USA WEEKEND, October 30, 2014, http://www.usatoday.com/experience/weekend/lifestyle/berlin-wall-endures-as-a-symbol-of-freedom-in-the-usa/17997741/.

Students take a tour to view a large piece of the Berlin

The Ronald Reagan Presidential Library in Simi Valley, Calif., has a piece of the wall on display. Reagan famously called on Soviet leader Mikhail Gorbachev to “tear down this wall” in a 1987 speech in Berlin. FREDERIC J. BROWN, AFP/Getty Images

Berlin Wall Monument (West view) at Central Intelligence

Three sections of the wall can be seen on tours of CIA headquarters in Langley, Va. Justinian Jampol, founder of the Wende Museum, says that when the pieces arrived at the CIA, they were blank on both sides; one side was painted with graffiti to show the East-West disparity. Public Domain Image

People walk on November 9, 2009 in front an original,

The Wende Museum in Los Angeles imported 10 segments of the Berlin Wall in 2009, to mark the 20th anniversary of its fall. The museum installed the pieces on Wilshire Boulevard and invited local artists to paint on some of them. The idea: replicate the wall’s function as a site for political and personal expression. It’s the longest contiguous stretch of the wall in the USA. MARK RALSTON, AFP/Getty Images

Berlin Wall BreakFree sculpture by artist Edwina Sandys

Edwina Sandys, granddaughter of British Prime Minister Winston Churchill, used two pieces of the Berlin Wall to create an outdoor sculpture titled BreakFree.On display at the Franklin D. Roosevelt Presidential Library in Hyde Park, N.Y., the sculpture shows two figures breaking free of coils of barbed wire. Edwina Sandys, Franklin D. Roosevelt Presidential Library and Museum

Berlin Wall BreakFree sculpture by artist Edwina Sandys

Back side of the BreakFree sculpture by Edwina Sandys at the Franklin D. Roosevelt Presidential Library in Hyde Park, N.Y. Sandys is the granddaughter of Winston Churchill. Edwina Sandys, Franklin D. Roosevelt Presidential Library and Museum

Berlin Wall, Reagan Peace Garden at Eureka College

Ronald Reagan Peace Garden, Eureka (Ill.) College: A section of the Berlin Wall sits in the Ronald Regan Peace Garden at Eureka (Ill.) College. Reagan graduated from the college in 1932.The college received the 5-by-4-foot section of the wall as a gift from Germany in 2000, when the college dedicated the peace garden. Eureka College

A piece of the Berlin Wall at the John F. Kennedy Presidential

A piece of the Berlin Wall at the John F. Kennedy Presidential Library and Museum in Boston. Construction of the Berlin Wall started in 1961, when Kennedy was president. Kennedy Library Foundation

Pedestrians walk past "Breakthrough," a sculpture created

Breakthrough, a sculpture created from eight sections of the Berlin Wall by artist Edwina Sandys, is displayed at Westminster College in Fulton, Mo. Sandys is a granddaughter of Winston Churchill, who delivered his anti-communist “Iron Curtain” speech here in 1946. Whitney Curtis for USA TODAY

Pieces of the Berlin Wall on display at the Hilton

Hilton Anatole Hotel, Dallas: Two segments of the Berlin Wall, painted by German artist Jurgen Grosse, were given to the Hilton Anatole Hotel in Dallas in 1990. HILTON ANATOLE

Berlin Wall exhibit - CTA Western station - front<br /><br /><br /><br />
Piece” width=”380″ height=”510″ /></p>
<p>The city of Berlin donated a segment as a “commemorative relic’’ for display at the Chicago Transit Authority’s Western Station, in the traditionally German Lincoln Square neighborhood.  <span class= Chicago Transit Authority

Pieces of Berlin Wall on display at the Newseum in

An exhibit at the Newseum in Washington, D.C., contains the largest display of unaltered portions of the Berlin Wall outside of Germany. The exhibit also features an East German guard tower. Sam Kittner, Newseum

Section of Berlin Wall at Microsoft Conference Center

The Microsoft Art Collection at the company’s campus in Redmond, Wash., contains a section of the wall donated by German automaker Daimler-Benz. Rob Wolf, Microsoft

Pieces of the Berlin Wall are on display at multiple

Pieces of the Berlin Wall are on display at multiple locations of Ripley’s Believe it or Not! Seen here is a piece in the New York location. Ripley Entertainment

A segment of the Berlin Wall is incorporated into a

A 3-ton piece of the Berlin Wall, purchased in 1996 for $17,500, is now in a men’s room at Main Street Station Casino in Las Vegas. Protected by glass, it anchors a row of urinals. Women who want to see the wall can ask a security guard for an escort when the coast is clear. Boyd Gaming

 

Tips & Tricks # 14: How Attribute Roles Work in MicroStrategy

Airport_Gates

Attribute Roles

The support of attribute roles is among the most requested features in the schema/modeling area. It is the result of a common modeling practice where two or more attributes are defined using the same lookup table and column. When these attributes are placed on the same report, the result may return no data. To address this issue, a unique table name alias is required.
This Tips & Tricks post demonstrates a business scenario where two possible solutions are available in MicroStrategy:
  1. Explicit logical table aliasing can be used to generate correct report Structured Query Language (SQL).
  2. MicroStrategy SQL Generation Engine automatically detects tables where the same column is used to create two different attributes.

Business Case Scenario

The Airport Example

A user defines two attributes that have the same definition but play different roles in the business model. In this example, attribute Origin Airport and Destination Airport are defined using the same Lookup Table and Column (Airport_ID).

TN5800-072-0108A.gif

Both attributes share the same forms, or information about them (Description, Location, etc.). In the fact table, however, a separate column exists for each of their roles (Origin_Airport_ID and Destination_Airport_ID).

TN5800-072-0108B.gif

If the user places both attributes on a report to obtain the number of flights originated from ‘MIA’ and arrived at ‘LGA’, an empty result set is returned. The SQL statement tries to obtain the description of an airport that is both ‘MIA’ and ‘LGA’ at the same time (Airport_ID = “MIA” AND Airport_ID = “LGA”), thus generating an empty result set as shown below:
Example

 

NOTE: The SQL for this template is similar to the following:

TN5800-072-0108D.gif

Returning no data as explained previously:

 

SOLUTIONS

1. Explicit Table Alias Definition

The recommended way to model attribute roles in MicroStrategy is using explicit table aliases. This approach gives project architects direct control over the specific attributes to be treated as different roles, as well as their mappings to logical tables. This level of control can ensure consistent Engine behavior.

  1. In MicroStrategy Desktop Schema Objects/Tables folder, right-click on the Table and select ‘Create Table Alias’.

     

  2. A logical table alias is created for LU_AIRPORT as LU_AIRPORT (1). The alias name can be modified. In this example, the alias table is named ‘LU_AIRPORT_ALIAS’.
The two attributes, Origin Airport and Destination Airport, can each have its own lookup table.
In this example, the Destination Airport attribute uses LU_AIRPORT_ALIAS as the primary lookup table. Be sure to uncheck LU_AIRPORT as a source table so no join will be made.
Form Definition for the ID

Form Definition for the DESC

TN5800-072-0108J.gif

The Origin Airport attribute’s definition remains the same, using LU_AIRPORT as the source table for both of its forms. LU_AIRPORT_ALIAS is not checked as a source table.
Form Definition for the ID

TN5800-072-0108K.gif

Form Definition for the DESC

TN5800-072-0108L.gif

Report SQL

TN5800-072-0108M.gif

2. Automatic Attribute Role Recognition

A VLDB property is available at database instance level in the Query Optimizations folder, called “Engine Attribute Role Options.” The option is disabled by default. When enabled, the engine detects columns that support more than one attribute in the same table and automatically creates a separate alias in memory only for each attribute. These automatic aliases are not saved into the metadata and cannot be edited in MicroStrategy Desktop. Thus, while the engine attribute role option makes it more convenient to use attribute roles, it also represents a loss of control over the schema representation that is ultimately used for SQL generation. As a result, some logical schema designs may function incorrectly when using automatic role recognition.
Example

 

REPORT SQL

TN5800-072-0108G.gif

Enabling/Disabling ‘Automatic Attribute Role Recognition’

Attributes are considered candidates for the unique table name alias feature if their attribute forms are defined off the same column of a lookup table. Due to the specific nature of the modeling design described above, and that users may want to create two attributes of the same definition, automatic attribute role recognition is an optional setting.

The Very Large Database (VLDB) ‘Engine Attribute Role Options’ property can be found under Project Configuration/VLDB Properties/Query Optimizations folder in the VLDB Settings.

IMPORTANT NOTES:

1. Attribute roles are intended for lookup tables. Since automatic role recognition effectively splits a logical table into several virtual logical tables, the option should not be applied when a fact table supports more than one attribute on the same column. In that case, some levels of analysis will not be available (that is, reports may return the “Fact does not exist at a level that can support the requested analysis” error). In general there should be no need to have attribute roles on fact tables. However, if it is necessary in a specific scenario, explicit table aliases should be used.
2. The automatic detection option will not work if the attributes in the roles situation are in the same hierarchy, meaning that a child attribute is shared. In the example above, the two airport attributes do not have a common child attribute.
———————————————————————————
Source: Jaime Perez, TN6197: How do Attribute Roles Work in MicroStrategy 8.x?, MicroStrategy Knowledgebase, ‎08-30-2001.

DataViz: Crayola Crayons – How Color Has Changed

Readers:

When I was a young boy, I loved to color with my big box of Crayola Crayons. I would pull out blank sheets of paper and create multi-colored masterpieces (at least my mother said so).

eight_crayons-200x138Crayola’s crayon chronology tracks their standard box, from its humble eight color beginnings in 1903 to the present day’s 120-count lineup. According to Crayola, of the seventy-two colors from the official 1975 set – sixty-one survive. [1]

A creative dataviz type who goes by the name Velociraptor (referred from here as “Velo”) created the chart below to show the historical crayonology (I just made that word up!) of Crayola Crayons colors.

 

crayola_crayon_color_chart-520x520Velo gently scraped Wikipedia’s list of Crayola colors, corrected a few hues, and added the standard 16-count School Crayon box available in 1935.

Except for the dayglow-ski-jacket-inspired burst of neon magentas at the end of the ’80s, the official color set has remained remarkably faithful to its roots!

Ever industrious, Velo also calculated the average growth rate: 2.56% annually. For maximum understandability, he reformulated it as “Crayola’s Law,” which states:

The number of colors doubles every 28 years!

If the Law holds true, Crayola’s gonna need a bigger box, because by the year 2050, there’ll be 330 different crayons! [1]

A Second Version

Velo was not satisfied with his first version, so he produced the second version below. [2]

Crayola Color Chart

A Third Version (and interactive too!)

Click through to the interactive version for a larger view with mouseover color names!

———————————————————————————–

References:

[1] Stephen Von Worley, Color Me A Dinosaur, The History of Crayola Crayons, Charted, Data Pointed, January 15, 2010, http://www.datapointed.net/2010/01/crayola-crayon-color-chart/.

[2] Stephen Von Worley, Somewhere Over The Crayon-Bow, A Cheerier Crayola Color Chronology, Data Pointed, October 14, 2010, http://www.datapointed.net/2010/10/crayola-color-chart-rainbow-style/.

Infographic: Happy Diwali!

Readers:

INDIAN GIRL LIGHTS A DEEPAWALI LAMP IN AHMEDABADWhile called the “Festival of Lights,” Diwali is most importantly a day to become aware of one’s “inner light.” In Hindu philosophy there is an idea of “Atman,” something beyond the body and mind which is pure, infinite and eternal. Today is a celebration of “good” versus “evil”; A day when the light of higher knowledge dispels ignorance. With this awakening comes compassion and joy.

The background story and practices vary region to region. Many people celebrate by lighting fireworks and sharing sweets and candies. Diwali is a holiday celebrated across a vast array of countries and religions. It is celebrated in India, Nepal, Sri Lanka, Myanmar, Mauritius, Guyana, Trinidad & Tobago, Suriname, Malaysia, Singapore and Fiji, by Hindus, Jains, Sikhs and Buddhists.

This informative infographic is from 2012, but I like the information about Diwali it provides and thought of sharing.

Namaste!

Michael

diwali_infographic_final1

Source: Metal Gaia, Happy Diwali!, November 13, 2012, http://metal-gaia.com/2012/11/13/happy-diwali/.

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.

 

Follow

Get every new post delivered to your Inbox.

Join 112 other followers