March 6, 2015 at 8:21 am
Good Day All.
I am hoping this is not a bizarre or vague topic but its totally weird on my end.
So basically I have a dimension called DimSegment which holds segment names for our business. It joins to my fact table by FK segment_id.
When the DW loads, data is loaded for relevant segments as the data exists. However when the data is viewed in excel, the weird thing is, every now and again, the segments are coming back empty. In order for me to fix this issue, I have to either reprocess the dimension or in some cases process the whole cube and refresh the excel data.
There is no rhyme or reason to this happening. Not all segments in the dimension have matching data in the Fact but I dont see how that should affect the segments that do indeed have data in the DW. All other dimensions work fine except for this one and for the other segments, I dont always have data for all the members in those dimension yet no problems.
Before I go deleting data out of the dimension for those segments that do not have data in the FACT table. Does anyone know what may be causing this?
March 6, 2015 at 8:29 am
Since reprocessing seems to fix your problem, the first thing I would check for is steps that are sometimes are not getting executed during your update process.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 6, 2015 at 8:33 am
Thanks Alvin
the first thing I would check for is steps that are sometimes are not getting executed
Not sure Im tracking your response. All the steps do execute when the cube is processed.
What I am not understanding is for eg. if data is showing in excel today, tomorrow when the cube updates and processes, the available data in excel instead of updating on a refresh all just "disappears".
March 6, 2015 at 8:38 am
That is pretty weird/interesting. I've seen some "interesting" behavior with Excel before...can you check to see what query is being executed against your cube?
I would also check the properties of your pivot table in Excel...have seen some funny behavior based on those in the past.
March 6, 2015 at 8:46 am
Interesting and weird is an understatement. Not sure how much help the MDX gives but I got this from Profiler when i ran a Refresh on the sheet. I'm not sure this is isolated to excel because the same thing happens in the Cube browser
SELECT
NON EMPTY CrossJoin(Hierarchize(DrilldownMember({{DrilldownLevel({[Dim Date].[FSCL_YM].[All]})}},
{[Dim Date].[FSCL_YM].[Year No].&[2015]})),
{[Measures].[SOME MEASURE],
[Measures].[SOME MEASURE],
[Measures].[SOME MEASURE]})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Dim Date].[FSCL_YM].[Month Nm].[Quarter No] ON COLUMNS ,
NON EMPTY Hierarchize({DrilldownLevel({[Dim Segment].[SEGMENT_HCY].[All]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS
FROM
(SELECT ({[Dim Date].[FSCL_YM].[Month Nm].&[2015]&[2],
[Dim Date].[FSCL_YM].[Month Nm].&[2015]&[1]}) ON COLUMNS ,
({[Dim Segment].[SEGMENT_HCY].[Segment NM].&[ERASED],
[Dim Segment].[SEGMENT_HCY].[Segment NM].&[ERASED],
[Dim Segment].[SEGMENT_HCY].[Segment NM].&[ERASED]}) ON ROWS
FROM [VESFINCUBE_TRA]) CELL PROPERTIES VALUE,
FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
March 6, 2015 at 9:25 am
I guess the point I was trying to get to, is to see whether the generated MDX is different for some reason when the dimension members are missing, as opposed to when they're not.
If the queries are identical, then something weird must be happening when you are processing the dimensions/cubes...maybe dimensions are not processed fully?
March 6, 2015 at 9:38 am
Thanks Martin
Alvin also suggested I check the processing steps of the Cube. Not sure why only this one dimension will be affected out of the others during processing but ill check the SQL running during DimSegment Processing and see if anything stands out.
A scenario I am currently testing in Dev is to see whether only segments with available entries stops this problem from occurring.
As i stated earlier, I've seen it where full processing works and there are no issues and other times where my data suddenly "flies away". The come the phone calls - "Where did the data go?"
I know this was somewhat vague but I appreciate you guys' help as always. Ill post with any updates I have. Was just wondering if this was something anyone else had experienced and me not losing my mind.
March 6, 2015 at 10:12 am
ttdeveloper (3/6/2015)
Thanks MartinAlvin also suggested I check the processing steps of the Cube. Not sure why only this one dimension will be affected out of the others during processing but ill check the SQL running during DimSegment Processing and see if anything stands out.
A scenario I am currently testing in Dev is to see whether only segments with available entries stops this problem from occurring.
As i stated earlier, I've seen it where full processing works and there are no issues and other times where my data suddenly "flies away". The come the phone calls - "Where did the data go?"
I know this was somewhat vague but I appreciate you guys' help as always. Ill post with any updates I have. Was just wondering if this was something anyone else had experienced and me not losing my mind.
No worries. It almost seems like a case where your dimension keys are changing, but the cube is not fully processed and facts are "pointing" to the incorrect dimension member. I would definitely check the processing options/settings of your dimensions and cube. It may make sense to fully process the entire SSAS database.
March 6, 2015 at 11:13 am
This is the xml I have scheduled to run daily for my Cube refresh.
You dont think it has anything to do with the "writebacktablecreation" option right.
Your suggestion on the dimension key definitely makes sense. I had checked that earlier and the segment dimension is set to only insert new records not existing in the table. Hence the reason i discarded that...
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>VESFINWH</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Also thought I'd mention that I added this step to the agent schedule to reprocess the dimension 2 days ago to see if that would help and for this morning, it didnt. However, running a one time reprocess from BIDS fixed it.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>VESFINWH</DatabaseID>
<DimensionID>Dim Segment</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
March 6, 2015 at 11:35 am
I don't think the write-back option has anything to do with it. Found another interesting article though, which suggests that you should enable the "ProcessAffectedObjects" option if you only do a ProcessUpdate on your dimensions.
Have a look and see if that resolves your issue: http://www.ssas-info.com/analysis-services-articles/66-mgmt/2212-what-happens-when-you-do-a-process-update-on-a-dimension
March 6, 2015 at 11:50 am
Martin
Again always helpful - ill check it out and get back. Thanks again for all the help.
March 10, 2015 at 7:06 am
So Martin/Alvin
After our discussion last week, I went back to have a look at my data model and found the problem - thanks for Martin's suspicion of missing dimension keys.
Turns out due to my DimDate joining to my FACT table by a key MMYYYY (32015), there were problems when the cube refreshed as this key was not necessarily unique. I noticed that in Excel, the issue with the disappearing values happened when i selected and deselected dates from the filter drop down.
I overhauled the entire model and rebuilt the DimDate this time with surrogate keys (now unique) and with the date grain added. Now the FACT table has a proper way to join and the missing values issue is - well no longer an issue.
Thanks for the tip Martin - didnt think this would ever come back and bite me but it did.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply