Debbie Edwards (8/31/2012)

---

The dimensions have processed Successfully prior to the updating of the SEN Cubes

The attribute key cannot be found when processing: Table: 'fact_SEN_Workflow_Steps', Column: 'DW_Prov_ID', Value: '370194'. The attribute is 'DW Prov Child ID'.

Checking the Source data and this attribute key matches the dimension so it’s fine.
Can I check the dimension now that the cube has failed?

Analysis Services…. Dimensions….. Provisions

No it doesn’t allow you to check for an ID Can I do in in SMS using MDX?

Q. Can you do this in MDX SELECT * FROM dim.Service_Prov WHERE DW_Prov_Child_ID = 370194

You cant browse the cube in Alalysis Services or SMS ‘ Error Loading Metadata. No Cubes were found.’

If I run it in Analysis Services on Full it succeeds which means I can never actually test for this issue.
Once it Suceeds when manually processed in Analysis Services it works in IS. Then fails again during the overnight.

Ive put this into the forum before and didn’t get anywhere but I thought Id try again because surely it can’t be me having these issues.

Can I get it to ignore errors and send them to a report so I can see what it thinks has happened and still have a cube I can look at at the end.

I have been totally stuck with this for months and haven’t got any further.

herladygeekedness (8/31/2012)

---

Not sure I can be much help, but "stuck for months" got to me.

When I get a cube processing error, I usually can fix it by processing each dimension separately and then re-trying. Sounds like you have done that AND checked that your fact key does, in fact, match the dimension key. At least, it sounds like you have done that?

I don't do MDX querying, I query the underly SQL dim table to see if that key is there. Sounds like you cannot access the dimension data ?

Process Full on the cube should process all dimensions,too. When you do that manually, all is well?

I do not use the AS components in SSIS, my SQL Agent cube does it for me:


<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">
<Object>
<DatabaseID>Fake Cube</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>


Hope I've at least given you a few ideas. Good luck!

Debbie Edwards (8/31/2012)

---

The dimensions have processed Successfully prior to the updating of the SEN Cubes

The attribute key cannot be found when processing: Table: 'fact_SEN_Workflow_Steps', Column: 'DW_Prov_ID', Value: '370194'. The attribute is 'DW Prov Child ID'.

Checking the Source data and this attribute key matches the dimension so it’s fine.
Can I check the dimension now that the cube has failed?

Analysis Services…. Dimensions….. Provisions

No it doesn’t allow you to check for an ID Can I do in in SMS using MDX?

Q. Can you do this in MDX SELECT * FROM dim.Service_Prov WHERE DW_Prov_Child_ID = 370194

You cant browse the cube in Alalysis Services or SMS ‘ Error Loading Metadata. No Cubes were found.’

If I run it in Analysis Services on Full it succeeds which means I can never actually test for this issue.
Once it Suceeds when manually processed in Analysis Services it works in IS. Then fails again during the overnight.

Ive put this into the forum before and didn’t get anywhere but I thought Id try again because surely it can’t be me having these issues.

Can I get it to ignore errors and send them to a report so I can see what it thinks has happened and still have a cube I can look at at the end.

I have been totally stuck with this for months and haven’t got any further.

Debbie Edwards (8/31/2012)

---

Thanks loads for that



Yep I process each dimension successfully in Integration Services on FULL. It seems to go fine every time



I have checked the underlying data in SQL and its totally fine. It the data in the cube that I cant check due to the unsuccessful process.



Thats it, if I do a manual process it works fine, Its just the IS Package where the cubes fail after processing the dimensions.



Ooh this is the interesting bit. What do you mean by SQL Agent Cube and what is the script that you included? Do you process all your dimensions and cubes overnight in a seperate job just for Analysis Services?

If I could figure this bit out I might be somewhere to getting it sorted. Unfortunately I dont have access to setting up over night jobs so I need to make sure I know what Im taking about to give instructions to the dba team.

Thanks for your help

herladygeekedness (8/31/2012)

---

The script is the second step in my SQL Agent overnight job. The first step runs all the dimension ETLs and then Fact Table ETLS in one big SSIS "parent" package with tons of "kids".

If that is all successful, this next step should be configured as SQL Server Analysis Services Command. The user in the RunAs must have SSAS privileges (I'm no authority on the permissions but whoever sets it up for you will need to test it, and reprocessing a cube is not damaging, so do insist that they test it before telling you It's Set!). It should be obvious to them which server to enter, wherever the cube lives!

You said you process in Integration Services - not SSAS? I open AS instance and right-click cubes to manually do it - are you only using the SSIS components for it as "manual"? I have not used any SSIS components for SSAS operations so if they have tricks, sorry, I'm not going to know them. We also don't use manifests, so not helpful on that, either.

If they have not provided you credentials to log in to SSAS I guess there isn't much you can do on that, but if you CAN access it, the script I provided can be run in a query window there. Likely generating same error message.

Maybe you are processing it via IBIDS, in your AS project interface? Just trying to figure out if you are confined to SSIS components or if you can open the AS project? If you can open the AS project, you should definitely be able to browse dimensions that processed. Check your datasource view(s) also. Make sure to preview the data. I'm hoping that something will jump out at you. If yours is set for queries vs tables (ours are queries), run them, see if you can spot any nulls or odd values. Verify relationships, also. I doubt anything moved, but it will be worthwhile to check all pieces.

I have to ask, though the answer is implied, does your fact table look ok otherwise? No weirdo data or unexpected nulls? especially in that field you are getting an error in - could be you solve for the first id, and, oh crap, now it can't find the next! Only time I had that was when I made a boo boo and had built the facts off of wrong dims. Solved for one key, then, uh oh, another! I strongly feel that it's a data issue and maybe a change was made some time ago that seemed like it would have no impact...?

Can't tell you how many times I've been bitten by that assumption much later...

Debbie Edwards (8/31/2012)

---

Thanks loads for that



Yep I process each dimension successfully in Integration Services on FULL. It seems to go fine every time



I have checked the underlying data in SQL and its totally fine. It the data in the cube that I cant check due to the unsuccessful process.



Thats it, if I do a manual process it works fine, Its just the IS Package where the cubes fail after processing the dimensions.



Ooh this is the interesting bit. What do you mean by SQL Agent Cube and what is the script that you included? Do you process all your dimensions and cubes overnight in a seperate job just for Analysis Services?

If I could figure this bit out I might be somewhere to getting it sorted. Unfortunately I dont have access to setting up over night jobs so I need to make sure I know what Im taking about to give instructions to the dba team.

Thanks for your help

herladygeekedness (8/31/2012)

---

oh, and it never hurts to ask for a reboot of the server!

herladygeekedness (8/31/2012)

---

I may have found a way around the cube being offline - when you go to process, check the options for ignoring errors. It's maybe not the best thing to do, but it might get the cube back to where just seeing the thing can make the problem obvious.