February 7, 2012 at 8:51 am
Hi,
Has anyone ever had issues where the cube data doesnt match the source data.
It looks like its specifically related to the fact table as the facts are different
For example this is what I drag back into the query
Year Pupil Fact_SEN_CHILD
2008/2009 2568 1
But if you query the source
Year Pupil Fact_SEN_CHILD
2008/2009 2568 <NULL>
Ive done a full process so the fact the cube thinks that this is a SEN child 1 but the fact is actually NULL is really worrying
In fact at the top level there area 2549 SEN children
In the source there are only 1387.
Its connected directly to the fact table so I havent replaced it with a named query. This is really worrying that you can have such a massive difference and I am lost as to why its happening.
Any help would be appriciated. I cant really move on until this is sorted.
Debbie
February 7, 2012 at 9:41 am
So you've set Fact_SEN_CHILD as a measure? If so, what is the aggregation type set to?
Also, if you right click in the DSV and view data, what do you see there?
Steve.
February 7, 2012 at 9:47 am
stevefromOZ (2/7/2012)
So you've set Fact_SEN_CHILD as a measure? If so, what is the aggregation type set to?Also, if you right click in the DSV and view data, what do you see there?
For the first question Im not sure what you mean. It does aggregate but the fact in the source is simply NULL and in the cube it says 1 which isnt correct at all and clearly its doing this to alot of the pupils in the data (Its at pupil level)
Second Q bit I can right click and view the DSV and it seems OK but I cant filter for that perticular child in here so Im not sure.
I cant give this to a user If I cant trust that the cube data is the same as the source data. Ive tried full reprocess but its still the same....:blink:
February 7, 2012 at 10:07 am
When you create measure, you can set the aggregation (Sum, Count, distinct count, min, max, average etc). Simply trying to find out what your measure is set to.
When viewing data through the DSV you can use a pivot table, this may help you limit the data to confirm that it's coming through as a null into SSAS.
Right now, if i had to guess (which i think i do), i'd say you're either pointing to a different data source, or your aggregation is perhaps set to count and you're counting nulls.
Steve.
February 7, 2012 at 10:09 am
Im going to replace the data source and reconnect to see if this helps. I will let you know what I find.
February 8, 2012 at 3:43 am
:crying:
SO what I have done is change the fact tabled to a Named query.
Within the query I have added the criteria of WHERE PUPIL = 2568 So there is only one pupil in the data set coming through. I’ve processed the cube and explored the data in the fact table and its fine. There are 18 rows (Im just showing the date and fact rows for these examples)
TimeID SEN Pupil
199709011
199809011
199909011
200009011
200109011
200209011
200309011
200309011
20040901NULL
20050901NULL
20060901NULL
20070901NULL
20070901NULL
20080901NULL
20090901NULL
20090901NULL
20100901NULL
20110901NULL
So the cube contains correct data.
Go to browser and add the details and I get incorrect data again….
Academic Year SEN Pupil
1997-19981Correct
1998 19991Correct
1999-20001Correct
2000-20011 Correct
2001-20021Correct
2002-20031Correct
2003-20042Correct. In the original this was two rows of 1 and 1
2005-20061incorrect. NULL in the fact table
2006-20071incorrect. NULL in the fact table
2007-20082incorrect. NULL in the fact table
2008-20091incorrect. NULL in the fact table
2009-20102incorrect. NULL in the fact table
2010-20111incorrect. NULL in the fact table
2011-20121incorrect. NULL in the fact table
Grand Total18 This is correct. There are 18 rows
What is going on? I have no clue but its very worrying. Im definitely connecting to the right table. It looks fine exploring the data but it displays wrong in the3 browser. I cant even see an explanation for it.
February 8, 2012 at 3:57 am
Ive just tried changing NULL to 0 and the exact same thing is happening.
Ive googled and come up with nothing on this issue.
Im running out of tests I can do.
February 8, 2012 at 3:59 am
stevefromOZ (2/7/2012)
When you create measure, you can set the aggregation (Sum, Count, distinct count, min, max, average etc).
Where do you do this? I can certainly check this, I definitely didn't set these up for any of my measures
February 8, 2012 at 4:35 am
Found the aggregate functions on the measures.
Changing the aggregate function of my facts. In Cube Structure right click on the measures and go to properties. See Aggregate functions
They are all set to Sum - Calculates the sum of values for all child members. This is the default aggregation function- Additive
Thinking about it is this correct?
I don’t want to sum up all the values for SEN Child because the it shows if the child in SEN against every academic year. So
2002-2003 There are 2 SEN records in the fact table in 2003
2003-2004 Only 1 SEN value, It will be the same as 2002-2003 so we don’t want to add this up to 3
2004-2205 There are no SEN records in the fact table here so it should be NULL
So Im actually not sure what aggregate function I should be setting it to if its not SUM? As it is my cube is a mess because its not reflecting the actual data.
February 8, 2012 at 4:47 am
Hi
Is your "Date Dimension" working correctly. This would be the dimension used to aggregate by [Academic Year]. Is the dimension correctly allocating the TimeId to the appropriate Academic Year?
Cheers
-Matt
February 8, 2012 at 4:52 am
Ooooooh Ill have a look at this!
I know I have got different year types in my dimension like Financial and standard. I dont think I sent academic up.
Thankyou...Something to do work with.....
Ill let you know how I get on
February 8, 2012 at 5:41 am
Also, are your facts correct? Ie unless you have some other measures, you'd not normally have a fact record where a "fact" (ie "was a student") doesn't exist. From your example it looks like you have facts for years where the person wasn't a student.
Steve.
February 8, 2012 at 5:49 am
I believe the facts are correct and you are right there is more than one fact in the cube. I didnt mention the other ones but this might give you a better idea......
Academic YearSEN SENMainstream
1997-1998 1 1 Correct
1998 1999 11 Correct
1999-2000 1 1Correct
2000-2001 1 1Correct
2001-2002 1 1Correct
2002-2003 1 1Correct
2003-2004 22 Correct. In the original this was two rows of 1 and 1
2005-2006 1NULL incorrect. NULL in the fact table
2006-2007 1 NULLincorrect. NULL in the fact table
2007-2008 2NULL incorrect. NULL in the fact table
2008-2009 1NULL incorrect. NULL in the fact table
2009-2010 2NULL incorrect. NULL in the fact table
2010-2011 1NULL incorrect. NULL in the fact table
2011-2012 1NULL incorrect. NULL in the fact table
Grand Total 18 This is correct. There are 18 rows
So the one I have been talking about it the SEN mainstream fact.
So in this example they started attending a none mainstream school in 2005 even though they are still a SEN child.
But the business question is about the mainstream SEN so if you look at anything after 2004 this child shouldn’t be coming up in that list.
February 8, 2012 at 5:59 am
Lowfar (2/8/2012)
HiIs your "Date Dimension" working correctly. This would be the dimension used to aggregate by [Academic Year]. Is the dimension correctly allocating the TimeId to the appropriate Academic Year?
Cheers
-Matt
I have my Financial and standard date information set up but I didnt know what to set my Academic Information up to so I didnt bother
I have Academic Year
Academic Term
Trimester (Eg. Spring, Summer and Autumn)
I dont suippose anyone has a good idea what time information these should be set to?
February 9, 2012 at 5:08 am
Really struggling, Im not able to move on from this issue at all. Ill try and start again with a few extra fact details (3)
Explore the fact table
TimeIDChildSENSEN MainstreamAs at January
19970901256811NULL
199809012568111
199909012568111
200009012568111
200109012568111
200209012568111
200309012568111
2004090125681NULLNULL
2005090125681NULLNULL
2006090125681NULLNULL
2007090125681NULLNULL
2008090125681NULLNULL
2009090125681NULLNULL
2010090125681NULLNULL
2011090125681NULLNULL
I have 12 as at facts For each month so the user can choose the year and then a fact for the as at month they are interested in. Here you can see that They were SEN mainstream from 1997 through to 2003. But they must have been created after January 2007 which is why its not showing at this point.
Now I want to show the same information in the cube itself so go to the browser
TimeIDChildSENSEN MainstreamAs at January
19970901256811NULL
199809012568111
199909012568111
200009012568111
200109012568111
200209012568111
200309012568111
20040901256811NULL
20050901256811NULL
20060901256811NULL
20070901256811NULL
20080901256811NULL
20090901256811NULL
20100901256811NULL
20110901256811NULL
So the plot thickens. SEN Mainstream is as usual showing incorrectly but the as at fact is OK. I’m at a loss and I’m going to have to try and explain this at some point.
The aggregation is some for both facts and one is showing correctly, the other is wrong.
Help
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply