Creating and Configuring a Project » Overview of project creation » Strategies to include supplemental data in a project

Strategies to include supplemental data in a project

The steps listed in Overview of project creation above relate to the process of creating a project which connects to a database or other data source such as a text file or Excel file.

MicroStrategy also supports strategies to include supplemental data in a MicroStrategy project. These strategies include:

Connecting to data stored in SAP BW, Microsoft Analysis Services, Hyperion Essbase, and IBM Cognos TM1 systems. When integrated with MicroStrategy, these systems are referred to as MDX cube sources. You can connect to any of these MDX cube sources to report and analyze the data concurrently within a project that also connects to a database, or you can create a standalone connection to your MDX cube source (see the MDX Cube Reporting Guide).
Using MicroStrategy Web to import data from different data sources, such as a file on your computer or Dropbox, a database table, the results of a SQL query, or Facebook with minimum project design requirements.

This capability of using MicroStrategy Web to import data, referred to as Data Import, is described in detail in Including personalized data and developing proofs-of-concept below.

Including personalized data and developing proofs-of-concept

The Data Import feature lets you supplement the primary data of your project in the following ways:

For information on creating the primary data for your project, follow the steps provided in Overview of project creation.

Include personalized data from various data sources. Along with the primary data provided in the project, your personalized data can then be reported on using all the standard MicroStrategy reporting and analysis features. This personalized data can also be displayed with the primary data in your project using documents.
Develop a reporting environment as part of a proof-of-concept. Data Import is a powerful proof-of-concept tool due to its ability to quickly integrate data into MicroStrategy, without requiring the full data modeling requirements that are described in this guide as part of creating a project.

MicroStrategy automatically maps attributes, metrics, and other objects to the data included in a MicroStrategy project using Data Import. These objects are created as managed objects.

A managed object can be removed once it is no longer referenced by another object in the project. The removal of unused managed objects is usually performed by an administrator. For more information on removing a database instance and its related managed objects, see the System Administration Guide.

While managed objects allow you to quickly integrate data using Data Import, the data is not directly related to the rest of your project schema. This means that only data integrated using a single Data Import process can be included together on a given report; no additional attributes, metrics, or other objects from the project can be included.

However, rather than allowing MicroStrategy to automatically create managed objects for the attribute data, you can map the data to existing attributes in the MicroStrategy project that are part of the relational schema. The benefits of using project attributes to define the data integrated into MicroStrategy using the Data Import feature is described in Mapping data to project attributes below.

Dashboards provide an additional option that lets you include reports that analyze data from different data sources together inside a single dashboard. This can be accomplished by including the reports as separate datasets of the dashboard, and then each dataset can be displayed separately on the dashboard as a report, graph report, widget, or other analysis tool.

Dashboards provide a method to include both data from the rest of your project and personalized data imported using the Data Import feature, into a single analysis view. For example, the dashboard below displays various information about a current customer’s telephone service plan and their potential to churn.

Some of the data on the document could come from the primary project, such as the churn prediction, revenue risk indicator, and peak and off-peak minute usage. Additional details such as the contract usage details and the customer demographics could come from separate data sources, each included into the project using Data Import. Because documents can present separate sets of data in a single view or location, your regular project data and personalized data can be displayed to analysts as if the data were integrated.

For important prerequisites and tuning information for the Data Import feature, refer to the System Administration Guide. For more information on how to use the Data Import feature, refer to the MicroStrategy Web Help

Mapping data to project attributes

During the process of importing data into MicroStrategy using the Data Import feature, you can use the automatically generated managed object attributes to identify and define the levels of your data.

Alternatively, you can manually map the data to existing attributes in the MicroStrategy project that are part of the relational schema. Mapping the data in this way replaces the managed objects that are used to represent the data with attributes in the MicroStrategy project. Mapping imported data to attributes that are part of a relational schema has the following benefits:

Report designers can integrate the logical model of the project with the imported data, thus creating a relation between the two sets of data. Data can then be joined across sources within a document. In addition, document features such as selectors and group-by, which can restrict data displayed on a document based on attributes, are also applied.

For example, a document includes a report that uses Data Import and a standard report, which both use the same Year attribute. The document also includes a selector based on the Year attribute. Since both reports map year data to the same Year attribute, the selector can restrict the data for both reports. If the Data Import report used a managed object for its year data, the selector would not be applied for that report on the document.

Administrators can search for dependents and manage access control lists (ACLs) for attributes that map both to the data warehouse and another data source.
MicroStrategy security filters can be applied to attributes in reports and documents that use imported data. For example, you can map a column, integrated into MicroStrategy through the use of the Data Import feature, to the Year attribute in your project. If a user with a security filter on Year runs a report or document that uses this import data that contains Year, the security filter on Year is applied.

Mapping data to project attributes is performed during the process to integrate data into MicroStrategy using the Data Import feature. During this process, you must select valid attribute forms for the columns of data to represent as project attributes, while meeting with the following requirements:

The ID form of the project attribute must be mapped to the column in the Data Import data source that you have created to relate the two systems of data. The columns must share the same data type. Numeric data types such as Integer are commonly used to represent the ID forms for attributes.
Ensure that all other columns mapped to attribute forms of project attributes include data that is relevant to the attribute form. This data must be relevant in terms of the data type as well as the content. If there are any data inconsistencies between the imported column data and project attribute form, using a project attribute can cause reporting issues. For example, data from two Microsoft Excel spreadsheets is shown below.

One spreadsheet is a good match to map the attribute forms of the Customer project attribute to the columns of the spreadsheet, because the data in the spreadsheet is consistent with the project attribute’s data in the project database. However, the other spreadsheet has data inconsistencies and therefore should use a managed object to represent the data instead of mapping the attribute forms of the Customer attribute to the data.

The process of integrating data using the Data Import feature is explained in the MicroStrategy Web Help. Refer to the Help for steps on how to complete this process, including how to map the data to project attributes.

Integrating geographical information with your imported data

During the process of importing data into MicroStrategy using the Data Import feature, you can integrate additional geographical information with your imported data. This can improve the depth of geographical information available for your data, and can also allow for easier integration with MicroStrategy mapping features such as the Map widget and map visualizations. An example of the type of mapping analysis available in MicroStrategy is shown below.

When importing your data, the column headers and the data itself are analyzed by MicroStrategy to determine what type of geographical information, if any, is available. MicroStrategy attempts to recognize data related to the geographical categories for Area Code, County, Country, State, City, Zip code, Location, Latitude, and Longitude.

MicroStrategy can automatically recognize geographical data that meets the following criteria:

If the following names are used for the column headers of your data:
Area Code: Includes data that resembles three-digit area codes.
County: Includes data for United States counties, such as Cooke, Tioga, and Jackson.
Country: Includes data such as United States, Germany, and Italy.

MicroStrategy can also recognize data that resembles various country abbreviations, codes, and synonyms.

State: Includes data such as Virginia, California, and Oklahoma.

MicroStrategy can also recognize data that resembles various state abbreviations, codes, and synonyms.

City: Includes data such as New York, Paris, and Tokyo.

MicroStrategy can also recognize data that resembles various city abbreviations, codes, and synonyms.

Zipcode: Includes data resembling five digit US postal codes.
Location: Includes locations, resembling one of the following formats:
Address, City, State, Country

For example, 1850 Towers Crescent Plaza, Tysons Corner, Virginia, United States

Address, City, State

For example, 1850 Towers Crescent Plaza, Tysons Corner, Virginia

City, State

Tysons Corner, Virginia

Latitude: Includes data that resembles latitude values. In addition to recognizing latitude values as separate attributes, latitude values can also be automatically applied as attribute forms of any of the geographical categories listed above. This capability is described below.
Longitude: Includes data that resembles longitude values. In addition to recognizing longitude values as separate attributes, longitude values can also be automatically applied as attribute forms of any of the geographical categories listed above. This capability is described below.

If a column of data is recognized as related to one of these geographical categories, additional geographical data can be included as part of importing the data into MicroStrategy:

New attributes can be automatically created and populated with data for Country, State, and City. These options are available if any one of these geographical categories is recognized:
If a column of data is recognized as Zipcode, attributes for City of Zipcode, State of Zipcode, and Country of Zipcode can be automatically created as part of the importing process.
If a column of data is recognized as City, attributes for State of City and Country of City can be automatically created as part of the importing process.
If a column of data is recognized as State, an attribute for Country of State can be automatically created as part of the importing process.

If you already have columns of data for any of these geographical categories, when importing your data you can choose to not create these attributes and instead use the attributes that are created based off of your data.

