Upon refresh with same data, get different results each time

[Former Member]

HI, I have an issue with a universe that I am currently building using information Desing tool (version 4.1). It only has few tables in, so nothing major. I am trying to bring a table in the universe which consist of 3 objects and 6 measures associated with them. Table looks like the example below with the data type –

Object 1       Object 2   Object 3  Measure1  Measure2  Measure3   Measure4   Measure 5

Date Object  Numeric   Numeric  Numeric     Numeric    Numeric     Numeric      Numeric

Object 1 – Its date object in yyyy-mm-dd hh:mm:ss.sss format

Object 2 – Its represent ID for a product which can occur multiple times in the table. Its in numeric format in the table however I need that as a string/varchar for reporting purpose. So I am using the following SQL statement in the object – rtrim(case([Object 2] as char(5))). Object 2 can be 5 or less char hence using the trim to remove any spaces added at the end.

Object 3 – Its smilar to objec2, represent a Skill ID. I am doing the same on this object and converting it to char. Measure 1-5 They are all Numeric values which are set to Sum up.

Now the issue I am facing is, When I build a report and have more than 1 object out of 3 in the query the measures don’t add up correctly and upon each refresh the query provide different set of results.

Example – Query 1 –

Select

Object 1,

Object 2,

Measure 1

From table ----

Result: Measure 1 = 400

The same query upon refresh (no update on the data base since the last execution) changes the results. The only time I seem to get accurate result is when I only use one object and a measure. Rest of the time it never returns the accurate results. Any idea what might be causing this issue? Any help would be much appreciate, Thank you in advance.

PS: Let e know if you require any furhter information. Sorry I am relatively new to Universe Design and trying to pick it up on the fly.

-S.K.Rab

[Former Member · Accepted solution]

Hello-

Did you check "Multiple SQL statements for each measure" setting and try?

tyr to view SQL each time you run query and compare results.

There might be something with your join's as well that might cause this issue, set up the cardinality's
between the table's in data foundation.

Taking these steps must resolve your issue.

[Former Member · Accepted solution]

Thank you ravi, This was helpful.

I have managed to resolve the issue, it was a data stirping option that was causing the query to return partial results on each refresh. Untick the data striping and row limit options and that resolved it.

Thank you for your help.

-SKR