MicroStrategy ONE
Other Data Sources and Relational Databases for Windows
If you use other databases or data sources, refer to the database-specific documentation for information on required settings. Standard settings are supported by MicroStrategy for most relational databases.
Microsoft Excel
A Microsoft Excel file can be used as a data source in MicroStrategy. The information below explains how to prepare an Excel file for use with MicroStrategy and how to connect to the Excel file.
This data can be used as part of a MicroStrategy project in various ways. For example, you can integrate the Excel data in your project using tools such as Architect, as described in the Project Design Help. You can also use Freeform SQL and Query Builder to access your Excel data, as described in the Advanced Reporting Help.
Prepare an Excel file as a valid data source
To use an Excel file as a data source, you must create and store the data in the Excel file so that it can be recognized in MicroStrategy as a set of tables that contain valid data. Follow the below instructions to create a table with valid data in an Excel file:
- Prepare the Excel file as follows:
- Ensure that all column headers are of a valid format:
- No spaces in the header name (for example, Category_ID instead of Category ID) .
- Alphanumeric, and beginning with a letter.
- Ensure that all cells for the ID column have a value in them.
- Ensure that all column headers are of a valid format:
- In the Excel file, create a table by performing the following:
- Highlight the specific rows and columns with the data to use 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, and so on to select the whole column. Doing so may include numerous empty cells with NULL values.
- In the Name Box, type a name for the highlighted cells, and then press ENTER. The name you type in is used in MicroStrategy as a table name.
The Name Box is the drop-down list on the left-hand side below the toolbars.
You can create multiple tables in one Excel file by highlighting different parts of the file and assigning them different names.
- Highlight the specific rows and columns with the data to use to create a report with, including the column headers, such as Category_ID and Category_DESC.
- Save the Excel file.
Ensure that the file is not password-protected.
Use your Excel file as a data source
To use an Excel file as a data source, you can create a data source name (DSN) for the Excel file. This DSN can be used by a database instance in MicroStrategy to connect to the Excel file. For information on creating a database instance, see Creating a database instance.
As an alternative, you can use Data Import to quickly include Excel data in your MicroStrategy project. Steps to use Data Import to import data and begin your analysis is included in the MicroStrategy Web Help.
Text files
A text file can be used as a data source in MicroStrategy. You can use Data Import to quickly include data from text files in your MicroStrategy project. Steps to use Data Import to import data and begin your analysis is included in the MicroStrategy Web Help.