SCSM Troubleshooting: DWASDatabase - Invalid Column Name
If you are unfortunate enough to be an SCSM Admin tasked with the maintenance, reporting, troubleshooting, workarounds, solutions, and everything else SCSM is/lacks all that probability becomes a certainty. You will inevitably come across some horrid nightmares and be expected to support.
When you have amassed enough knowledge and experience, you might be able to offer some potential solutions. In the case of DWASDatabase, it usually comes down to rebuilding the whole SCSM Data Warehouse Cube Database again.
However that's not a fix done in an hour as it requires a resync of the entire Data Warehouse, plus stopping archival for those days. So in the event you experience an error that seems you just need to do something simple to the Cube Database, read on!
If you have a cube that won't be processed and keeps throwing an error: Invalid column name 'Property'. It means that attribute in the dimension can no longer be queried from the DWDataMart.
In this case, the name of the property was "ActionLog" .... long story short, a vendor decided that adding a property called ActionLog would make the action log control appear on the Change Request form in the SCSM Console which they proceeded to do all on production cause they didn't set up a test environment. It didn't work and they still walked away with the money.... but we got pens with their name on it which continues to serve as a constant reminder.
The problem is when the Data Warehouse marked the Change Request Custom Property "ActionLog" as gone that's all it did. The cubes don't ever update themselves, every dimension has a static query to pull data, and now that this "ActionLog" property can no longer be queried trying to process a cube yields the Invalid column name error.
So there are two options:
- Rebuild DWASDatabase - requires unregistering Data Warehouse, manually removing cube jobs that won't delete themselves, and other painful things
- In this case, Use SQL Server Data Tools (SSDT) to attempt to load the DWASDatabase and manually remove the ActionLog attribute from ChangeRequestDim
But where can we get SSDT? Well, you can either find and download the standalone program from Microsoft, or you can find it under the feature selection of the SQL Enterprise Edition when installing SQL Server 2012.
Yes, the Cube Database is such a nightmare you must install another program to try to remove corrupted attributes!
But wait, there's more.... there's always more... now, what do we do?
The seemingly simple Remove an Attribute from a Dimension instruction from Microsoft is not enough to complete what we want to accomplish!
Open SQL Server Data Tools as admin
File > New > Project > Business Intelligence - Analysis Services > Import From Server
Name the Project as DWASDatabase_SSDT as this is the database that will be created on the Analysis Server
Server = localhost || Database == DWASDatabase
Looking good so far….. Takes like 10 minutes…..
Now that it's all loaded, double-click "ChangeRequestDim.dim" under Dimensions
This brings up Dimension Structure Tab, far left has attributes listed
Right-click ActionLog and hit Delete
Taking a while…..
Taking a while…..
Eventually given a prompt saying it'll be deleted…. Yes, do it!
Now we have to save changes, it says the cubes will be saved, wanna proceed …. Yes, do it!
Go Build > Rebuild
Taking a while…..
Go Build > Deploy
When prompted, enter the service account password for each data source it uses
Now there should be a DWASDatabase_SSDT database present. Ensure you can process the ChangeRequestDim or even the cube which would process the Dimension.
If it can be, then remove the bad database and rename your DWASDatabase_SSDT to what the database was called, in this case, DWASDatabase is the original name.