New attribute forms can be automatically created to define the latitude and longitude values for any Area Code, County, Country, State, City, and Zipcode. Be aware of the following if you select to automatically create these latitude and longitude values:
The latitude and longitude values for an Area Code, County, Country, State, City, or Zipcode are determined by the files City.csv, AreaCode.csv, County.csv, Country.csv, State.csv, and ZipCode.csv. These files are included as part of an Intelligence Server installation. You can modify these tables to change the latitude and longitude values for a given location, as well as add additional locations that can then have latitude and longitude values automatically assigned when importing data into MicroStrategy. Any changes to these .csv files are reflected when a user is connected to the associated Intelligence Server to import their data into MicroStrategy.

The State.csv file includes alternative types of regions such as provinces, districts, and so on.

By default, latitude and longitude values for counties and zip codes are only provided for locations within the United States, as defined by the County.csv and ZipCode.csv files, respectively.

If an Area Code, County, Country, State, City, or Zipcode is recognized, the first valid latitude and longitude values that are found in the .csv tables are automatically assigned to the latitude and longitude attribute forms. This can assign incorrect latitude and longitude values in some scenarios.

For example, if the city of Springfield is recognized in your data, the first matching latitude and longitude values found in the City.csv file are for Springfield, Colorado. However there are multiple Springfields in the United States.

To avoid automatically assigning incorrect latitude and longitude values, use the following best practices:

You can modify the .csv tables to only include the locations relevant to your data.
For locations within the United States, rather than including city information directly in your data, you can instead include only zip code information. Using this zip code data, MicroStrategy can accurately determine the latitude and longitude of the location, and you can also select to create City of Zipcode, State of Zipcode, and Country of Zipcode attributes.

Once you import your geographical data into MicroStrategy, you can then use the Map widget and map visualizations to display and analyze the geographical data. For steps to create and configure the Map widget, refer to the MicroStrategy Mobile Design and Administration Guide. For steps to create and use map visualizations as part of Visual Insight, refer to the MicroStrategy Web Help.

Creating additional time-related data for your imported data

During the process of importing data into MicroStrategy using the Data Import feature, you can allow MicroStrategy to create additional time-related data for your imported data. This can help to define your data more specifically when it comes to time information.

When importing your data, the contents of the data is analyzed to determine whether it is in a format that supports dates or times. MicroStrategy can automatically recognize date and time data that meets the following criteria:

The data uses a valid date or date and time format. Date data can include month, day, and year information in the format MM/DD/YYYY, where MM is a two digit month, DD is a two digit day, and YYYY is a four digit year. Date and time data can include month, day, and year information in the same date format described above, as well as time information in the format HH:MM:SS. For time data, HH is a two digit hour value, MM is a two digit minute value, and SS is a two digit second value. A complete entry for data that includes date and time information can have the format MM/DD/YYYY HH:MM:SS.

If a column of data is recognized as including date or time data, additional time data can be included as part of importing the data into MicroStrategy:

The following new attributes can be automatically created and populated with data if date data is recognized:
Year: Includes data such as 2010, 2011, and 2012.
Quarter: Includes data such as Q1 2011, Q2 2011, and Q3 2011
Quarter of Year: Includes data such as Q1, Q2, and Q3.
Month: Includes data such as January 2011, February 2011, and March 2011.
Month of Year: Includes data such as January, February, and March.
Week: Includes data such as Week 50, 2011, Week 51, 2011, and Week 52, 2011.
Week of Year: Includes data such as 50, 51, and 52.
Day of Month: Includes data such as 28, 29, and 30.
Day of Week: Includes data such as Sunday, Monday, Tuesday.
Date: Includes data such as 1/27/2011, 1/28/2011, and 1/29/2011.

If you already have columns of data for any of these date categories, when importing your data you can choose to not create these attributes and instead use the attributes that are created based off of your data.

The following new attributes can be automatically created and populated with data if time data is recognized:
Hour: Includes data such as 12, 13, and 14.
Minute: Includes data such as 57, 58, and 59.
Second: Includes data such as 57, 58, and 59.

If you already have columns of data for any of these time categories, when importing your data you can choose to not create these attributes and instead use the attributes that are created based off of your data.

All of the attributes listed above can be automatically created and populated with data if date and time data is recognized.

Once you import your data into MicroStrategy, you can then include any of the time attributes that were automatically created during the import process on your reports, documents, and dashboards. For steps to analyze the data you have imported using Data Import, refer to the MicroStrategy Web Help.