Data warehouse for data storage and relational design

A well-designed and robust data warehouse is the source of data for the decision support system or business intelligence system. It enables its users to leverage the competitive advantage that the business intelligence provides. Data warehouses are usually based on relational databases or some form of relational database management system (RDBMS) platform. These relational databases can be queried directly with Structured Query Language (SQL), a language developed specifically to interact with RDBMS software. However, MicroStrategy does not require that data be stored in a relational database. You can integrate different types of data sources with MicroStrategy such as text files, Excel files, and MDX Cubes. For more information on accessing data stored in alternative data sources, see Storing and analyzing data with alternative data sources.

The source systems described above, such as OLTP systems, are generally designed and optimized for transactional processing, whereas data warehouses are usually designed and optimized for analytical processing. In combination with MicroStrategy tools and products, the data warehouse also provides the foundation for a robust online analytical processing (OLAP) system. Analytical processing involves activities such as choosing to see sales data by month and selecting the applicable metric to calculate sales trends, growth patterns, percent-to-total contributions, trend reporting, and profit analysis.

Most data warehouses have the following characteristics:

• Data access is typically read-only. The most common action is the selection of data for analysis. Data is rarely inserted, updated, or deleted. This is in contrast to most OLTP source systems which must be able to handle frequent updates as data is gathered. For more information on source systems, see Source systems for data collection.
• Data is aligned by business subjects.
• Data formats are uniformly integrated using an ETL process (see Extraction, transformation, and loading process).
• Data history extends long-term, usually two to five years.
• A data warehouse is populated with data from the existing operational systems using an ETL process, as explained in Extraction, transformation, and loading process.

The structure of data in a data warehouse and how it relates to your MicroStrategy environment can be defined and understood through a logical data model and physical warehouse schema. Defining a project's logical data model and physical warehouse schema are important steps in preparing your data for a MicroStrategy project. For more information on the steps of the project design process, see The Logical Data Model and Warehouse Structure for Your Logical Data Model.

Storing and analyzing data with alternative data sources

Along with integrating with relational databases, which are a common type of data warehouse, MicroStrategy can also integrate with a number of alternative data sources. A data source is any file, system, or storage location which stores data that is to be used in MicroStrategy for query, reporting, and analysis. A data warehouse can be thought of as one type of data source, and refers specifically to using a database as your data source.

The following are different data source alternatives which MicroStrategy can integrate with:

• MDX Cube sources: In MicroStrategy you can integrate with sets of data from SAP BW, Microsoft Analysis Services, Hyperion Essbase, and IBM Cognos TM1, which are referred to as MDX Cube sources. MicroStrategy can integrate with these data sources while simultaneously accessing a relational database effectively. For more information on connecting to and integrating MDX Cube sources in MicroStrategy, see the MDX Cube Reporting Help.
• Text files and Excel files: With MicroStrategy's Freeform SQL and Query Builder features, you can query, analyze, and report on data stored in text files and Excel files. As with MDX Cube sources described above, MicroStrategy can report against these alternative data sources while concurrently accessing a relational database to integrate all of your data into one cohesive project. For more information on using text files and Excel files with the Freeform SQL and Query Builder features, see the MDX Cube Reporting Help.

Additionally, the Data Import feature lets you use MicroStrategy Web to import data from different data sources, such as an Excel file, a table in a database, or the results of a SQL query, with minimum project design requirements. For more information on how Data Import can be used to integrate data from various data sources into your project, see Strategies to include supplemental data in a project.

For more information on how to use the Data Import feature, refer to the MicroStrategy Web Help.