Introduction
Many companies have both a data warehouse and SAP
Business Intelligence Warehouse (SAP BW). This system
landscape requires an integrated business intelligence (BI)
solution, such as MicroStrategy, that can concurrently access
both SAP BW and the data warehouse effectively. This
chapter describes how MicroStrategy Intelligence Server
integrates with SAP BW by using SAP’s OLAP Business
Application Programming Interface (BAPI) and
MultiDimensional Expressions (MDX).
Pick the Topper
Free Downloads
Reporting Suite
Introduction
The Freeform SQL functionality adds great flexibility to
MicroStrategy’s query and reporting capabilities.
Traditionally, you use the MicroStrategy Engine to generate
SQL to run against one specific relational database to get a
desired report. Starting from 8.0, in addition to generating
reports in the traditional way, you can also use your own
customized SQL statements to generate reports from
operational systems included in a MicroStrategy project. This
capability could save you a tremendous amount of work time
since you do not need to place the data into a data mart or
data warehouse first.
The following graphic is an illustration of the Freeform SQL
Editor, where you define the SQL statement for the report.
Notice the different panels for different purposes. For details
about these panels, please refer to the online help (search for
the “Using the Freeform SQL Editor” topic).
Freeform SQL reporting
The Freeform SQL reporting feature allows you to use your
own SQL statements to access data from various data
sources, including relational databases, Excel files, and flat
files, as long as they are included in the MicroStrategy
environment. Details on how to create Freeform SQL reports
from these data sources are discussed in this chapter as well
as in the online help (search for the “Creating Freeform SQL
reports” topic).
How to use the Freeform SQL reporting feature effectively
depends on your work environment. As with every other
MicroStrategy functionality, before you start using this
feature, you need to assess your particular work situation and
find a way to strike a good balance between project
maintainability and fast-paced development. For example,
building three or four Freeform SQL reports could be very
valuable, but building 100 such reports could make
maintenance and testing very difficult.
Whether you should use the Freeform SQL reporting feature
at all is another question that you should ask yourself. Most
likely, you may want to consider using this feature if you are
in one of the situations discussed as follows.
When should I use the Freeform SQL reporting feature?
If your company is used to creating static reports using
customized SQL to retrieve data from a certain data source,
and especially if your SQL queries have worked well in the
past, then you may want to simply use MicroStrategy to
deploy those reports to your users. There is no need to
recreate the SQL with the MicroStrategy Engine, as is done if
the data is moved to a data warehouse for report generation.
In the same spirit, if you have existing stored procedures that
have proven to be successful, then you can continue to use
them to generate MicroStrategy reports. One important thing
to note is that you must know what exact data the stored
procedure is supposed to retrieve because this information is
essential in building a Freeform SQL report. Specifically, you
need to know the number of columns, column names, and
their data types, all of which are necessary for mapping the
columns to MicroStrategy objects. For examples on different
databases, please see the online help topic “Creating
Freeform SQL reports using stored procedures”.
Another situation for which you might want to use Freeform
SQL reporting is when you need to run queries against a set
of OLTP tables that are not set up for OLAP analysis. As for
all the Freeform SQL reports, connecting to the right data
source is a prerequisite.
SQL query syntax
Well-written SQL queries are the key to building successful
Freeform SQL reports. To take full advantage of the Freeform
SQL reporting feature, MicroStrategy recommends that you
ensure the correctness and validity of your SQL statements
before creating any such reports. MicroStrategy does not
offer consultation or technical support for the syntax or
composition of your SQL queries.
Depending on your needs, you can compose SQL statements
in several ways:
• create new SQL statements from scratch, if you have not
created any before
• use existing SQL statements that you previously defined,
which have proven to be successful in terms of retrieving
data from the data source
• tune the MicroStrategy Engine-generated SQL by
modifying it to suit your needs
This means you can reuse the Engine-generated SQL by
changing some of its clauses or syntax to get a different
result set
Note that you cannot tune the Engine-generated SQL that
involves the use of the Analytical Engine. Typically, the
Analytical Engine comes into play for metric qualification
using analytical functions (such as OLAP functions),
custom group banding, use of the plug-ins, and so on. If
the Analytical Engine is used for any part of the SQL
during the report generation, that part is labeled as “[An
Analytical SQL]” in the report SQL
SQL support
With the Freeform SQL reporting feature, you can use both
single-pass and multi-pass SQL queries to generate reports.
Make sure that you use derived table or common table
expression syntax.
If you have to use derived table or common table expressions,
then you can have only one SELECT clause in your SQL
query. This means that a report SQL statement with Pass 1,
Pass 2, Pass 3..., as can be found in many MicroStrategy
Tutorial reports, cannot yield the desired report, unless you
modify the query using derived tables or common table
expressions. Please check the database that you use to ensure
that derived tables or common table expressions or both are
supported.
Freeform SQL reports vs. standard reports
You can create Freeform SQL reports using your own SQL
queries against a data warehouse, or from an Excel file, or a
flat file (information on how to create these reports is
provided later in this chapter). Although Freeform SQL
reports can only be created on Desktop, once created, they
can be executed from both Desktop and the Web like any
other standard reports. Functions that apply to the execution
and manipulation of standard reports also apply to Freeform
SQL reports, including the following
• formatting
• exporting
• thresholds
• graphing
• Narrowcast Server subscriptions and report execution
• object security
• OLAP services
• prioritization
• Report Services documents
• scheduling
• subtotals
• Web report execution
However, the following features do not apply to Freeform
SQL reports:
• custom group
• consolidation
• transformation
• filter
• save as template/filter
• report as filter
• data marting
Filters
A filter specifies the conditions that the data must meet to be
included in the report results. For information on Filters in
general, please refer to Chapter 5, Filters, in this guide.
You cannot use existing filters in a Freeform SQL report;
however, you can still do filtering by including a Where
clause in the SQL statement. You can even embed prompt
objects in the Where clause, if needed. For example,
where Year_ID=[Year_prompt]
Only two kinds of prompts can be used: value prompts
and element list prompts. For more information,
please refer to the Prompts subsection in this chapter.
In addition, you can use the view filter functionality for
Freeform SQL reports in the same way as for regular reports.
Prompts
A prompt is a MicroStrategy object that allows user
interaction at report run time. For general information on
prompts, please refer to Chapter 9, Prompts, in this guide.
For Freeform SQL reports, only two types of prompts are
supported—value prompts and element list prompts. To add
prompts, you can select from the two options on the Edit
menu in the Freeform SQL Editor:
• Add New Prompt: launches the Prompt Wizard that
allows you to create a new value prompt or an element list
prompt.
Note the following:
– Only project attributes can be used to create prompts
in Freeform SQL reports.
– Any prompt created this way is saved as a normal
object in the metadata.
• Insert Prompt: displays the Open dialog box where you
can select an existing prompt that you have previously
created in the project, either a value prompt or an element
list prompt.
You cannot type the name of an existing prompt
directly into the SQL statement.
Once you exit the Prompt Wizard or the Open dialog box, the
prompt is inserted automatically into the SQL statement at
the current cursor position. If an area in the SQL statement is
highlighted, it is replaced by the prompt name. Prompt
objects appear in the SQL statement in pink and are enclosed
in brackets ([ ] ) if the name of the prompt contains any
space, for example, where Year_ID = Year_prompt and
where Year_ID = [Year prompt].
Drilling
Drilling allows you to look at specific data at levels other than
what is originally displayed on the grid or graph. For regular
reports, you can drill in different directions, such as down,
up, or across attributes, based on the system-generated drill
maps or custom drill maps. For Freeform SQL reports,
support for drilling is limited to only attributes within the
Intelligent Cube.
This functionality is controlled by the “Drill with
Intelligent Cube” privilege.
For example, a report has the Year and Quarter attributes.
When you move Quarter off the report and into the Object
Browser, you can only drill down from Year to Quarter on the
report. However, if both attributes are placed on the report,
you cannot drill from either one of them.
If you move Year off the report and into the Object
Browser, you cannot drill up from Quarter to Year on
the report.
Managed objects
A managed object is just like a normal object except that it is
managed by the system and is stored in a special system
folder. When you create a Freeform SQL report, you can use
existing attributes (including project attributes) to map the
columns in the SQL statement in the Freeform SQL Editor.
Alternatively, you can create new attributes, attribute forms,
and metrics on the fly to which to map the columns in the
SQL statement; these new objects are managed objects.
You can find out whether the object (attribute or metric) is a
managed object or not by using the Properties dialog box. If it
is a managed object, the Location field on the General tab
indicates “Managed by the system” (see the graphic below).
Creating Freeform SQL reports
Freeform SQL reports Freeform SQL reports can be created
on Desktop only. However, these reports can be manipulated
and executed from both Desktop and the Web. This section
describes the process of creating a Freeform SQL report
• from a database
• from an Excel file
• from a flat file
• using a stored procedure
Creating a Freeform SQL report from a database
The process of creating a Freeform SQL report from a
database involves the following general steps:
1 On Desktop, create a database instance for the data source
that the Freeform SQL report will run against.
You must create a database instance before
defining a Freeform SQL report.
2 Make the database instance available for Freeform SQL
reports (in the Project Configuration Editor, select
Project Definition, then Database Instances, and then
the database instance name from the Database Instance
list).
3 Create a Freeform SQL report by selecting New from the
File menu and then Freeform SQL. The Freeform SQL
Editor is displayed.
Access to the Freeform SQL Editor is available only
to Desktop Designers with the “Use Freeform SQL
Editor” privilege and those with the “Create
schema objects” Common Privilege.
4 Below the toolbar, from the database instance drop-down
list, select the database instance against which the
Freeform SQL report is set to query.
5 Type your SQL statement in the SQL statement panel.
6 In the Mapping panel, map the columns in the SQL
statement to MicroStrategy objects (attribute forms and
metrics).
The number of mappings should match the
number of columns in the SQL statement.
7 Insert prompts into the SQL statement, if needed.
8 Insert security filters, if needed.
9 Click OK to exit the Freeform SQL Report Editor. The
Report Editor is displayed.
10 Define the Freeform SQL report in the same way as you
define a standard report, using features such as
formatting, sorting, view filters, thresholds, exporting,
and so on.
11 Save the Freeform SQL report.
You must save the report first before you can run it.
12 Run the report.
Creating a Freeform SQL report from an Excel file
The Freeform SQL reporting feature also allows you to create
reports using data from Excel files. The creation process
involves the following steps.
Create a table with the Excel file
1 Prepare the Excel file.
– Make sure that all the columns with data have proper
headers: no space in the header name (for example,
Category_ID, not Category ID) and are alphanumeric,
starting with alphabets (for example, M2004Q1, not
2004Q1M).
– Make sure that all cells for the ID column have value in
them (not empty).
2 Create a table by doing the following:
– Highlight the rows and columns with the data that you
want to create a report with, including the column
headers, such as Category_ID and Category_DESC.
Do not use the column headings (at the top of the
Excel spreadsheet, marked as A, B, C...) to select
the whole column because doing so may include
numerous empty cells with NULL value.
– Type a name for the highlighted part (rows and
columns) in the name box, and then press ENTER.
You may create multiple tables in one Excel file by
highlighting different parts of the file and naming
them differently.
3 Save the Excel file with a name.
Make sure that the file is not password-protected.
Set up the data source (ODBC)
1 From the Control Panel, select Administrative Tools and
then Data Sources (ODBC). The ODBC Data Source
Administrator dialog box is displayed.
2 Select the System DSN tab, and then click Add. The
Create New Data Source dialog box is displayed.
3 Select the Microsoft Excel Driver if you are using the
Windows platform, and then click Finish. The ODBC
Excel Setup dialog box is displayed.
4 Enter a Data Source Name (DSN) in the space provided.
5 Click Select Workbook. The Select Workbook dialog box
is displayed.
6 Under Database Name, select the Excel file that you saved
and named in Step 1, Prepare the Excel file.
7 Click OK to close the Select Workbook dialog box and
return to the ODBC Excel Setup dialog box.
8 Click OK to return to the ODBC Data Source
Administrator dialog box.
9 Click OK. The ODBC data source is set up.
You can then use the MicroStrategy ODBC Test Tool to test if
data can be retrieved from the table(s) you created from the
Excel file.
Make sure to select the correct DSN for the Excel file
for the test.
Create a database instance for the Excel file
1 On MicroStrategy Desktop, create a new database
instance that points to the DSN for the Excel file.
2 Make the new database instance available for your
Freeform SQL report.
In the Project Configuration dialog box, select Project
Definition, then Database Instances, and then choose
the DSN for the Excel file from the available database
instances list.
Create a Freeform SQL report from the Excel file
1 From the File menu on Desktop or right-click anywhere in
the right panel on Desktop, select New and then Report.
2 In the New Grid dialog box, select Freeform SQL.
3 In the Freeform SQL Editor, from the Database Instance
drop-down list, select the database instance you created
previously (see the “Create a database instance for the
Excel file” subsection).
4 In the SQL Statement panel, type in your SQL query.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics to be used in the
MicroStrategy report.
6 Click OK to close the Freeform SQL Editor. The Report
Editor opens in Design view by default.
7 Format the Freeform SQL report as you do with a
standard report.
8 From the File menu, click Save or Save As.
You must save the report before you can run it.
Otherwise, you will see a Desktop message saying
that the report “cannot be executed unless it is
saved.”
9 In the Save Report As dialog box, enter a name for the
report.
10 Run the Freeform SQL report.
Note the following:
– Column names in the SQL statement have to match
the column headers in the Excel file.
– Case does not have to match, as long as the column
names are correct.
– Do not use the Excel file name as the table name for
the “From” clause. Use the table name instead.
Remember that the Excel file is the data source that
contains the “tables”.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics to be used in the
MicroStrategy report.
Creating a Freeform SQL report from a text file
The Freeform SQL reporting feature also allows you to create
reports using data from text files. The creation process
involves the following steps:
Prepare the text file for MicroStrategy use
1 Make sure that the file type is text file (.txt).
2 Select a correct delimiter, for example, comma.
3 Make sure that field (column) names appear in the first
row of the file and are delimited.
4 Save the text file in a folder, which will be used as the data
source for MicroStrategy reports.
Set up the data source (ODBC)
1 From the Control Panel, select Administrative Tools and
then Data Sources (ODBC). The ODBC Data Source
Administrator dialog box is displayed.
2 Select the System DSN tab and then click Add. The
Create New Data Source dialog box is displayed.
3 Select DataDirect5.0 Text File (version 5.00.00.42) as
your ODBC driver and then click Finish. The ODBC Text
Driver Setup dialog box is displayed.
If you do not have this driver on your machine, you
need to install it.
4 On the General tab, enter a Data Source Name (DSN).
5 In the Database Directory field, provide the path of the
directory where you store the text file.
6 Select Comma as the Default Table Type.
7 Select the Column Names in First Line check box.
8 On the Advanced tab, click Define. The Define File dialog
box is displayed.
9 Select the text file you want to define and click Open. The
Define Table dialog box is displayed.
10 In the Table Information section, in the Table text box
enter the name of the table for the text file, for example,
LU_EMPLOYEE (for LU_EMPLOYEE.txt).
11 Select the Column Names in First Row check box.
12 Click Guess to display all the columns contained in this
table.
13 Click OK to return to the Define File dialog box.
14 Click Cancel to return to the ODBC Test Driver Setup
dialog box.
15 Click Apply and then OK to return to the ODBC Data
Source Administrator dialog box.
16 Click OK. Your data source for the text file is now set up.
17 (Optional) Use the MicroStrategy ODBC Test Tool to test
retrieving data from the table (for the text file).
Make sure to select the correct DSN for the text file.
Create a Freeform SQL report from a text file
1 From the File menu on Desktop, right-click anywhere in
the right panel on Desktop, select New and then Report.
2 In the New Grid dialog box, select Freeform SQL. The
Freeform SQL Editor is displayed.
3 Below the toolbar, from the Database Instance drop-down
list select the database instance you created in the
previous procedure, “Create a database instance for the
Excel file”.
4 In the SQL Statement panel, type in your SQL query.
Note the following:
– Column names in the SQL statement have to match
the field names in the text file.
– Case does not matter, as long as the column names are
correct.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics that will be used in the
MicroStrategy report.
Note the following:
– When mapping the columns, it is important that you
follow the same sequence of the columns as they
appear in the SQL statement. Doing otherwise will
cause the report to fail.
– Make sure that the number of mappings is the same as
the number of columns in the SQL statement. For
example, if your SQL statement lists 10 columns from
which to retrieve data, you should map them to exactly
10 objects (including attributes and metrics).
– For each attribute, you must map the ID form at least.
6 Click OK to close the Freeform SQL Editor. The Report
Editor opens in Design view by default.
7 From the File menu, click Save or Save As.
You must save the report before you can run it.
Otherwise, you will see a Desktop message saying
that the report “cannot be executed unless it is
saved”.
8 In the Save Report As dialog box, enter a name for the
report and click Save.
9 Run the Freeform SQL report.
Report Objects
Report Objects are objects associated with a given report. At
any time, a user can choose to view only a particular set of
those objects. For example, you choose to show Metric1 and
Metric2 but not Metric3 on the template.
Report Objects also indicate the lowest level of detail
available in a report. This is accomplished by looking at the
list of attributes in the Report Objects list. For example, a
report with Year, Month, and Week in Report Objects has
data for the metrics in that report at the Year, Month, and
Week level. A user can choose to view only Year and Month
on the template. In that case, the data is aggregated by
default to the Month level, the lowest level of detail on the
report.
The Reports Objects list is not shown by default in the Report
Editor, although it is displayed on the left side of the Report
Viewer. If it is not automatically displayed, choose Show
Report Objects from the View menu.
Reports
Once you have created your project, set up attributes and
facts, and created a simple metric, you can run a report.
A report is a request for specific formatted data from the data
warehouse. Reports can contain attributes and facts from the
data warehouse, filters to determine how much data is used
to generate the report, and metrics to perform calculations on
the facts. More sophisticated functions, such as report limits,
metric qualifications, dynamic aggregation, and shortcuts to
reports and filters, allow you to create more functional and
informative reports.
Metrics
Metrics are analytical calculations performed against stored
data (facts) to produce results that can then either be read as
status material or analyzed for decision-making purposes. A
metric can be defined within a report to specify the data to be displayed in the report. This data can then be read or analyzed for decision-making purposes.
Many-to-many
In a many-to-many relationship, each element in the parent
attribute is related to more than one element in the child
attribute, and each element in the child attribute is related to
many elements in the parent.
In a car manufacturing plant, many models of cars are
produced, and each comes in several colors. That is, there are
many colors for a single type of car, and many types of cars
can be associated with the same color.
Many-to-many relations can be a particularly tricky data
modeling scenario. Additional information can be found in
Appendix D, Advanced Data Modeling.
One-to-many
In a one-to-many relationship, each element in the parent
attribute is related to more than one element in the child
attribute, and each element in the child attribute is related to
only one element in the parent.
For example, in a relationship between Year and Quarter,
Year is the parent attribute and Quarter is the child attribute.
The following graphic depicts the relationship between the
parent and child.
Attribute relationships
Attribute relationships give meaning to the data in a logical
data model by associating attributes based on business rules.
The types of attribute relationships are
• one-to-one
• one-to-many
• many-to-many
One-to-one
In a one-to-one relationship, each element in the parent
attribute is related to one and only one element in the child
attribute, and each element in the child attribute is related to
only one element in the parent.
Attribute elements
Attribute elements are the data shown on the report. Think of
them as a sub-level of the attribute. For example, City might
be the attribute, whereas London, Milan, and New York are
the attribute elements.
In the data warehouse, attributes are usually represented by
columns in a table, and attribute elements are represented by
the rows.
summarization of data.
Attributes
Once you have determined all the facts necessary to complete
your business model, you identify the attributes for that
model. Attributes act as holders of information, allowing you
to add context to your facts in a report.
For example, if you had $10,000 in revenue, that number
does not mean anything in a business sense unless you know
the context, such as which region, the designated time frame
for the sales, and what was the labor involved.
Simply put, attributes provide categories for the summarization of data.
Facts
A fact has two characteristics: it is numerical and
aggregatable. Examples of facts include revenue, inventory,
and account balances.
There are some cases where a fact is not numerical or
aggregatable, but these are rare.
Facts are stored in the data warehouse in fact tables. These
fact tables comprise different columns, each cell representing
a specific piece of information. Metrics, which are business
measures, are created from this information.
SQL aggregations, such as SUM and AVG, are performed on
the facts in the database tables. For example, in the following
SQL statement, the ORDER_AMT column in the warehouse
might correspond to the Order Amount fact in the
MicroStrategy environment:
SELECTsum(a21.ORDER_AMT) REGION
FROM ORDER_FACTa21
JOIN LU_EMPLOYEEa22
ON (a21.EMP_ID = a22.EMP_ID)
WHERE a22.CALL_CTR_ID in (5, 9, 12)
In this example, ORDER_AMT is the fact, whereas
sum(a21.ORDER_AMT) represents a metric.
Configuration objects
Administrative and connectivity-related objects, also called
configuration objects, are managed in MicroStrategy Server
Administrator by an administrator role. Examples of
configuration objects include users, groups, server
definitions, and so on.
Application objects
The report designer creates the application objects necessary
to run reports. Application objects, which are developed in
MicroStrategy Desktop and Web, are the building blocks for
reports and documents. These application objects include
reports, report templates, filters, metrics, prompts, and so
on.
Schema objects
Schema objects are created usually by a project designer.
Schema objects relate the information in the logical data
model and physical warehouse schema to the MicroStrategy
environment. Facts, attributes, and hierarchies are examples
of schema objects. These objects are developed in
MicroStrategy Architect, which can be accessed from
MicroStrategy Desktop.
Metadata database
Once you have a data warehouse populated with all the
information your business needs to succeed, how do you
retrieve the information from the database correctly and in
the least amount of time?
The metadata contains information that facilitates the
transfer of data between the data warehouse and the
MicroStrategy application. It stores the object definitions and
the information about the data warehouse, including its
structure.
MicroStrategy uses the metadata to translate user requests
into SQL queries and to translate the SQL queries back into
MicroStrategy objects, such as reports.
The three types of objects that are stored in the metadata are
• schema objects
• application objects
• configuration objects
Physical warehouse schema
The physical warehouse schema is based on the logical data
model. It is a detailed graphic representation of your business
data. It organizes the logical model in a method that makes
sense from a database perspective.
While the logical data model tells you what facts and
attributes to create, the physical warehouse schema tells you
where the underlying data for those objects is stored. The
physical warehouse schema describes how your data is stored
in the data warehouse.
Two key components make up the physical warehouse
schema—tables and columns. Columns and tables in the
physical warehouse schema represent facts and attributes
from the logical data model. The rows in a table represent
attribute elements and fact data.
Logical data model
The logical data model graphically represents the flow and
structure of data in a business environment. It comprises
facts, attributes, and hierarchies. Facts are numerical and
aggregatable, such as daily revenue, inventory data, and
hours worked. Once you have determined what your facts are,
attributes allow you to answer the questions about a fact,
such as a time frame for specific revenue totals. Hierarchies
are groupings of attributes, ordered to reflect their
relationship with other attributes. For example, you can
group the attributes Year, Month, and Date to form the Time
hierarchy.
Together, these three components—facts, attributes, and
hierarchies—form your logical data model.
Data warehouse
The Installation and Configuration Guide provides guidance
on setting up a robust data warehouse environment. Data
warehouses are generally based on some form of relational
database and can be queried with Structured Query Language
(SQL) to pull information from the warehouse into a report
format. The data stored in the data warehouse originates
from the source systems.
Data warehouses are designed and optimized for analytical
processing. Analytical processing involves manipulating
the data in the warehouse to calculate sales trends, growth
patterns, trend reporting, profit analysis, and so on.
The data warehouse is a large database populated with data
that is stored in tables. These databases have many tables,
tracking many different pieces of information. It is not
necessary to have multiple data warehouses as a data
warehouse can store many databases and tables.
The data in the robust data warehouse can be populated with
data from an existing source system using an ETL process.
ETL takes the data from all sources, which can be spread over
several different locations, and funnels them into one data
warehouse.
ETL process
The extraction, transformation, and loading (ETL) process
represents all of the steps necessary to move data from
disparate source systems to an integrated data warehouse.
The ETL tool is provided by a third-party vendor.
The first step is to extract or retrieve data from source
systems. The second step is to transform the data and prepare
it to be loaded into the data warehouse. Transformation
procedures include converting datatypes and column names,
eliminating bad data, correcting typographical errors, filling
in incomplete data, and so on. The third and final step is to
load the data into the warehouse.
The tools used to perform various aspects of the ETL process
must contain information about the data, which facilitates
the transfer of data from the source systems to the data
warehouse. Specifically, such tools help you to
• store information about the structure and content of both
the source system and data warehouse
• correlate the source system structure and content to that
of the data warehouse
Source systems
Source system refers to any system or file that captures or
holds data of interest. This data is eventually analyzed in
report format to determine answers to business-related
questions.
The source system is the originating point of the data. For
example, if you use your ATM card to conduct a transaction,
the ATM is the point of transaction. It is the place where the
data is gathered. In this example, the ATM gathers the
information about how many dollars you deposited or
withdrew from your account. The data is then written to a
source system that is the large database behind the ATMs.
Deposits, withdrawals, sales transactions, inventory
depletion, or replenishment are referred to as transaction
processing. The source system records the transaction.
Transaction processing data is usually stored in databases or
mainframes for storage retrieval.
Introduction
A report is a MicroStrategy object that represents a request
for a specific set of formatted data from the data warehouse.
Reports are the focus and goal of business intelligence. They
allow users to gather business insight through data analysis.
The different parts of a report include: attributes and facts
from the warehouse, filters that determine how much data is
used to generate the report, and metrics to perform
calculations on the facts.
Report design versus report creation
Report design is the process of building reports from basic
report components in MicroStrategy Desktop and Web.
While report design is the most generic method for defining a
report, it also requires the most in-depth knowledge of the
project. In general, this method should be available only to
the select group of advanced users and report designers who
will design reports for others to use.
Report creation is the process of building reports from
existing, predesigned reports either in Desktop or in Web.
Given the wealth of reporting functionality that you can make
available to your users, you have the ability to design reports
that provide a wide range of options for users to create their
own reports.
Report creation is different from report design in that it
provides a more guided experience and does not require your
users to have a thorough understanding of the project. This
allows your users to create their own reports in a controlled,
user-friendly environment.
Designing reports
You create reports in the Report Editor of Desktop, which has
four report view modes:
• Design View describes the report definition and allows
you to create and edit reports. The attributes, metrics, and
other objects to be used in the report are displayed. You
do not have to execute the report to view or modify the
report structure.
• Grid View offers a formatted, cross-tabular display of the
actual report data after the report is executed.
• Graph View is similar to Grid View, but the display is in a
graphical format instead of cross-tabular.
• SQL View displays the SQL generated by the
MicroStrategy Engine and executed in the warehouse. It
also includes various execution statistics.
MicroStrategy Web provides the same report view
modes, although the equivalent of SQL View is called
Details.
You design reports in Design View, which allows you to select
the metrics and attributes to use on the report. You can also
define report filters, which determine the data used for
metric calculation.
You can add various formatting options, such as fonts and
styles, in either Design View or Grid View.
Interactive report editing
Once a report is saved, you have the option of allowing your
users to edit it interactively while viewing the results without
re-executing the report against the warehouse. This means
that the changes are performed in Desktop or the Intelligence
Server, rather than in the warehouse.
• Pivoting and page-by reorganizes report data by swapping
objects within an axis or by moving objects from one axis
to another.
• Sorting allows you to specify an ascending or descending
order to present the report data for a particular row or
column.
• The View filter restricts the amount of data displayed on
the report, by controlling the subset of data to be
displayed from the data retrieved from the database.
• Derived metrics are calculations defined on-the-fly with
the data available in the report. They are based on existing
metrics in the report to provide simple column math
capability.
• Report Objects contain all of the objects available for
display on the report. Use Report Objects to interactively
modify the content of the report while viewing the report
results. It displays the level of the report data definition.
• Thresholds and stoplights allow you to highlight data that
meets conditions by using different cell formats, symbols,
and images or replacement text.
• Subtotals permit you to add, remove, and edit the
subtotals at different levels for metrics on the report.
• Aliasing is the temporary renaming of objects for the
report display.
• Outline Mode creates an indented grouping of related
attribute elements, allowing you to expand and contract
sections of related data.
• Exporting is rendering the report in different formats or
applications, such as a spreadsheet or a word processor.
What is a filter?
A filter is used to select the data for calculating the metrics in
the report. It also restricts the attribute elements included in
the report. In our example, we use the Month filter, which
does not allow April, May, and December data to be included
in the metric calculations. For our purposes, these months
are not representative of the normal business cycle, so the
filter excludes them from calculations.
In summary, a filter affects the nature of the metric
calculation by restricting the information used to compute
the report metrics.
What is a report limit?
After all the metrics are calculated, you may need to further
restrict the data, without changing how the calculations were
performed. For example, you want to see only the top ten
employees from a report that ranks all the employee sales. If
you apply a report limit, the data used to calculate the sales
rank is not affected.
A report limit specifies a set of criteria used to restrict the
data returned in the report data set after the report metrics
are calculated. Because it is based on the report’s metric
values, a limit is applied after all of them are calculated.
The Report Editor allows you to set limits on any metric you
want to apply to the report. Report limits are defined using
operators such as between and less than.
What is a metric qualification?
A metric qualification is a filtering condition based on the
value of a metric. It contains an output level, which
determines the level at which the metric is calculated and to
which attributes the metric applies. Like every filter, a metric
qualification changes the nature of the metric calculations,
unlike a report limit, which is applied after the metrics are
calculated.
What is report as filter?
Report as filter allows you to create a report and use it as a
filter to generate another report. It is a different way to
achieve the same results as a metric qualification, but it is
easier to understand and create. Because the logic used to
generate the final report is clearer, MicroStrategy
recommends using it rather than the metric qualification.
Understanding how a report is executed
These four steps are the data definition section of the report
execution. The data definition establishes how the data is
accessed and manipulated in the data warehouse.
The other functions noted in step 4 comprise the view
definition, which represents how the data is viewed and
manipulated in the Intelligence Server.
Data definition and view definition objects
The following tables are samples of the information stored in
the data definition and the view definition.
Report designers are generally concerned with data definition
while report creators usually focus on view definition. Report
designers work on the details of reports to create a context or
environment for the report creators to work in. This
environment allows report creators to work within defined
limits, ensuring that only reasonable queries are submitted to
the database. Reasonable means that irrelevant data sets
cannot be created, nor can huge amounts of data be retrieved
from the warehouse. This designer-versus-creator convention
allows a group of report designers to be trained about more
advanced report functions while report creators can
manipulate reports without needing to understand the report
execution details. Through privileges, you can assign
different levels of functionality to different users.
Intelligent Cubes
The Intelligent Cube takes advantage of the separation of the
data definition and the view definition. The cube is a shared
copy of the report data saved in memory and is used for
manipulation of the view definition. The division allows
multiple reports with different views to share a common data
definition. This division allows the Analytical Engine to
perform highly interactive analysis against a set of data
without accessing the data warehouse. In practical terms, you
can modify reports and navigate through the data without
leaving the executed report or waiting for an additional
execution against the data warehouse.
The following diagram represents the Intelligent Cube,
different views that can use the Intelligent Cube, and its
underlying report cache.
The report view is an in-memory representation of the
current view of a report, based on the view definition of that
report. Each user running the same report has a unique
report view on the Intelligence Server. Manipulating the
report views is done after the report has been executed and
uses Intelligent Cube technology. Intelligent Cubes are
automatically instantiated whenever a report is executed; you
do not have to manually create Intelligent Cubes.
The MicroStrategy Intelligence Server leverages the
Intelligent Cube technology for in-memory report
manipulation, such as formatting and sorting. You can
exploit Intelligent Cubes when you design reports by allowing
report creators to use Intelligent Cubes when they
manipulate the view definition of reports.
The report cache is created by an Intelligence Server
schedule based on the data definition and the caching
properties of the report. It contains pre-processed report data
and is stored on disk. The Intelligent Cube is identical to the
report cache but is stored in the Intelligence Server memory.
However, the Intelligence Server uses sophisticated memory
management rules to decide if and when to move an
Intelligent Cube to disk.
The Analytical Engine has been enhanced to use Intelligent
Cubes to allow manipulation of the data displayed in the
report view.
What is a view filter?
A view filter is a quick qualification applied in memory to the
report data set. Because it affects only the view definition, the
report does not have to be re-executed in the data warehouse.
A view filter works in a manner similar to sorting and
subtotals, with which you are already familiar.
A view filter, just as the report limit, is always applied at the
level of the Report Objects list. However, the report limit and
the view filter are not interchangeable. A report limit restricts
the size of the report data set returned from the data
warehouse. In contrast, the view filter is applied to the report
data set without altering its size, allowing you to view a subset
of that information. It retrieves the information quickly
because a view filter dynamically accesses data already in
memory.
When considering how to build a report, a report designer
must balance the memory usage and the processing power of
the data warehouse and the Intelligence Server. A report limit
is more efficient in terms of data size because it does not
return unnecessary information from the data warehouse.
However, the data that a view filter does not display may not
be unnecessary, but rather the user does not need to display it
currently. He may need to display it later.
If a report limit is too restrictive, its utility is reduced because
users must more frequently redefine their data definition to
find the information they want. A view filter is more flexible,
allowing users to refine the analysis after the report is
executed, but it is more demanding on the Intelligence
Server. A view filter is intended to be a post-report execution
function to provide further investigation and refinement of
the report data set. As such, it is aimed at a report creator,
whereas the report limit is usually used by the report
designer.
A report designer must consider the following:
MicroStrategy provides the flexibility to choose report filters,
report limits, as well as view filters on a report-by-report
basis. Each has a different role in the execution and business
meaning of a report.
What is a derived metric?
While viewing a report, you may want to perform calculations
between columns. For example, a quick comparison, such as
Metric1 - Metric2, may be useful. Derived metrics allow you
to perform such column math, or math between metrics, in
the report. A derived metric is a calculation based on the
metrics already in the report. Derived metrics are generated
based on the report data set.
Derived metrics are always evaluated in memory, so they do
not need to access the data warehouse. Although they only
present the data already available on the report in a different
way, they are a powerful and easy-to-use tool. For example,
you can use derived metrics to quickly perform on-the-fly
analyses such as margins, contributions, and differences
between metrics already on the report
What is dynamic aggregation?
Dynamic aggregation occurs when you move an attribute
from the grid to Report Objects. The metric values roll up to
the new level of the grid. Dynamic aggregation occurs
whenever the attributes in Report Objects are not the same as
the attributes on the grid. Dynamic aggregation happens on
the fly, in memory.
The Analytical Engine selects the best aggregation function to
use, by default. However, you can also specify the function for
each metric. You can use any of the standard predefined
subtotal functions or the user-defined subtotals.
What are subtotals?
Totaling is another function of the report view that you can
define. Subtotals reflect data rolled up to the selected
attribute levels and can be applied dynamically to any report.
You can apply subtotals using one of many standard subtotal
functions such as, total, count, minimum, maximum,
standard deviation, and others. If these simple aggregation
functions do not satisfy your particular needs, you can create
a customized user-defined subtotal using the Subtotal Editor.
For more information, see the What are user-defined
subtotals? section that follows.
You can apply the subtotal by position, across a level, or using
group by.
• Applying a subtotal across a level calculates a subtotal
across the selected attributes. The subtotal is applied to
particular levels-rows, columns, and pages. This really
means “group by attributes to the left of the selected
attribute.”
In other words, if you have Region and Employee, in that
order, on a report (as on the Basic Report), selecting
across Employee means group by Region. A subtotal for
each Region, totaling the individual Employee-Region
values, displays on the report. Likewise, across Region
means group by none since there is nothing to the left of it
on the report. The result is a grand total. However, if the
report is pivoted and the order of the attributes changes,
the totals also change. If Employee is pivoted to the left of
Region, the across Employee subtotal means group by
none.
• The by position option means applying the subtotal based
on its location on the report. The subtotal is calculated
across all attributes and hierarchies on the report. It
provides the same behavior as across level, but without
selecting a level. Instead, the level is selected dynamically
so these subtotals change as you alter the layout of the
template. The two choices for by position are All
subtotals, meaning “across all attributes,” and Grand
Total, translating to “across the leftmost attribute.”
For example, you can choose to subtotal on rows and/or
columns. The Basic Report contains the columns Region,
Employee, Revenue, Cost, and Profit. You can subtotal by
both rows and columns, which provides totals at the
employee and region level for each metric.
By default, the by position option is selected.
• Group by applies the subtotal by the selected attribute
across all other attributes on the template, regardless of
position. Group by effectively allows you to use both
subtotal and sort by attributes that are not the furthest to
the left. The Grand Total check box allows you to also add
a subtotal grouped by nothing, effectively calculating a
total of all attributes on the template.
If a report contains Region, Category, and Quarter and
you group by Region, a Region subtotal always appears,
regardless of where Category and Quarter are located with
respect to Region. You can also group by multiple
attributes. For example, grouping by Region-Category on
that report provides a subtotal every time a new
Region-Category combination occurs.
Group by works best if the report is sorted by the same
attribute used to group the subtotals, regardless of
position.
What are custom subtotals?
By default, when you use subtotals in a report, the same
subtotal function is used for all metrics in the report. The
name of the subtotal is displayed in the subtotal line items
that appear in the report. You can use custom subtotals to
give you more control over the characteristics of a subtotal.
Custom subtotals allow you to define custom subtotal line
items that appear on your reports. Custom subtotals allow
you to do the following:
• customize the subtotal name that appears in the subtotal
line item.
• define different subtotal functions to be used on different
metrics in the report.
• specify the level of each total.
• turn off subtotaling for specific metrics on the report.
You can make the subtotal name dynamic by typing special
characters in the subtotal name field as listed in the following
table.
What are smart subtotals?
A compound metric, at a high-level, is composed of two
metrics, such as Metric1/Metric2. The subtotal of a
compound metric can be calculated in two different ways:
• Calculate the sum of all parts of the compound metric,
then perform the compound metric. This formula is
represented by Sum(Metric1)/Sum(Metric2).
• Calculate the compound metric for each row of the report,
and then roll up the data to the correct level. The formula
for this is Sum(Metric1/Metric2).
The first case uses smart subtotals, which calculate subtotals
on the individual elements of a metric. For example, the
Profit Margin metric is calculated as the Profit metric divided
by the Revenue metric. The Profit Margin metric can be
totaled as follows:
• Add all the profit values together. Add all the revenue
values together. Divide the two sums. This is a smart
metric.
• Divide each profit value by each revenue value. Sum up
these ratios.
What are shortcut metrics?
Shortcut metrics are based on metrics already included in a
report and provide a quick way to add new metrics to a
report. They are really just special derived metrics. Shortcut
metrics are available when you right-click on a metric column
or metric header and are based on the selected metric.
Shortcut metrics can be found in the Desktop only.
Shortcut metrics belong to one of the following categories:
• Percent-to-total metrics: display a percent in relation to a
selected total of each item affected by the metric.
• Transformation metrics: apply offset values, such as “four
months ago,” to the selected attribute.
• Rank metrics: apply a ranking number to the metric
values for a given attribute.
Advanced sorting
Sorting allows you to order the report data set to present your
business information in a more informative way. For
example, you can alphabetically sort country and region on a
report, allowing you to quickly find a particular region. The
Basic Reporting Guide discusses such quick sorting, which is
selecting a column or row to sort on.
Advanced sorting allows you to create your own, more
complex sorts for rows and columns. You can select the object
to sort by, the sorting order (ascending or descending), the
sorting criteria, and the position of the totals. The options for
the sorting criteria depend on the sort object. For example,
Employee can be sorted by last name, first name, Social
Security Number, or the attribute ID. The sorting criteria do
not have to be displayed on the report.
Multiple-key sorting, or hierarchical sorting, allows you to
sort data according to multiple sorting criteria in a
hierarchical manner. This means that the first criterion is the
basis for sorting. Any ties are resolved using the second
criterion, any remaining ties are resolved using the third
criterion, and so on. If a tie remains after all the criteria are
used, the default sort order is used as the tiebreaker. In a
simple example, you can sort by ascending employee last
name, then ascending employee first name. If two employees
have the same last name, their first names are compared to
alphabetically sort them. You can, of course, create more
complex multiple-key sorting.
Sorting metrics hierarchically allows you to use group totals
for sorting.
Custom Formats
Custom formats allow you to create your own formats for
data in a report. You can format text, numbers, and date and
time using custom formats. Once you create a custom format,
you can use it in other metrics and report objects as well.
Each custom format can have up to four optional sections,
one each for:
• positive numbers
• negative numbers
• zero values
• text
You can specify these sections, separated by semicolons in
the order listed above. If you specify only two sections, the
first is used for positive numbers and zeros, and the second is
used for negative numbers. If you specify only one section, all
numbers use the same format. The following paragraphs list
the different custom formats that you can apply for text,
numeric data, and date and time with examples of each type
of formatting
1. What is MicroStrategy 8?
MicroStrategy 8 is the latest release of the MicroStrategy Business Intelligence (BI) Platform. With the addition of
over 2,000 enhancements across the entire platform as well as several exciting new sets of functionality (see
question 2 below for details), MicroStrategy 8 sets the gold standard for ease-of-use, comprehensive functionality,
scalability, security and lowest total cost of ownership in the business intelligence software industry.
2. What are the signifi cant new features are available with MicroStrategy ?
MicroStrategy provides the following 8 signifi cant new breakthroughs:
Breakthrough 1 | Redesigned Web Interface
• Feature: Streamlined Web interface utilizing the latest XHTML technology
• Benefi t: Fast, intuitive, simple BI interface for business users
Breakthrough 2 | Fully Interactive Reporting
• Feature: Drag-n-drop interactivity from any web browser, such as Microsoft IE, Mozilla, Firefox or
Netscape, that is completely zero-footprint, using no Java or Active X controls
• Benefi t: Industry’s richest reporting experience, and business user self-service, inside and outside the fi rewall,
with fl exibility and security
Breakthrough 3 | Seamless Integration of Analytics and Reporting
• Feature: OLAP features embedded directly in enterprise reports
• Benefi t: Seamless user experience to uncover root causes from any enterprise report without prior
programming or switching interfaces
Breakthrough 4 | WYSIWYG Document Design and Editing Over the Web
• Feature: First-ever ability for end users to easily design and refi ne reports over the Web
• Benefi t: Radically reducing the time needed to design and deploy new reports
Breakthrough 5 | Direct Access to SAP BW for Reporting and Analysis
• Feature: Direct access to SAP BW through MDX via OLAP BAPI’s
• Benefi t: Reporting, analysis, and monitoring of SAP BW content through a high performance, fully
managed, zero footprint Web interface, that is fully integrated with all other reporting and analysis
Breakthrough 6 | Operational Database Reporting
• Feature: Access any operational system with MicroStrategy’s free-form SQL engine
• Benefi t: Instant managed reporting against data from operational systems – secured, parameterized
and personalized; start with small and fast BI implementations, then grow with MicroStrategy to managed
enterprise levels
Breakthrough 7 | Heterogeneous Data Source Access
• Feature: Join data from multiple relational and multi-dimensional sources using MicroStrategy Report Services
• Benefi t: Combine data from across the enterprise – data warehouses, data marts, operational systems, and
SAP BW data sources – into a single document
Breakthrough 8 | Integrated Predictive Analytics and Forecasting
• Feature: MicroStrategy data mining services
• Benefi t: Put predictive insight into everyday MicroStrategy reports by incorporating advanced
forecasting models and predictive models from best-of-breed data mining products
3. How do I upgrade my reports to MicroStrategy 8 from my current version?
Although MicroStrategy 8 represents a signifi cant breakthrough in functionality, upgrading existing reports to
MicroStrategy 8 is seamless. Since there are no fundamental changes to the metadata, overall architecture or new
products, customers can upgrade their projects by simply pointing their existing metadata to the MicroStrategy 8
Intelligence Server.
4. How do I upgrade my customizations to MicroStrategy 8 from MicroStrategy 7.5?
MicroStrategy Web and SDK customizations can be easily migrated from MicroStrategy 7.5.x due to the backward
compatible nature of the underlying web architecture and parameter driven customization fi les. Also, MicroStrategy
8’s SDK has a utility to facilitate customization upgrades. A small number of customizations may need to be
manually converted.
5. What is the performance and scalability impact of upgrading to MicroStrategy 8?
MicroStrategy 8 inherits and improves upon all the scalability, caching, scheduling, clustering, security and other
enterprise class-IT features of the MicroStrategy 7i platform. In addition, features such as asymmetrical clustering,
improved memory management and high throughput document bursting have been added to the platform.
Customers should experience comparable performance and scalability levels for existing applications after upgrades.
(Exact performance depends upon hardware, usage patterns and other factors). Sizing and performance for new
applications follows previously published guidelines.
6. Will my users require retraining to be productive with the redesigned web interface?
No. MicroStrategy 8 preserves all existing functionality and workfl ow paradigms from the previous web interface.
Existing MicroStrategy users will feel immediately comfortable using the new interface. In addition, new functionality
such as one click toolbars, icons, and dialog boxes are intuitively familiar to any user familiar with Microsoft®
Outlook, Word, Excel, or PowerPoint.
7. Does any of the exciting new interactive reporting functionality require a thick-client web interface
or downloads?
No. MicroStrategy remains fi rmly committed to providing all web-based functionality through a zero-footprint
thin-client web interface. Through the use of XHTML technologies, functionality is provided across multiple browser
platforms (IE, Netscape, Mozilla FireFox) without requiring the use of cookies or code downloads. Also, MicroStrategy
8 uses no Java or ActiveX controls.
8. How can I convert my existing Crystal Reports documents to MicroStrategy 8?
MicroStrategy 8 ships with a stand-alone Crystal Converter Utility. This utility can be used to batch convert multiple
Crystal Reports documents into MicroStrategy Report Services Documents. Often, reports have taken days, weeks,
perhaps even years to perfect over time with input from business users and IT report designers. This utility is
designed to capture most of that value and make it readily available for MicroStrategy Report Services documents.
Not all Crystal Reports documents will convert over exactly from Crystal to MicroStrategy; where that is the case,
report designers will be required to complete the conversion.
9. How does the MicroStrategy SAP BW connector work?
MicroStrategy 8 Intelligence Server contains a new MDX Generation Engine that accesses SAP BW using SAP’s OLAP
BAPI’s. MicroStrategy automatically imports cube defi nitions into the MicroStrategy metadata to speed development.
When users create and execute reports against the SAP BW cubes, MicroStrategy 8 dynamically generates
MDX, executes it against SAP BW, and retrieves and formats the result set. All OLAP functionality such as pivoting,
fi ltering, adding derived metrics, and drilling within cubes is available to end users.
10. Has MicroStrategy been certifi ed by SAP?
Yes. MicroStrategy has achieved both the “Powered by NetWeaver” and SAP BW certifi cations from SAP. Details on
the certifi cations can be viewed on SAP’s website at http://www50.sap.com/softwarepartnerdir/.
11. Will MicroStrategy 8 help improve my SAP BW performance?
Yes. MicroStrategy utilizes a sophisticated caching architecture to store results for frequently accessed queries. This
ensures that these results are retrieved for the user from the MicroStrategy Intelligence Server without re-execution
against SAP BW. In addition to MicroStrategy OLAP services, users can drill within the cache for further OLAP
analysis. Scheduling of these caches during non-peak hours improves performance.
12. Can I combine data from multiple SAP BW cubes and instances as well as SAP and non-SAP data onto
one document?
Yes. Using MicroStrategy Report Services, data from different SAP BW cubes and instances from SAP, and from non-
SAP sources can be combined onto one document.
13. Will the end user experience differ for reports from SAP BW and Operational Databases as compared
to regular Data Warehouse-based MicroStrategy reports?
No. End user interactions and experience for SAP BW based reporting is the same as for regular MicroStrategy
reports, as users access data through the same interface and with a single login mechanism. Users have a seamless
data consumption experience and may not even know where the data they are accessing is coming from.
Accessing Operational Data Sources with Free-Form SQL
14. Can I access multiple operational databases from within a single project?
Yes. A single MicroStrategy project can create reports accessing data from any available data source across the enterprise.
Developers can create these reports by typing SQL into a free-form SQL editor and can even re-use stored
procedures, prompting, and security fi lters to create powerful reporting applications.
15. Does MicroStrategy 8 allow me to combine heterogeneous data sources?
Yes. Using MicroStrategy Report Services, data from multiple operational systems, data warehouses, data marts, and
SAP BW cubes can be combined onto a single document.
16. What new Data Mining and Predictive Functionality has been added to MicroStrategy 8?
MicroStrategy 8 provides exciting new functionality that permits insight from Data Mining and forecasting
algorithms to be added to reports. The function library has been expanded to include advanced functions such as
multi-variant regression (used for forecasting) as well as the most popular data mining algorithms: neural networks,
decision trees and clustering. The predictive models can be imported from a third party data mining tool via PMML
(Predictive Modeling Markup Language) and executed within MicroStrategy Intelligence Server.
17. What Data Mining tools are supported with MicroStrategy 8?
MicroStrategy 8 can import Data Mining models from any tool that can export models to the PMML format. These
include SAS®, SPSS®, IBM® Intelligent Miner and Teradata Miner, among others.
18. What platforms will be supported with this release?
MicroStrategy 8 Intelligence Server is certifi ed to run on Windows 2000, Sun Solaris and IBM AIX. MicroStrategy
Web is certifi ed on a combination of operating systems, Windows, SOLARIS, AIX, and LINUX, and application servers
such as Microsoft IIS, IBM Webshpere, SunOne, BEA Weblogic, and Apache/Tomcat.
19. How is MicroStrategy’s metadata unique?
MicroStrategy’s metadata is unique for three key reasons.
1. MicroStrategy uses a single metadata for all BI applications. For example, MicroStrategy 8 does not use
separate metadatas to support a scorecard application or a reporting application or an analysis application.
Because of that, MicroStrategy truly delivers one version of the truth because the defi nition for key business
performance metrics is always consistent.
2. MicroStrategy’s metadata controls every aspect of the BI application including mappings to the database,
business model abstraction, objects to build reports, even formatting. Everything in the BI application is encapsulated
within an “object” that can be used modularly to create whatever dashboard, report, or analysis is
required. This provides a positive network effect in that every developer can effi ciently and effectively benefi t
from each other’s work.
3. MicroStrategy’s metadata is fully object-oriented. That has two important implications.
a. Every object is reusable. That means objects can be used to build other objects. A straightforward
example is using two business metrics, M1 and M2, to create another unique business metric, M3.
b. Change control is automatic. If the business defi nition of M2 changes, everywhere M2 is used, the new
defi nition is automatically applied with no additional steps required. Since M3 is made up of M2, M3 also
changes automatically to assure all calculations are consistent and correct.
20. What type of security does MicroStrategy 8 offer?
MicroStrategy’s security model governs three fundamental areas of every BI application: (1) access to the BI
functionality each user gets, (2) the report objects each user can use, and (3) cell-level data each user can access.
MICROSTRATEGY: BEST IN BUSINESS INTELLIGENCE
MicroStrategy 8 applies these security controls according to users, user groups and user roles for precise set up
of security policies. MicroStrategy’s approach to security ensures that administrators have centralized control and
maximum fl exibility in implementing the security required for their users.
1. Privileges control BI functionality by defi ning the types of actions users may perform in the system such as
printing, saving, exporting to Excel, drilling, pivoting, sorting, formatting and creating reports.
2. Access Control Lists control the report objects each user see. This is particularly useful in maximizing report
fl exibility. A developer could create one report for thousands of users. That report could contain ten business
metrics. Each user would run that report and only see the business metrics he or she has access to. There is no
need to build thousands of variants of the same report to serve those users.
3. Security fi lters prevent users from seeing specifi c data in the database and/or cells in reports. Automatically
applied, they ensure that users only see data they are authorized to view.
21. What type of authentication does MicroStrategy 8 use?
MicroStrategy 8 authenticates users using any of the following methods:
1. Standard user name and passwords: Stored in the MicroStrategy metadata and encrypted at 160-bits.
2. Database Pass-through: use user name and password security from the databases. This is very convenient
when user communities already exist in database tables.
3. Windows Authentication
4. LDAP or Lightweight Directory Access Protocol systems such as Microsoft Active Directory, Novell, or SunOne
5. Integrated with Single Sign-on products such as Netegrity or Oblix.
22. Can MicroStrategy 8 automatically generate full BI application documentation?
Yes. The documentation covers every object such as reports, fi lters, metrics, database mappings, their defi nitions,
where they are used, and their dependencies with other object – every aspect of the BI application. Conveniently,
the documentation is in HTML so it can be posted on a website for use by the entire user population. Also, since it
is in HTML, users can quickly navigate through it using hypertext links.
23. What are some of the other enhancements to products in the MicroStrategy platform?
In addition to the new functionality discussed earlier, the table below lists some additional enhancements made to
other MicroStrategy Products.
Product
MicroStrategy Web &
Web Universal
MicroStrategy Offi ce
MicroStrategy Narrowcast Server
MicroStrategy Desktop
MicroStrategy Architect
MicroStrategy Intelligence Server
MicroStrategy Administrator
MicroStrategy SDK
Enhancements
• Redesigned interface for business user
• Undo/Redo capability
• Run Report Services documents
• Page-By and Outline reports
• Large File Export (over 250 MB)
• Burst (PPE) Report Services docs
• Shortcuts to objects
• Graphing enhancements
• Asymmetrical Project Clustering
• Decimal data type support
• Narrowcast Server scripts in Command
manager
• Web Customization Upgrade Utility
• User Manager Sample Application
• Object management and shortcuts
• Enhanced right mouse click menu
• Retain Microsoft document formatting
• Use Excel’s Auto-fi lter & Subtotals
• Optimized user of Intelligence Server clustering
• User delivery notifi cation
• Command-line project duplication
• Find & Replace metric formatting
• Logical Views using Freeform SQL
• Vector graphics in PDF documents
• Enhancements to Enterprise Manager
• New object support in Object Manager
• XHTML controls for Web layout changes
• New APIs and improved documentation
Teradata Corporation and MicroStrategy® work to deliver an analytical solution that provides a high ROI for departmental level solutions, while scaling effortlessly and cost effectively to enterprise wide deployments. The result of this unique combination of analytical technologies are joint customers that make MicroStrategy a leading BI product for the Teradata system. Customers benefit from the highly optimized technology integration and from a substantial library of best practices that ensure the successful deployment of demanding business intelligence solutions.
Adding to the joint successes is Teradata University Network, an online portal designed for further education in the areas of data warehousing, business intelligence and database administration.More than 1100 instructors representing over 800 universities and 50 countries are included in the Teradata university Network. MicroStrategy is Teradata’s original BI partner in this groundbreaking initiative.The Partnership A core distinction of the joint solution is its reliance on the processing power and data management capabilities of the highly scalable Teradata system.MicroStrategy technology was designed to take complete advantage of an organization’s investment in the powerful Teradata technology. MicroStrategy intelligently accesses and processes data straight from the Teradata system, eliminating data limitations and time-intensive cube administration. Any piece of data can be accessed at any time, with complete centralized security administration and proprietary performanceenhancing algorithms for fast response.Benefiting customers who manage large amounts of information and databases isthe synergistic partnership that Teradata and MicroStrategy have forged over the years.The longevity of this partnership is in part due to the companies’ strong technical relationship.
MicroStrategy belongs to Teradata’s Early Adopter Program and hosts a Teradata Server in its own in-house laboratory to perform prerelease certification of Teradata’s new releases. MicroStrategy devotes considerable resources to optimize its performance on Teradata.Consequently, the two partners are ableto integrate key capabilities, making their united platform more in sync with today’s fast-paced business environment and its demand for readily accessible information. By enabling their products to work togetherseamlessly and by combining archival Partners Teradata and MicroStrategy in a Snapshot As the global leader in data warehousing and analytic technologies, Teradata solutions make smart companies smarter.
Teradata give companies the people, technology,innovation, and a world-class network of customers and partners enabling them to gain competitive advantage to master their markets.With Teradata, the smartest wins.Founded in 1989, MicroStrategy is a leading worldwide provider of business intelligence software. The MicroStrategy Business Intelligence Platform™ distills vast amounts of data into vital, probing insight to help drive cost-efficiency, productivity, customer relations and revenue-generation.MicroStrategy offers scalability, powerful analytics, user-friendly query and reporting features and an easy-to-use web interface.
Because of MicroStrategy and Teradata’s joint heritage in the high-end of BI, with the ability to support hundreds of users over terabytes of data, our technologies are uniquely positioned to help organizations leverage the fullest potential of BI. Our combined strengths in user and data scalability allows us to serve our customers’growing needs for the most demanding and sophisticated BI applications.
Subscribe to:
Comments (Atom)