June 11, 2010 at 3:11 am
I am a little unsure of how Analysis Services data sources work in Reporting Services. For example I choose the Year Month and the fact Number of Pupils with data and get
Year Month Number of Pupils with data
2010-06-11 May1011
As soon as I add the pupils surname I get every row even if it doesn’t answer that fact for example
Year Month Surname Number of Pupils with data
2010-06-11 MaySmith0
2010-06-11 MayJones0
2010-06-11 MayBrown 1
2010-06-11 MayHollis0
I have a fact table and 2 dimensions. Pupil Information dimension and time dimension.
Is it supposed to do the above? I was convinced that If I just chose a specific fact and brought in the pupil information I should only see those pupils that answered the question. E.g.
Year Month Surname Number of Pupils with data
2010-06-11 May Brown1
Have I done something wrong in the data source? The Relational OLAP has 1s and Nulls in the fact column.
Include Empty Cells is not selected in the Reporting Services data source view.
If anyone can shed any light It would be very much appreciated
Debbie
June 11, 2010 at 1:29 pm
Put a filter in your MDX to show only values of 1
June 15, 2010 at 3:17 am
If you are getting this in SSRS, then I am also assuming you are getting this in the cube Browser or when you run an MDX statement...is that correct? If not then in SSRS make sure that you are using the NON EMPTY on the columns.
If you are getting this in both then what you need to do is change the default binding option for NullProcessing on the measure in your cube. I have seen this happen and this is a compatibility thing that also confuses me and I don't understand why it is set to Automatic instead of Preserve. You will want to change the property to Preserve and then you should no longer see the rows as long as you are using the NON EMPTY statement - http://msdn.microsoft.com/en-us/library/ms127041(v=SQL.90).aspx
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
June 15, 2010 at 3:20 am
Fantastic,
I will have a look at that and let you know how it goes
Debbie
June 15, 2010 at 4:16 am
Ive been screenshotting the issue here....
Managed to update to Preserve but Im still getting 0's coming through.
I used this as instructions on how to do it...
http://msdn.microsoft.com/en-us/library/ms345138(SQL.90).aspx
June 15, 2010 at 5:35 am
I have never changed the processing option in the dimension usage section. I have always changed that in the cube structure tab in the measures source properties.
http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!558.entry
Try that and then re-process. That should definitely resolve your issue since I see you were using the cube browser feature which uses NON EMPTY.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
June 16, 2010 at 3:35 am
Thank you so much.
It works and my data source is much quicker too.
Ive added my screen shots to http://my.opera.com/DebzE/blog/reporting-services-using-analysis-services-data-source-showing-facts-at-pupi
I wouldnt have been able to figure that one out myself in a million years.
Thanks again
Debbie
June 16, 2010 at 5:35 am
Not a problem and glad to help. I see this happen from time to time and have pinpointed why it works in some cases and not others. I would think that Preserve would be the default behavior and should be a best practice, so it really confuses me why Automatic is the default option.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply