Monthly Archives: February, 2014

Tips & Tricks #6: How the Incremental Refresh Option Works in MicroStrategy 9.4.x

Starting with MicroStrategy 9.2.1, Intelligent Cubes have a new feature to update information without republishing it. It is referred to as Incremental Refresh. There are different ways each Incremental Refresh type option works.

Defining an Incremental Refresh Report

Prior to MicroStrategy 9.2.1, if the data in an Intelligent Cube needed to be updated, users had to re-publish the Intelligent Cube,  either manually or using a schedule. This process will cause all the data for the Intelligent Cube to be loaded from the data warehouse into Intelligence Server’s memory, so that the existing data for the Intelligent Cube is overwritten.

MicroStrategy 9.2.1 introduced a new feature known as Incremental Refresh Options, which allow Intelligent Cubes to be updated based on one or more attributes by setting up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically.

For example, if a user has an Intelligent Cube that contains weekly sales data, the user may want this Intelligent Cube to be updated at the end of every week with the sales data for that week. By setting up incremental refresh settings, he can make it so that only data for one week is added to the Intelligent Cube, without affecting the existing data and without having to reload all existing data.

Users can select two types of objects for the incremental fetch: a report or a filter.

  • Filter: The data returned by a filter is compared to the data that is already in the cube. By default, the filter defined for the Intelligent Cube is used as the filter for the incremental refresh.
  • Report: The results of a report are used to populate the Intelligent Cube. By default, the report template used is the same as the Intelligent Cube’s template.

In order to set up an incremental refresh report, the user should first right-click on the Intelligent Cube and select Define Incremental Refresh Report:

This will bring up the Incremental Refresh Options editor:

Here, the user can define one of the following Refresh type options:

  • Update:  If new data is available, it is fetched and added to the Intelligent Cube, and if the data returned is already in the Intelligent Cube, it is updated where applicable.
  • Insert:  If new data is available, it is fetched and added to the Intelligent Cube. Data that was already in the Intelligent Cube is not altered.
  • Delete: The data that meets the filter or report’s definition is deleted from the cube. For example, if the Intelligent Cube contains data for 2008, 2009 and 2010, and the filter or report returns data for 2009, all the data for 2009 is deleted from the cube.
  • Update only: If the data available is already in the Intelligent Cube, it is updated where applicable. No new data is added to the Intelligent Cube.

The type of object used for the incremental fetch can be selected in the Advanced tab:

Users simply have to run the incremental fetch report, and this will automatically refresh the data in the Intelligent Cube.

Incremental Refresh Options Examples

In this example, the following database table is used. This is a transaction table for item, status, quantity sold (qty_sold) and transaction number.

Cube definition:

Report Objects:

- Item

- Status

- Transaction Number

- Quantity

Filter: Transaction Number greater than or equal to 100

Initial data:

Data is updated as below on the database side:

Line 2 – qty_sold number is updated

Line 3 – status is altered from confirmed to canceled

Line 4 – newly added

Line 5 – newly added

UPDATE

Insert new rows from report data and overwrite overlapping rows between old cube data and report data.

Line 2 – qty_sold number is updated.

Line 3 – Status canceled row is newly inserted, and line 4, the original data is not modified. For any change for any other attribute, a new line is added and the previous line also persists.

Line 5 – Newly added transaction is inserted.

And the new data with transaction_number 1 is not added because it does not meet the filter criteria to have transaction_number >= 100.

INSERT

Only insert new, non-overlapping rows from report data.

Line 2 – qty_sold number is NOT updated.

Line 3 – Status canceled row is newly inserted.

Line 5 – Newly added transaction is inserted.

And the new data with transaction_number 1 is not added.

DELETE

Remove overlapping rows from old cube data.

Delete Incremental Refresh report is not executed against the warehouse, and executed for Intelligent Cube with the following query. All the data meeting the criteria is deleted.

SQL

Delete from CUBE  IncrementalRefreshTestwhere [Transaction Number]@[transaction_date] >=  100

UPDATE ONLY

Only overwrite overlapping rows from report data.

Line 2 – qty_sold number is updated.

Summary

In summary, when defining an Incremental Refresh report, take the following behavior into consideration.

  • Update/Update only option does not compare all the attribute elements.
  • Delete option is performed on the Intelligent Cube, and data is not compared with the warehouse.

Gartner Releases 2014 Magic Quadrant for BI and Analytics Platforms

Gartner Magic Quadrant BI 2014

Readers:

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,

Michael

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.

MicroStrategy Tops Ventana Research’s 2014 Mobile BI Value Index Research

MicroStrategy Highlighted for the Value its Mobile Solution Provides to Enterprises, with Top Score in Five Out of Seven Categories

Ventana Research Mobil BI 2014TYSONS CORNER, Va., Feb. 20, 2014 /PRNewswire/ — MicroStrategy® Incorporated (Nasdaq: MSTR), a leading worldwide provider of enterprise software platforms, today announced that its Mobile App Platform ranks number one in Ventana Research’s 2014 Value Index for Mobile Business Intelligence (BI) research. Ranked first out of 16 mobile BI vendors, MicroStrategy was cited for the value its solution provides to its customers, with the highest rating in usability, manageability, reliability, capability and validation.

Ventana Research’s Value Index Captures Both IT and Business-Centric Perspectives

According to the Ventana Research report, “The top three companies’ products all provide strong user experiences and functionality, but MicroStrategy stood out across the board, finishing first in five categories and most notably in the areas of the business user experience, mobile application development, and presentation of information.”

“It is clear that today’s mobile workforce requires the ability to access information and analyze data simply, straightforwardly and using an intuitive interface,” said Mark Smith, CEO and Chief Research Officer, Ventana Research. “MicroStrategy’s continued investment and excellent customer examples demonstrate its commitment to mobile BI across smartphones and tablets and helping customers design and deploy applications that provide significant business value but also in a safe and secure manner.”

MicroStrategy President Paul Zolfaghari said: “We are thrilled to earn this number one ranking in Ventana Research’s Value Index for Mobile BI.  Since 2009, MicroStrategy has led the way with its innovative mobile platform, which continues to be highly esteemed by independent analysts and thousands of enterprises worldwide.  Our customers get value quickly from their MicroStrategy Mobile™ solutions, and this recognition underscores how integral the MicroStrategy Mobile App Platform™ is to the future of their businesses.”

Ventana Research’s 2014 Value Index for Mobile BI evaluated vendors and their products in seven categories of requirements. Five categories are product-related, including assessing usability, manageability, reliability, capability, and adaptability. Two categories quantify the customer assurance issues of vendor validation and total cost of ownership and return on investment (TCO/ROI). Ventana Research noted that the largest percentage of organizations using mobile BI place important emphasis on easy access and use of the technology.

“Our mobile solution delivers business-changing mobile apps that make employees more productive and achieve results in less time and at a fraction of the cost,” said Hugh Owen, VP of Mobile Products, MicroStrategy Incorporated.  “In 2011-2012, we saw hundreds of organizations deploying to hundreds of users.  Since early 2013, MicroStrategy’s enterprise mobile apps are now helping thousands of organizations, and these organizations are deploying to thousands and tens of thousands of employees, highlighting the importance of reliability, where we had the highest category rating overall in the Value Index. We’re extremely excited to play such a critical role in the mobilization of the enterprise.”

Ventana Research Mobil BI 2014 Chart

MicroStrategy Mobile Delivers Significant Business Value to Thousands of Organizations

Thousands of organizations have received significant business value from deploying MicroStrategy Mobile apps to support new business processes and mobilize existing operational and information systems. For example, luxury brand retailer GUCCI has been able to conduct business in ways simply not possible before. At the recent MicroStrategy World 2014 conference held in Las Vegas, GUCCI’s CIO Simone Pacciarini delivered a live demonstration of their innovative use of MicroStrategy to deliver a better retail experience for their employees and customers. A video of the GUCCI demonstration is available here: http://youtu.be/z4SzR44BqRw.

Of the thousands of innovative business apps in production, MicroStrategy customers across the globe have also performed ROI analyses to measure the impact of mobile technology on their enterprise.  Customers are noting the many benefits of deploying MicroStrategy Mobile.  For example:

  • AutoTrader.com credits its MicroStrategy Mobile app as being a “game changer” that has transformed how their sales reps work; reps spend less time on meeting preparation and more time developing additional opportunities. AutoTrader.com’s Senior Director of Enterprise Data and Shared Services said, “One of the things we use to measure the value of the MicroStrategy platform is the usage from our dealer customers and our salesforce, and the usage of the platform has grown 40 times.”
  • A global leader in insurance brokering and risk management said it generated an incremental $10M in revenue in the first 90 days of its MicroStrategy Mobile app rollout.
  • A leading Fortune 1000 software company cites a 30 percent increase in deal size associated with the use of its MicroStrategy app for sales reps.
  • A large European healthcare system said it achieved 25 percent time savings using the MicroStrategy app.  The internal reporting team saves one week every month fulfilling report requests. Furthermore, cost savings on paper for one year offset the cost of the MicroStrategy app; thus, the solution not only makes the organization more “green,” it pays for itself.
  • Having rolled out a MicroStrategy app to 10,000 store managers, a large global discount supermarket chain said it achieved time savings of 17 percent—enabling managers to spend more time with their employees, the merchandise business, and customers to provide better service.
  • One of the largest international airports said it saved 4,000 person hours a year with the use of its MicroStrategy app for operations analysis. The app has increased the level of accountability, transparency and efficiency within the airport’s various business units and has reduced paper consumption by 50 percent.

To access a free copy of the report, visit: http://www.microstrategy.com/ventana-mobile-bi-value-index-2014.

Tips & Tricks #5: Designing MicroStrategy Documents for Exporting to Excel

Overview

The following best practices will help ensure that your document is displayed correctly when it is exported to Microsoft Excel. When you export to Excel, the resulting spreadsheet looks like a PDF of the document. For example, objects have the same position and size in Excel that they do in a PDF.

For steps to export a document to Excel, see the section Exporting a document in MicroStrategy Web later in this blog entry.

When designing a document that might be exported to Excel, do the following to ensure that the document is displayed correctly in Microsoft Excel:

  • Know how different object types are exported and displayed in Excel, as shown in the following table:

Tip5-1

  • Choose Excel-compatible colors for all objects, including panels, shapes, and Grid/Graphs. Use the set of 40 colors that appear in the Color dialog box in the Document Editor. Excel supports these 40 colors in addition to many more. Other colors are matched by Microsoft Excel as closely as possible. Avoid using gradient colors, since they are not exported to Excel.

Tip 5-2

1 Excel Fill Colors

Tip5-3

2 Excel Font Colors

  • Use graph styles that are supported by Microsoft Excel. For example, if you include a Gauge graph in the document, it is not displayed in Excel. If you include a Combination graph, the exported version in Excel may not be displayed exactly like the original graph in MicroStrategy Web.
  • Avoid overlapping objects. When exported, the document may not be displayed correctly. For example, an object in the background of the document may be displayed in the foreground of the Excel spreadsheet.
  • Provide extra space around objects because they may increase in size when the document is exported to Excel.
  • Use text field borders to create lines and rectangles. Standard MicroStrategy line and rectangle controls may not be displayed correctly in Excel. You can also use a panel stack to create a colored background; for instructions to create panel stacks, see the Dashboard Creation Guide.

Tip5-4

3 Excel Border (Line) Colors and Weights

Avoid inserting line breaks within text fields. Line breaks (inserted by typing CTRL+ENTER) are not rendered in Excel.

  • Do not enable word-wrapping in a column header on a Grid/Graph in MicroStrategy. If you do so, the headers are not displayed correctly in Excel or PDF. Enable word-wrapping in Excel after you export the document.

Tip5-5

4 Turning off Word Wrap in MicroStrategy

Tip5-6

5 Turning off Word Wrap in Excel

  • Use an absolute file path to define the location of an image used in a document. Do not use a relative file path. Images in documents specified with paths relative to MicroStrategy Web and Intelligence Server are not displayed when exported to Excel.

Exporting a document in MicroStrategy Web

Exporting a document allows you view and interact with the document results outside of MicroStrategy Web. For example, you can view results in a PDF file within Adobe Reader, manipulate exported data in an Excel spreadsheet, or create an interactive Flash dashboard for off-line use.

You can export a document to the following formats:

  • PDF file: Export the document to a PDF file in a reader, such as Adobe Reader. You can view the PDF on any device with a PDF reader, such as another computer, a Linux machine, a Nook, or a Kindle.
  • Excel spreadsheet (.xls): Export the document to a Microsoft Excel spreadsheet for further manipulation and use. See the Document Creation Guide for tips to create a document that will display correctly when it is exported to Excel.
  • HTML file: Export the document to an html file in a separate window.
  • Flash file: Export the document to a fully interactive, stand-alone Flash file. All the Flash files in a project are exported in one of the following formats:
  • MHT format, which can be opened in Internet Explorer, and in Firefox with a third-party plug-in

The document designer determines the Flash file format.

You are prompted for the following when you export a document, unless the document designer has specified the default exporting options for the document:

  • If your document is grouped, you can choose to export the entire document or only the selected group element. Page-by allows you to view the document by a selected group element.
  • If your document contains multiple layouts, you can choose to export the entire document or only the layout currently being viewed by the user.
  • You can export a document from within the document, or directly from a folder. The following procedures describe both tasks. You can also export a single Grid/Graph to a PDF file or an Excel spreadsheet.

To export an open document

  1. In MicroStrategy Web, click the name of the document to execute it.
  2. From the Home menu, select Export, then select the file format in which to export the document. The options are:
    • Excel
    • HTML
    • PDF
    • Flash

       If an export format is not available on the toolbar, that export format has not been made available for this document.

3. Depending on the file type, you may be prompted to open or save the exported file. You can choose to either:

  • Open the file, by clicking Open. A copy of the document opens in a browser.
  • Save the file, by clicking Save. Name and save the file. Do not change the file type.

To export a document directly from a folder

  1. In MicroStrategy Web, right-click the document and select Run as.
  2. If the document contains prompts, answer them and click Export.
  3. If DHTML is disabled, click the Export icon or PDF icon in the Actions column.

*        If an export format is not available on the toolbar, that export format has not been made available for this document.

4. If DHTML is enabled, select the format to export to:

    • To export to a PDF, select Export to PDF.
    • To export to Microsoft Excel, select Export to Excel.
    • To export to HTML, select Export to HTML.
    • To export to Flash, select Export to Flash.

5. Depending on the file type, you may be prompted to open or save the exported file. You can choose to either:

    • Open the file, by clicking Open. A copy of the document opens in a browser.
    • Save the file, by clicking Save. Name and save the file. Do not change the file type.

Exporting a Grid/Graph from a document

You can export a single Grid/Graph displayed in a document to either a PDF file or an Excel spreadsheet. This provides additional flexibility to share and print your data, so that you do not have to export the entire document.

The document must be displayed in Express or Flash Mode. In Express Mode, the title bar of the Grid/Graph must be displayed. The following procedures describe both tasks.

*         A widget is exported as a Grid/Graph.

To export a Grid/Graph from Express Mode

  1. In MicroStrategy Web, open the document in Express Mode.
  2. Click the icon on the title bar of the Grid/Graph and point to either Export to PDF or Export to Excel.
  3. If you choose PDF, the PDF file opens in another browser window.
  4. If you choose Excel, you are prompted to open or save the exported file. You can choose to either:
  5. Open the file, by clicking Open. A copy of the document opens in another browser window.
  6. Save the file, by clicking Save. Name and save the file. Do not change the file type.
  7. In MicroStrategy Web, open the document in Flash Mode.
  8. Hover your cursor over the Grid/Graph. Icons for exporting to Excel and to PDF are displayed.
  9. Do one of the following:

To export a Grid/Graph from Flash Mode

  • To export the Grid/Graph to Excel, click the Export to Excel icon. You are prompted to open or save the exported file. You can choose to either:
  • Open the file, by clicking Open. A copy of the document opens in another browser window.
  • Save the file, by clicking Save. Name and save the file. Do not change the file type.
  • To export the Grid/Graph to PDF, click the Export to PDF icon. The PDF file opens in another browser window.

Commentary: Some Thoughts on my MicroStrategy v9.4.1 Upgrade Installation on my Laptop – PART 3

Readers:

I am back to continue and finish this three-part commentary about installing MicroStrategy v9.4.1 on my laptop.

Just also wanted to let you know that v9.4.1 Hotfix 2 was released on 02/12/2014 and is now available. I recommend you get your v9.4.1 GA version all up and running properly before you consider installing the Hotfix.

Best regards,

Michael

Reviewing Upgrade Prerequisites

Before you begin upgrading your MicroStrategy system, it is always a good practice to review the MicroStrategy Readme document so that you are aware of any changes from previous releases. You should also review the system prerequisites outlined in the Planning Your Installation chapter of the MicroStrategy Installation and Configuration Guide.

If you do not review the MicroStrategy hardware and software requirements before upgrading, you may experience problems with the upgrade.

Database and Driver Requirements

Refer to Certified and Supported Configurations in the MicroStrategy Readme for updated information about specific database and driver combinations certified by MicroStrategy.

System Sizing Guidelines

There are several factors to consider when you initially set up your MicroStrategy system. These factors include the number of users that will access the system, report complexity, and whether or not you should employ caches. You should periodically re-evaluate your system and update it based on actual system performance and use.

In particular, before updating your metadata (see the Update the Metadata section below), MicroStrategy recommends that you have an amount of free disk space equal to at least twice the on-disk size of the metadata database.

A complete discussion of system sizing guidelines is beyond the scope of this blog entry. Refer to the Planning Your Installation chapter of the MicroStrategy Installation and Configuration Guide for the latest details about sizing your system.

Due to performance improvements and enhancements, MicroStrategy version 9 may require more memory than version 8 for comparable functionality. In particular, if your MicroStrategy 8.x system is running on Windows and is approaching the 3 GB Windows memory limit, you may need to upgrade your Intelligence Server machines. For more information on MicroStrategy memory recommendations, see the system requirements in the MicroStrategy Readme and the Tuning chapter in the System Administration Guide.

Privileges and Access

Before upgrading, ensure you have the following:

  • If you are installing on a Windows system, you must have a login account with administrative privileges for the domain or target machine.
  • MicroStrategy Intelligence Server installation files. You can access the installation files from a disk or from a network location.
  • Write permissions in the installation directory; otherwise the installation/upgrade process fails.
  • If you have purchased a CPU-based MicroStrategy license and are installing on UNIX or Linux, you need root access permissions for installation.
  • A license key from MicroStrategy for the version of the MicroStrategy software that you are installing.

Checking for Supported Data Types

MicroStrategy Analytics Enterprise supports a wide variety of data types for each supported warehouse database. However, some pre-Analytics Enterprise projects may contain data types not supported in MicroStrategy Analytics Enterprise. If a project containing columns with unsupported data types is upgraded, the data types for those columns are assigned as “reserved,” and proper data types are not assigned in temporary tables. This affects report execution.

Before proceeding with the upgrade, you must ensure that all data types assigned in the pre-Analytics Enterprise project are supported in MicroStrategy Analytics Enterprise.

See the MicroStrategy Project Design Guide for a listing of the supported data types for each database type and additional information about changing to supported data types.

Backing up the Metadata

Although the MicroStrategy installation process itself does not affect your project’s metadata, MicroStrategy recommends that you back up your metadata before any significant installation or upgrade. In most major MicroStrategy upgrades, a metadata update is required for all the pre-existing projects in your metadata. Once you update your metadata project, you cannot revert that metadata to a previous version. Therefore, MicroStrategy strongly recommends that you perform a full database backup of your original metadata prior to the upgrade.

MicroStrategy strongly recommends that you also tape backup, image, or ghost the production server before upgrading.

If you want to keep an old MicroStrategy Tutorial metadata repository and warehouse from a previous MicroStrategy version, rename the Microsoft Access files or move them to another location; otherwise, they are overwritten during the installation process. The Access files are installed by default in the MicroStrategy\Tutorial Reporting folder.

Updating the Project Metadata

MicroStrategy requires that you use the Configuration Wizard to update a metadata project created in a pre-Analytics Enterprise version of MicroStrategy to the latest version.

Be aware of the following:

  • If you are upgrading a MicroStrategy 8.x metadata that is stored in a DB2 UDB for z/OS database, refer to MicroStrategy Tech Note TN32695.
  • For assistance with updating MicroStrategy metadata projects from versions prior to MicroStrategy version 8.1.0, contact MicroStrategy Technical Support.

MicroStrategy requires that you update projects through an Intelligence Server connection (3-tier). Upgrading your project using a direct ODBC connection (2-tier) is not supported.

If you do not upgrade the metadata to the latest version, certain features will not work as expected. For example, if MicroStrategy Web Analytics Enterprise connects to a pre-Analytics Enterprise metadata through an Analytics Enterprise server, Change Journaling, Distribution Services, and some Report Services enhancements may not be available.

Downgrading Metadata Projects

Downgrading a MicroStrategy metadata or project to any previous product version is not supported. Once you update the project metadata to the latest version, you cannot downgrade to earlier product versions. Therefore, backing up the metadata is an essential step in the upgrade process as it allows you to revert to a backup version of the metadata, if necessary, to obtain pre-update versions of the projects it contains.

Configuring an Upgrade Test Environment

Your MicroStrategy environment includes multiple variables, such as security requirements, performance requirements, and VLDB settings, that are unique. MicroStrategy cannot anticipate all the ways these variables may interact with the upgrade process. Thus, MicroStrategy recommends you create a test environment and upgrade that environment first, then thoroughly test the upgraded installation. Once the tests are complete, then upgrade your production environment. This ensures that the upgrade of your production environment proceeds smoothly and any unexpected difficulties do not require additional downtime.

I will post a blog in the near future about testing your upgraded environment.

If you do not want to create a test environment, MicroStrategy recommends that you create and save an Integrity Manager integrity test baseline of your reports and documents. You can then execute an integrity test against this baseline when the upgrade is complete, to ensure that the upgrade has not altered any of your report results. For detailed information about using Integrity Manager to execute integrity tests, see the Integrity Manager chapter of the MicroStrategy System Administration Guide.

Best Practices for Configuring an Upgrade Test Environment

MicroStrategy recommends that you follow these best practices for configuring your upgrade test environment:

  • Do not modify any existing configuration objects. If you need additional configuration objects for testing, you can either create additional objects, or duplicate an existing object and modify it. This applies to database instances, connections and logins, security filters, users and user groups, and security roles.
  • If your production environment is clustered, then your test environment should also be clustered.
  • If your test and production data warehouses have different database table prefixes, make sure you are using the correct prefixes in the test environment’s Warehouse Catalog.
  • Create an integrity test comparing reports from the upgraded test environment with the same reports in the production environment, so that you can easily see where any differences are.
  • If possible, plan to execute data integrity and performance load tests against the production warehouse. This ensures that the test scenarios are as representative of the production environment as possible.
  • If you are creating reports and documents specifically for an upgrade integrity test, create those reports and documents before you duplicate the production metadata.
  • If you are using connection mapping for users to access the data warehouse, check to be sure that all users can log in to the test data warehouse, since user passwords may differ between the test warehouse and the production warehouse.

One way to manage this is to create a new generic database login, and then use the following sample Command Manager script to change users’ connection mappings to use this new login:

ALTER CONNECTION MAP FOR USER “

username” DBINSTANCE “production_warehouse_instance” DBLOGIN “test_login” ON PROJECT “project“;

For steps to use Command Manager, see the Command Manager Help, or the Command Manager chapter of the MicroStrategy System Administration Guide.

  • If you are using Narrowcast Server, make sure that the database copy of the Narrowcast repositories is not used when setting up the Narrowcast Server test environment. Instead, make a copy of the repositories with the Copy Repository utility included with Narrowcast Administrator and use this copy. This ensures that the test environment does not accidentally refer to a production server. For detailed instructions on creating a copy of the Narrowcast repositories, see the Narrowcast Server Upgrade Guide.

High-level Steps to Configure an Upgrade Test Environment

To ensure that your tests accurately reflect the upgrade experience, the upgrade test environment should reflect the production environment as closely as possible.

To Configure a Test Environment

  1. Set up the hardware for the environment. MicroStrategy recommends that this hardware duplicate the configuration of the production environment as closely as possible.
  2. Install your current version of MicroStrategy in the test environment.
  3. Using the Project Duplication Wizard, duplicate the production metadata into the test environment. For instructions on using the Project Duplication Wizard, see the Managing Your Projects chapter of the MicroStrategy System Administration Guide, or see the Project Duplication Wizard Help.
  4. Make sure that your test environment Intelligence Server is connected to your test environment metadata, and not your production metadata.
  5. If you do not intend to execute your tests against a production warehouse, duplicate the production warehouse, and ensure that the test environment points to the duplicate warehouse and not the production warehouse.
  6. Upgrade the test environment.
  7. Test the upgrade. Again, a future blog topic.

Upgrade Deployment Tests

Deploying the upgrade involves installing, activating, configuring, and running the upgrade processes for Intelligence Server, MicroStrategy Web Server, and MicroStrategy Mobile Server, as well as for the metadata, Narrowcast Server, and Enterprise Manager data repositories. These changes, as well as any other procedures that alter the production environment, should be tested when setting up the test environment.

Deployment tests should be performed by MicroStrategy administrators who normally have the responsibility of tuning and monitoring the MicroStrategy installation.

 

Reference Materials

Some detailed information about installing and configuring MicroStrategy products is beyond the scope of this blog entry and can be found in the MicroStrategy Installation and Configuration Guide. The MicroStrategy Installation and Configuration Guide provides detailed procedures on installing and configuring your MicroStrategy system. It also includes important information about installing, deploying, and configuring MicroStrategy Universal products.

In addition, the MicroStrategy Readme contains information about the new products, new features, and bug fixes available in MicroStrategy Analytics Enterprise.

For detailed instructions for upgrading Narrowcast Server, refer to the Narrowcast Server Upgrade Guide.

Tips & Tricks #4: How to Create N-Level Deep Nested Prompts in MicroStrategy Developer (Desktop)

This is something that I actually had the opportunity to use this week. It really came in handy and helped satisfy my client’s requirements.

Just a reminder as we discuss this tip, I am using MicroStrategy v9.4.1 and MicroStrategy Developer. Developer is the new name for MicroStrategy Desktop as of v9.4.1

This example demonstrates how to create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter.

Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts. The following steps describes how to achieve this.

Create the Year Prompt

  1. Create the highest level filter first. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute “Year” and click “Prompt“.
  2. Click “prompt on attribute element list” and then click “Next” through the rest of the screens to accept the default values.
  3. Do not set any additional conditions. Save the filter as “Year Filter“.

Trick4-1

Trick4-2

From this level on, each filter will contain both a prompt on an element list and will have its element list filtered by the filter created one level above it. There is no limit to the number of such filters that can be layered.

Create the Quarter Prompt

  1. Create a filter on attribute “Quarter” and click “Prompt“.
  2. Select “Use a filter to reduce the number of elements” and select the “Year Filter” created in the previous step.
  3. Save this filter as “Quarter Filter” .

Trick4-3

Create the Month Prompt

  1. Follow the same basic procedure as above to create a Month filter. Create a filter on attribute “Month“.
  2. Click “Prompt“.
  3. Select “Use a filter to reduce the number of elements” and select the “Quarter Filter” created in the previous step.
  4. Save this filter as “Month Filter” .

