Data Types » Big Decimal » Using the Big Decimal data type

Using the Big Decimal data type

With the Big Decimal data type, MicroStrategy preserves the precision of attribute ID values and attribute ID forms when displaying IDs and performing operations such as filtering, drilling, and page-by. For more information about these operations, see the Basic Reporting Guide.

You can define attributes that are identified by numeric columns in the database. These numeric columns can have more than 15 digits of precision, such as account numbers and other long integers. You must use the Big Decimal data type to handle these values, because these data values have higher precision and cannot be stored in normal numeric data types.

If you do not associate high-precision database fields with the Big Decimal data type, you may see numbers truncated starting with the 16th digit. The WHERE clause in the report SQL statement in drill reports may truncate numbers starting from the 16th digit, and page-by may not return results.

When using the Big Decimal data type, follow the rules listed below:

Constant: You can force a constant to be stored as a Big Decimal value by enclosing it in hash marks. For example, you can define a filter as “Customer@ID exactly #12345678#”, even though 12345678 does not necessarily require the Big Decimal data type.
Attribute form: If you change the column data type to Big Decimal on the Column Alias tab in the Attribute Editor, you must also select Big Decimal as the form format type in the Form format: Type drop-down menu in the Definition tab.
Attribute ID: Follow the steps in the topic Defining attributes with high-precision ID forms in the MicroStrategy Developer Help (formerly the MicroStrategy Desktop Help).
Metric: Although it is possible to define Big Decimal as the data type for metric values, consider the following drawbacks:
Precision is lost when any Analytical Engine calculation is performed, the metric is used in a data field in a document, the metric is subtotaled, or metric values are displayed in Graph view.
Numeric formatting strings supported in MicroStrategy can have a different effect when applied to the Big Decimal data type. For numeric formatting descriptions and examples when using the Big Decimal data type, see Numeric data formatting.
When qualifying on a Big Decimal metric, you must explicitly identify high-precision constants by enclosing the value within hash (#) symbols. For example, #1234567890123456#.

Note that the Warehouse Catalog does not automatically map DECIMAL(p, s) or NUMERIC(p, s) columns to the Big Decimal MicroStrategy data type even when the precision is greater than 15. This is because Big Decimal should only be used when the column is used as an attribute ID form.

Numeric data formatting

You can apply the following numeric data formatting to the Big Decimal data type:

For information on how numeric data formatting is applied to other data types in MicroStrategy, see the Advanced Reporting Guide.

Symbol

Description

0 (zero)

Digit placeholder.

If the number contains fewer digits than the placeholders contained in the format, the number is padded with zeros.

For example, the format code 00000 will display the number 12 as 00012.

If there are more digits to the right of the decimal point than the placeholders in the format, the decimal portion is rounded to the number of places specified by the placeholders.
If there are more digits to the left of the decimal point than the placeholders in the format, the extra digits are retained.
If the format contains zeros to the left of the decimal point, numbers less than one are displayed with zeros to the left of the decimal point.

#

Digit placeholder.

This digit placeholder displays significant digits and insignificant zeros.

For example, the format code ##.### will display the number 0025.630 as 25.630.

If there are more digits to the right of the decimal point than the placeholders in the format, the decimal portion is rounded to the number of places specified by the placeholders.
If there are more digits to the left of the decimal point than the placeholders in the format, the extra digits are retained.
If the format contains only number signs (#) to the left of the decimal point, numbers less than one are displayed beginning with a zero.

For example, the format #.00 will display the number 0.43 as 0.43.

, (comma)

Thousands separator.

If the format contains commas separated by #'s or 0's, commas separate the thousands. Note that the actual thousands separator used depends on the session locale.

. (period)

Decimal separator. Note that the actual decimal separator used depends on the session locale.

The following table lists examples of custom numeric formats. It includes the formatting symbols, the report data, and how that data is displayed after applying the formatting to a Big Decimal data type.

Format

Cell data

Display

#.##

250.436
0.43

250.44
0.43

#.0#

250.436
125

250.44
125.0

#,###

1500

1,500

"Sales="0.0

123.45

Sales=123.5

"X="0.0;"x="-0.0

-12.34

x=-12.3

"Cust. No. " 0000

1234

Cust. No. 1234

ALT+0163 #.##

250.45

£ 250.45