Small numbers in the cube shown as zero in excel - rounding error?

Question

Small numbers in the cube shown as zero in excel - rounding error?

squareoff

Mr or Mrs. 500

Points: 587
More actions
April 23, 2018 at 12:06 pm

#149592

Hi all,
I've created a Tabular SSAS Cube recently. Certain numbers within the cube are very small (e.g. 0.0000612762).
When I connect to the cube from excel to look at the data, these small numbers are shown as zero.
Has anyone encountered it before?
Is data being truncated in the cube if it is very small?
Please advise.
Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Negmat 18367 - Monday, April 23, 2018 12:06 PM
Hi all,
I've created a Tabular SSAS Cube recently. Certain numbers within the cube are very small (e.g. 0.0000612762).
When I connect to the cube from excel to look at the data, these small numbers are shown as zero.
Has anyone encountered it before?
Is data being truncated in the cube if it is very small?
Please advise.
Thank you!

Have you changed the format to increase the decimals shown?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 2

Hi Luis,

I'm very new to SSAS.

Where would I change the format?

I've looked around the Model.bim file and can't find it anywhere.

Please advise.

Thank you!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Negmat 18367 - Monday, April 23, 2018 2:46 PM
Hi Luis,
I'm very new to SSAS.
Where would I change the format?
I've looked around the Model.bim file and can't find it anywhere.
Please advise.
Thank you!

That's not part of the mode, it's defined in excel (or any other presentation layer).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 4

Yes, I've updated the "Format" in excel, but it is just showing all zeroes as if the data got truncated prior to getting to excel ...

Any other suggestions?

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 5

Hi All,

I solved the mystery and would like to share it here - perhaps it will help someone else.

Clue #1 came from running a query on the database side (which aggregates fact data) and comparing results to the excel pivot (connecting to the cube) to identify which metrics are calculating correctly and which seem to be truncating decimal points.

Clue #2 came from running the following query against the SSAS cube (which essentially returns metadata results - similar to INFORMATION_SCHEMA):

SELECT
    [CATALOG_NAME],
    [CUBE_NAME],
    MEASURE_NAME,
    DATA_TYPE,
    EXPRESSION,
    MEASURE_IS_VISIBLE,
    MEASUREGROUP_NAME,
    MEASURE_DISPLAY_FOLDER,
    DEFAULT_FORMAT_STRING
FROM $system.mdschema_measures

Looking at the DATA_TYPE column results I've noticed that DATA_TYPE = 5 (numeric?) for the non-truncated data in the cube and DATA_TYPE = 20 (int ?) for the truncated data in the cube.

Clue #3: The Fact View which is brought into the cube stacks a number of FACT tables, and defaults to NULL columns where the data is not present in every fact table. The columns which were set to NULL in the 1st Fact query within the view are exactly the ones which were truncated. It seems to mean that as SSAS cube is being built (or data is processed) it assumed those NULL columns to be INT datatype.

Solution:

1. Updated the Fact View in the database to remove TOP 10000 and instead selected all the data (casting NULLs in the top query to NUMERIC might have worked as well, but I did not try that)

2. In Visual studio (Model.bim) I removed and recreated the Fact View(i.e. re-imported from the data source), added all the relationships to the dimensions etc. Once added, I reran the query shown above and saw that now all the DATA_TYPE values = 5

3. Connected to the cube from excel and QAed the pivot, confirming that data truncation is no longer happening and the data in the excel pivot (essentially the cube) is matching back to the data in the database.

If anyone has questions, let me know.

Thanks!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

It's great that you found a solution and posted it on here. Thank you very much for doing it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

squareoff Mr or Mrs. 500 Points: 587 More actions · Answer 7

squareoff

Mr or Mrs. 500

Points: 587

April 30, 2018 at 10:20 am

#1988655

It was my pleasure.

Thank you Luis!