Trick4-4

Try it Out

Place only the lowest level filter in the filter section of the report. In this case, the “Month Filter” will be placed in the filter section.

When the report is executed, it will prompt for Year, then for Quarters in the selected Year, then for Months in the selected Quarter, as shown in the following sequence of screen shots.

Trick4-5

Trick4-6

Trick4-7

Bryan Redux #1: Left Joins in MicroStrategy

Readers:

I am occasionally going to re-blog posts from my friend, Bryan Brandow’s MicroStrategy site.

I consider Bryan one of the best in the business, but his passions lie in other areas these days.

I will denote these blogs by beginning them with “Bryan Redux.” If you want to visit Bryan old site, the URL is http://www.bryanbrandow.com.

Best Regards,

Michael

Bryan posted this on Tuesday, March 22, 2011.

Left Joins in MicroStrategy

Bryan BrandowAn interesting stance by MicroStrategy is that they really push you for proper warehouse modeling (or at least what they consider proper).  At the same time, the tool’s flexibility can really handle just about any model, and I’ve seen the SQL Engine come through in some amazing scenarios where other vendors laughed and walked out.  One commonly requested feature is the ability to left join two tables in a report.  That is, left join Dimension to Fact, not left joining multiple passes.  There are plenty of valid reasons you would need this feature, and for many years I would joke “MicroStrategy can do everything .. except Left Joins”.  Imagine my surprise when I discovered an extremely buried feature that does enable left joins!  I stumbled on this a several months ago and have no idea if it’s been there all along or was introduced recently.  Based on forum and friend activity, not many other people are aware of it either.  Today, I’ll show you the secret.

Build a normal report with Attribute1, Attribute2 and a Metric.  The SQL will come out like this:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key)

join DimAttribute2 a13   on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

But let’s say that you need to left join DimAttribute2 to FactTable.  Simply follow these steps:

Step 1: Edit the Attribute

  1. In the attribute editor, go to Tools -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the third option, Preserve all elements of final pass result table with respect to lookup table but not relationship table.
  3. Update Schema.

Step 2: Edit the Report

  1. In the report editor, go to Data -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the fourth option, Do not listen to per report level setting, preserve elements of the final pass according to the setting at the attribute level.

With those two options combined, the resulting report now generates this SQL:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key) left outer join DimAttribute2 a13 

on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

Conclusion Note that since you need to turn on a report level setting, changing the attribute won’t modify your entire system.  This is nice because you can choose to let some reports to left join on that attribute while not others.  One side effect I have experienced is that this attribute is no longer eligible for Intelligent Cubes. If you can live with that, this becomes a pretty handy trick.

Bryan’s Blog Entry Link:  http://www.bryanbrandow.com/2011/03/left-joins-in-microstrategy.html

Tips & Tricks #3: How to Define Custom Subtotal Displays in MicroStrategy Desktop

By default, when users apply subtotals in a report, the name of the subtotal is displayed in the subtotal line items that appear in the report. Users can use custom subtotals to give more control over the characteristics of a subtotal. Custom subtotals allow users to define custom subtotal line items that appear on the reports.

Users can make the subtotal name dynamic by typing special characters in the subtotal name field as listed in the following table.

Trick2-1

Trick2-2

To define a specific subtotal displays for a report like the one shown above, follow the steps below:

  1. Select Subtotals from the Data menu. The Subtotals dialog box opens. Clear the Totals check box to remove the standard subtotals.
  2. Click Advanced.
  3. Click New to create a custom subtotal.
  4. Type the following for the name: “Total for the #P #0″. Remember that P displays the parent attribute and 0 (the number zero, not the letter o) displays all the forms of the parent attribute. In this case, only one form exists for each, as shown below.

Trick2-3

 

All the metrics on the report are listed. Users can select the subtotal function to use for each. Total is correct for all of the metrics.

  1. Check the Total for the #P #0 subtotal (shown below).
  2. Click Advanced.
  3. Select Across level and then select the Region and the Employee as the levels.
  4. Click OK to save the new subtotal.
  5. Click OK to return to the Subtotals dialog box.
  6. Click OK.

Trick2-4

The report should now look like this.

Trick2-5

Commentary: Some Thoughts on my MicroStrategy v9.4.1 Upgrade Installation on my Laptop – PART 2

MicroStrategy Platform v9.4.1

Upgrade best practices

Review the following recommendations to help ensure the success and stability of your MicroStrategy system and projects when upgrading to the latest version of MicroStrategy.

  1. Follow the upgrade order and recommendations outlined in this section, in particular the upgrade checklist found at The Upgrade Process Checklist in the section below. In particular, always upgrade Intelligence Server prior to upgrading client applications such as MicroStrategy Web or Developer.
  2. Create an upgrade test environment by duplicating your production environment and production metadata. Upgrade this test environment and test it before upgrading your production environment.
  3. Do not downgrade MicroStrategy products or components on a machine to previous versions if you have already installed the most recent version of another MicroStrategy product on that machine.
  4. All MicroStrategy products on a machine must use the same version of MicroStrategy. Do not install or upgrade only some MicroStrategy 9.3.1 products on a machine containing older versions of other MicroStrategy products.
  5. Avoid installing MicroStrategy products using services such as Windows Terminal Services, which create a virtual session on the host machine. Always install MicroStrategy directly on the server machine’s physical interface, or by using a remote connection tool (such as Microsoft Netmeeting or Virtual Private Network) that takes full control of the server machine’s interface.
  6. If you are using clustered Intelligence Servers, then to retain stability in your Intelligence Server cluster while upgrading, shut down Intelligence Server on all nodes in the cluster before proceeding with the upgrade. For more information about clustering Intelligence Servers, see the Clustering chapter in the System Administration Guide.
  7. Every node in the MicroStrategy cluster must run the same version of MicroStrategy for the cluster to work properly.

