The value ‘-1.#IND’ is showing up in tables when calculations from my olap datasource result in 0. Is there a way to change this to default to 0?
OLAP calculation resulting in -1.#IND value
What is the value when you look at these rows in the Data Cube data Preview?
Hi James,
Thanks for responding.
It is an OLAP datasource so there is no Dundas Data Cube preview if that is what you are referring too. Unless there is something I don’t know here.
Hi @ryan - even with OLAP data, you should be able to click on the data preview screen at the bottom left of the dashboard designer. I would also be curious what you are seeing there.
Thank you!! I was able to find it (it was actually on the side where properties are!)
It still shows the same in that screen.
@ryan
If it is showing the same in the data cube then that the result of the query as Dundas sees it.
What is the raw data when you query the same source outside of Dundas,
You are saying it is OLAP but that is still just tables with rows (Just star or snowflake structure. so it can be queried with regular SQL.
So let see what the results are in SSMS.
Another thing is how ever you are querying this make sure you are looking at raw results, no replacements. if you do have replacements in your query make sure they are working correctly( by just looking at the raw then reviewing what they are doing to what should be doing.
We all get into this same situation, where we look in Dundas and start seeing things in the results that we did not expect. This is the steps we take, first look in the Dundas Cube Data Preview, Is it there then go down another level and see if it is there (it will be trust me) and see about fixing at the lowest level is my suggestion.
It could even be bad data.
1 Like
James,
Thanks for your response. It is because the OLAP calculation is dividing 0/0 and resulting in a null value. Sorry if I did not include this in my opening post. The result is NaN in the cubes.
I still would like to know if it is possible to set an option in Dundas on how to handle null values like in excel.
Is there a place to do it?
0/0 is not truly null. it is -1.#IND.
My first suggestion is detect the possible 0/0 in the the lowest level possible to make it NULL instead of -1.#IND, if in SQL I might use a case statement.
Then in the Data Cube you can do Null replacement.
Thank you for all of your resposnes lately James.
So I guess the suggestion is to abandon my OLAP cubes and create a data cube in order to have any kind of null replacement?
if by OLAP cube you mean you just dragged a table over?
If that is what you have done then I am not sure how you are getting a divide by zero issue in a table result.
If you dragged a view over then get into the SQL for the view and fix it there.
in my example that SQL Select could be a table, but same thing it will only replace NULLs not the divide by zero result that is not a true NULL (hence why you are seeing -1.#IND not NULL).
if it is a table that has the -1.#IND then what is filling that table, it is a data warehouse so there some sort of ETL and a query there. Might not be best to fix it there, you might have to change to a Select Statement instead of just dragging the table over so you can replace that with NULL or some other value that make scene for charting.
james,
I am a bit confused. I have done no such thing with a table nor a view from anywhere in Dundas. I have simply dragged elements from my data connector over. I can tell that you are quite knowledgeable and appreciate all of the help that you have given me on this post and others. Have you worked specifically with Analysis Services as a data connector in Dundas before and is that what you are referencing in these posts?
I am new to Dundas, but I think that using a Dundas Data Cube and an Analysis Services data connector are quite different. I am unsure if I am just too new to Dundas or if we are not communicating because our datasources are so different. Either way, I am left with the impression that I must fix this in the analysis services calculations at the datasource level.
Hi Ryan,
If you try to reproduce the same query in Management Studio, what are you seeing as output from your cube? Since you’re simply dragging and dropping from a cube, Dundas BI isn’t processing your data - it’s just a pass through as we generate an appropriate query to build a metric set. This means that almost certainly, your cube is giving this result your seeing.
A good technique might be to run a SQL profiler tool as you Dundas BI to see exactly what query is being sent to your cube. Once you know this MDX query, test in Management Studio. If there results are different, we have a clue where the problem might reside.
OLAP is one of the most common data sources that Dundas BI users utilize so I very highly doubt the issue exists in the Dundas BI side or else it would have been reported long ago. It could be something as silly as a culture setting but it’s hard to tell without more information.
Failing this - let me know and i’ll set you up with a dedicated support person who can try to help you come to the bottom of this.
Hi Ryan,
A Dundas BI data cube is the ETL layer object, used to transform the data. For OLAP cubes, you would simply create a data cube, and drag the OLAP cube under the data connector to the data cube designer.
You are right that analysis services is different in some aspect, but the transforms such as null replacement transform usage will remain the same regardless of the data source type. I would recommend the following resources from the support site:
https://www.dundas.com/Support/learning/videos-tutorials/data-cubes/introduction-to-data-cubes
Gentlemen,
Thank you very much for your responses.
I can confirm that the values are not zero when I review the data directly from Analysis Services. When reviewing the data in studio it is displayed as NaN. These calculations in question are division problems which divide a numerator by a denominator. Naturally, when slicing these calculations by some hierarchies/dimensions - you will eventually end up with 0/0. I was looking for something like you see in excels pivot table format options:
This is a very valuable feature.
- Is the only way to fix error/null/NaN values in OLAP datasources for me to create a data cube and/or change all of my calculations in OLAP?
- Do the Dundas experts recommend always making a data cube out of your OLAP datasources for reasons like this?
Thank you again for your responses!
Fundamentally, a data cube’s primary objective is to cleanse and consolidate raw data from the source. So generally speaking, it would be recommended to create a data cube as there are some null/other invalid numbers that need to be modified before being passed along to appropriate visualizations.
With OLAP specifically, we don’t see a lot of situations where it is a necessity to create an data cube, at least with most of our clients. However, given your use case, it is something worth exploring, in my opinion.
1 Like
@ryan
When I hear OLAP I think the Datawarehouse tables that SSAS is built on top of, so i thought you were querying or dragging those right into Dundas not the Cubes you built in SSAS.
So yes we had some confusion.
No I have not worked with SSAS Cubes yet. Let just say our DW is still in development and we not gotten to the SSAS portion yet.
I still say that NaN result is not the same as NULL result so a NULL replacement will not work. That is why I suggested to go to the source (that I thought was a SQL Query, but in your case will need to be an MDX Query) and detect the divide by zero and change the result to NULL or directly to what you would replace Null with.
now I have,
Error: System.NotSupportedException: Floating point values "NaN" and "Infinity" are not supported.
from a data cube getting warehoused by Dundas.
This i strange, when i run the query in SQL I do not have any of these unsupported values. Oh I have a calculated Element maybe it is being caused there. Yeap it is a divide by Zero issue.
So Let me get into the calculated element and put some code in there that will detect it.
…
this is what i had:
return $YAI$/($SES$ + $YAI$)
This is what I changed it to:
var retValue = 0;
if ($YAI$ + $SES$ > 0)
{
retValue = $YAI$/($SES$ + $YAI$);
}
return retValue;
Testing, that fixed it.
Now I just wait for the day that all computer systems just default Zero divided by Zero is 0, but that would only be followed by a fundamental change in math theory.
ps: it took almost TWO years for this to get a divide by Zero in this Data Cube. I just did not see it being possible before 