Introduction:Data Import is a new feature in MicroStrategy Intelligence Server/Web 9.0.2, where data from sources such as Excel, CSV, databases can be imported into MicroStrategy without any architecting steps. Refer to the following MicroStrategy Knowledge Base document for best practices for data import in MicroStrategy Intelligence Server 9.0.2.
TN34495: Best practices for Data Import in MicroStrategy 9.0.2
This document provides the best practices for data import in MicroStrategy Intelligence Server 9.2.0 - 9.2.1. Enhancements have been implemented in these versions to allow users to import file sizes greater than 100MB.
CONSIDERATIONS FOR FILE SIZE
A successful import depends on the following factors:
- Web Server
- Memory on Intelligence Server machine.
- MicroStrategy Intelligence Server Configuration.
Typical browsers (Internet Explorer, Firefox) are not built for uploading large files (since they are mostly for consuming information) and run into memory issues when users manually try to import large files. “Manually” here refers to the action where a User browses for the file and uploads the file from a client machine. A manual import is different from the URL import, where in the latter, memory consumed by a browser is not a factor.
Based on internal testing, it is seen that some browsers such as Firefox is better at handling large sized uploads than others such as Internet Explorer. Our recommendations are to have these upper limits for files sizes with the following browsers, while uploading manually: Internet Explorer: 400 MB , Firefox: 750 MB
Note: As a workaround for this browser limitation, import files larger than the above values, as URL import.
Typically Web Servers have their own upper limits for the amount of data transfer that they allow from Client to Server. The out-of-the-box MicroStrategy Web installation using IIS sets this value at 4MB. Refer to TN 34465 if it is necessary for users to increase this limit.
Memory on Intelligence Server:
There should be enough memory available on the Intelligence Server machine to be able to publish the Intelligent Cube. The datatypes being imported have a significant impact on memory utilization. The usual considerations which are applicable to Intelligent cube publication are applicable in this case also.
MicroStrategy Intelligence Server Configuration:
The following project governing settings impact data import:
- Governing Rules > Default > Import Data > Maximum file size (MB): Default value is 100MB in MicroStrategy Intelligence Server 9.2.0 and 30MB in MicroStrategy Intelligence Server 9.2.1. Increase this value if it is necessary to use import files larger than this limit.
- Governing Rules > Default > Import Data > Maximum quota per user (MB): Default value is 100MB. This means that a user has an in-memory space of 100MB on the MicroStrategy Intelligence Server to upload the files successfully.
- Governing Rules > Default > Result sets > Memory consumption during data fetching (MB): Default value for this setting -1, which is unlimited.
Frequently Asked Questions:
Question 1: What can I do with the default MSTR Installation (with IIS Web server)?
Answer: In MicroStrategy Intelligence Server 9.2.1, the default governing for Maximum File size is 30 MB, Maximum Quota is 100 MB, IIS Web server limit is 4 MB. So, based on these governing settings, a user can:
- Upload files up to the range of 30 MB manually.
- Upload files up to the range of 4 MB thru URL.
Answer: Based on our Internal Tests1
|Manual Upload (MB)||URL Upload (MB)|
1. Results are based on machine with 8 GB RAM, Firefox 3.6.17, and IE 8. User results may vary based on hardware spec.
2. These results were using Firefox as the browser. Internet Explorer was only able to upload 400 MB. The browsers crash beyond this upper limit.
Question 3: How do I upload files larger than 30 MB?
Answer: The following steps need to be taken to upload files larger than 30 MB:
1. Increase the following governing settings:
Project configuration > Governing Rules > Default > Import Data > Maximum file size (MB): Increase this value to a value higher than the size of the file that needs to be uploaded.
Project configuration > Governing Rules > Default > Import Data > Maximum quota per user (MB): It is recommended to increase the value to 4x the size of the file that needs to be uploaded.
2. From the MicroStrategy Web Administrator page, increase the server busy timeout from the default setting of 10 seconds to a large enough value that can support the data import job. For a 1500 MB size file, it’s typically increased to 4000 seconds.
3. If using a manual upload method, there should be enough free memory to be used by the browser. Typically, the browser uses 1.1x file size. Uploading a 750 MB file manually would take around 825 MB memory on the client.
4. If using IIS as the web server and when performing a URL upload, refer to TN 34465 to increase the IIS upper limit.
5. MicroStrategy Intelligence Server machine should have enough RAM to upload the cube. A suggested value is 6x file size. This is however just an estimate and the actual requirements will different in customer environments.
CONSIDERATIONS FOR DATA IN FILES (Excel/Text/CSV)
- When importing data from a file, the import process may be easier if the structure resembles a flat table including column headings, for example, Year, Employee, and Profit. This means that users should also avoid pivoting or cross tabbing the data prior to importing it. The column headings are automatically mapped to attributes and metrics, and it is not necessary for users to manually insert column heading names.
- Text/CSV files should be strictly comma separate values. Do not use any other separator (e.g. tab, semicolon) even in conjunction with comma. For Text/CSV, only UTF8 & 16 codepage are supported which are also the most frequently used codepage. In case it is another code page such as Windows 1252, convert the codepage or import as Excel.
- Duplicate column headers are supported. An index number is added to the duplicate column header.
- Confirm the type of data for the columns. Choose a numeric data type if all data of the column is numeric. If there is any non-numeric value, the column alias should be mapped as text. If a column has numeric data and some cells contain the string ‘NULL’, they should be replaced by empty cells or the whole column should be imported as Text.
- Handling NULL cells.
Null values are auto filled in attribute columns with the preceding non-null value.
From MicroStrategy Intelligence Server 9.2.1, Null values are supported in Metric columns by considering them as Nulls i.e. If some cells are empty, and users still choose 'numeric' as the data type, the blanks are treated as Nulls.
CONSIDERATIONS FOR FILE UPLOAD USING URL
In MicroStrategy Intelligence Server 9.2.1, https and ftp (along with http) are supported. Anonymous authentication is supported. Basic authentication is supported only if the username and password are part of the file. Example: https://diuser:dipassword@MyWebServer:18183/DataImportFiles/ Test.xlsx and ftp://diuser:dipassword@MyWebServer:10021/DataImportFiles/Test.xls
TN Key: 36815