The Upgrade Process Checklist

The upgrade process described in this section involves the following high-level steps. To help ensure a successful upgrade, follow these steps in the order they are presented in this section.

1. Prepare the MicroStrategy system and projects for upgrade

Preparing a MicroStrategy system for an upgrade involves reviewing information specific to your version upgrade, pre-upgrade information and prerequisites, checking for supported warehouse data types, and backing up the production metadata. It may also involve creating an upgrade test environment that duplicates your production environment.

2. Install and configure Intelligence Server Analytics Enterprise and Developer Analytics Enterprise on a test server

In this step, you install and configure MicroStrategy Intelligence Server Analytics Enterprise and MicroStrategy Developer Analytics Enterprise on a test server and then establish a connection to your production metadata.

3. Update the production metadata

In this step, you update the metadata version of your production projects using the test server environment.

4. Perform basic stability testing

In this step, you perform basic testing to ensure the stability and efficiency of Intelligence Server and your updated projects.

5. Install and configure Intelligence Server in the production environment

Once you are satisfied with the status of the latest version of Intelligence Server, and have updated the projects in your test environment, you install Intelligence Server in the production environment.

6. Install remaining MicroStrategy products in the production environment

With the latest version of Intelligence Server installed in your production environment, you now install and configure the remaining MicroStrategy products in your production environment.

7. Test the upgrade, and perform other post-upgrade tasks

After upgrading to the latest version of MicroStrategy, you perform several post-upgrade tasks such as testing the system, activating your installation, checking system licensing and functionality, managing user privileges, and optimizing your MicroStrategy system.

Next: Reviewing upgrade prerequisites

Commentary: Some Thoughts on my MicroStrategy v9.4.1 Upgrade Installation on my Laptop – PART 1

v9.4.1

Readers:

Last night, I installed MicroStrategy v9.4.1 on my laptop. I already have MicroStrategy v9.3.1 Hotfix 3 on it, but want to start experimenting with some of the new features.

I have a 10 seat license that any legitimate business can download and use for free. The link to apply and download the 10 seat version is here.

Before I talk about some of the interesting components of this install, I want to say that this was the easiest and smoothest install (actually, an upgrade) of any MicroStrategy product I have had. I installed the whole enchilada; Intelligence Server, Web Server, Mobile Server, etc.

I hope you find these notes helpful.

MichaelI have some other really interesting commentaries I am working on and hope to be able to share with you soon.

Best Regards,

Michael

Impact of the Upgrade

My scenario involved the following configuration:

MicroStrategy Software:

  • I already had MicroStrategy v9.3.1 Hotfix 3 installed on my laptop.

Laptop Configuration:

  • Windows 7 Professional with Service Pack 1
  • Intel Core i7 CPU @ 2.20 GHz
  • 64-Bit Operating System
  • 6.0 GB RAM
  • 600 GB Hard Disk

Here are a few name/product changes MicroStrategy Made:

  • MicroStrategy v9.4.1 is referred to now as MicroStrategy Analytics Enterprise.
  • MicroStrategy Desktop is renamed to MicroStrategy Developer.
  • MicroStrategy Distribution Services replaces Narrowcast in v9.x.

Upgrading to MicroStrategy Analytics Enterprise can have a significant impact on your system. The sections below cover some of the specific effects of upgrading.

Client/Server Interoperability

MicroStrategy Analytics Enterprise clients and servers are interoperable with MicroStrategy clients and servers from version 9.0.2 and later. However, full feature support may not be available when the MicroStrategy client and server are on different versions. To ensure full feature support, upgrade all clients and servers to MicroStrategy Analytics Enterprise.

MicroStrategy Analytics Enterprise is not interoperable with pre-9.0.2 releases. That is, clients (such as MicroStrategy Web or Developer) from MicroStrategy Analytics Enterprise cannot communicate with servers (such as Intelligence Server or MicroStrategy Web Server) from pre-9.0.2 releases, and clients from pre-9.0.2 releases cannot communicate with servers from MicroStrategy Analytics Enterprise.

If your system is using a version of MicroStrategy prior to 9.0.2, all clients and servers must be upgraded to MicroStrategy Analytics Enterprise at the same time.

All MicroStrategy products on an individual machine must use the same version of MicroStrategy. In my case, since my laptop is my only environment, I will be upgrading all components to v9.4.1. Do not install or upgrade only some MicroStrategy Analytics Enterprise products on a machine containing older versions of other MicroStrategy products. For example, if you upgrade your Intelligence Server to MicroStrategy Analytics Enterprise, and the Intelligence Server machine contains a copy of Developer, make sure you upgrade Developer on that machine to MicroStrategy Analytics Enterprise as well.

In addition, if a MicroStrategy Web client from a version of MicroStrategy prior to 9.3.1 connects to an Intelligence Server from version 9.3.1 or later, a previous version of the MicroStrategy Web search page is shown. To correct this, either upgrade the MicroStrategy Web client to the latest version, or, in the MicroStrategy Web user preferences, change the default start page to any different page, save the user preferences, change it back to its previous value, and save the user preferences again.

MicroStrategy Mobile Client/Server Interoperability

MicroStrategy Mobile clients from MicroStrategy Analytics Enterprise can communicate with Intelligence Server or MicroStrategy Mobile Server from pre-9.2.0 releases. However, full feature support may not be available when the MicroStrategy Mobile client and server are on different versions. To ensure full feature support, upgrade all clients and servers to MicroStrategy Analytics Enterprise.

MicroStrategy Mobile Server Analytics Enterprise and later is not interoperable with pre-9.2.0 client releases. That is, MicroStrategy Mobile clients and apps from before version 9.2.0 cannot communicate with Intelligence Server Analytics Enterprise or MicroStrategy Mobile Server Analytics Enterprise.

New Features and Workflow Changes

This section describes some of the changes in MicroStrategy Analytics Enterprise and earlier that may affect your users’ workflows.

For a complete list of new products, new features, and updates in MicroStrategy Analytics Enterprise, see the MicroStrategy Tech Note “New Features in MicroStrategy Analytics Enterprise.”

The default options for VLDB settings may change between releases. You can determine what VLDB default settings have changed by creating a VLDB settings report for your database type before the upgrade, and comparing it to a VLDB settings report created after the upgrade. For instructions on how to create a default VLDB settings report, see the section on Default VLDB settings for specific data sources in the Supplemental Reference for System Administration.

New features and workflow changes in MicroStrategy Analytics Enterprise

Some of the new features of MicroStrategy Analytics Enterprise that may affect your users’ workflows include:

  • The name of MicroStrategy Desktop has been changed to MicroStrategy Developer.
  • The following predefined security roles have been renamed:
    • The Desktop Analyst security role has been changed to Analyst.
    • The Desktop Designer security role has been changed to Developer.
  • The following privilege groups have been renamed:
    • The Desktop Analyst privilege group has been changed to Analyst.
    • The Desktop Designer privilege group has been changed to Developer.

New features and workflow changes in MicroStrategy 9.4

Some of the new features of MicroStrategy 9.4 that may affect your users’ workflows include:

  • In a document, if you no longer display an attribute that is used to sort data, the data is no longer sorted by that attribute. You can still select that attribute to sort data.
  • When importing data from a file, the Select Linking Object panel is now the Select Attribute Form dialog box.
  • When creating a new Visual Insight dashboard, the Dataset Objects panel is now opened by default.
  • When creating a new Visual Insight dashboard, you are no longer immediately prompted to select a visualization type to add to the dashboard. Instead, a blank visualization is added to the dashboard and displayed.
  • The menu options for adding a new metric to a Visual Insight dashboard have been reorganized. For detailed steps to add new metrics to a Visual Insight dashboard, see the MicroStrategy Web Help.
  • When defining a threshold condition to display data in a visualization, to create a new metric value band, click the horizontal slider bar in the Thresholds Editor.
  • When enabling a visualization to update the data displayed in another visualization, the Enable Filtering on Selection option is selected by default.
  • The options to export a Visual Insight dashboard are now available in the Tools menu, under Export.
  • The Graph Matrix visualization has been combined with the Graph visualization.
    • The graph styles previously available for the Graph Matrix visualization (Bar, Area, Line, Scatter, Bubble, and Grid) are now available as styles for the Graph visualization.
    • In the Graph panel, the Rows and Y-axis areas have been combined into the Vertical Axis area.
    • In the Graph panel, the Columns and X-axis areas have been combined into the Horizontal Axis area.
  • In a Grid visualization, in the Properties panel, the Fit To option is now the Width option.
  • Integrity Manager now retrieves all rows of a report or document at once.
  • In MicroStrategy Office, many locales no longer require the Microsoft Office Multi-Lingual User Interface (MUI) to correctly display prompt values during internationalization.
  • In MicroStrategy Office, if you add multiple outline reports with dynamic grouping to a single Excel worksheet, all those outline reports retain their dynamic grouping. Previously, only the first outline report retained its dynamic grouping.

New features and workflow changes in MicroStrategy 9.3.1

Some of the new features of MicroStrategy 9.3.1 that may affect your users’ workflows include:

  • In Windows, the MicroStrategy folder in the Start menu has been replaced by two other folders. The MicroStrategy Products folder contains the following items:All other MicroStrategy applications can be found in the MicroStrategy Tools folder in the Start menu.
    • Command Manager
    • Developer
    • Integrity Manager
    • Object Manager
    • System Manager
    • MicroStrategy Web
  • In MicroStrategy Web, the look and feel of the interface has been updated. A new navigational icon bar has been added to the Web interface, with options to create quick dashboards, reports, documents, and more. For detailed instructions on using this new interface, see the MicroStrategy Web Help.
  • In MicroStrategy Web, on the toolbar, the floppy disk is now Save for reports and documents. Previously the floppy disk was Save As.
  • In MicroStrategy Web, to share a link to an object, in your personal folder, right-click the object and select Share, then click Email Link.
  • Visual Insight analyses are now referred to as quick dashboards.
  • In Distribution Services, the Use Send Now privilege is no longer required to send a preview of a subscription. The new privilege Use Send A Preview Now is now required to send a preview of a subscription.
  • The MicroStrategy SDK is no longer available to be installed with the MicroStrategy release. The most recent version of the MicroStrategy SDK is available as a free download from the MicroStrategy support site https://resource.microstrategy.com/msdz/default.asp.
  • Update packages can now be hosted on remote servers. Prior to MicroStrategy 9.3.1, update packages were required to be hosted on the Intelligence Server machine. For information about importing update packages, see the Managing Your Projects chapter of the MicroStrategy System Administration Guide.
  • If you are upgrading from MicroStrategy 9.2.1m or earlier, some widgets that were previously created to display on Android tablets may display as grids or graphs on the mobile device. To display these widgets correctly on Android tablets, see Updating Android widgets from MicroStrategy 9.2.1m.

Next: Upgrade Best Practices

Follow

Get every new post delivered to your Inbox.

Join 119 other followers