March 13, 2013 at 10:55 am
Hello all,
Short version.
I need to determine how from within TSQL I can query an SSAS cube to find the source column name (KeyColumn) of an attribute within a dimension. ie, my attribute is called "Product Color"; however in the database, that is coming from a table called "Products" with a column name of "Custom1". I basically need to find the database column names of all the attributes in a specified dimension.
Long version.
Our application leverages a somewhat generic, yet configurable cube per client. Each of our clients, are configured by the amount and type of data they send us. So from a database perspective, within our customer and product dimensions, we allow for our ETL team to leverage any one of multiple generic/custom columns. Being that said generic columns aren't named for their purpose (again because they're generic so we can have a model database for all clients), the columns are then renamed within the Cube and the cube then uses those names for the application. We're working on some custom reporting that hits the database and not the cube (I know) and with the above said, I have no way of knowing what to index because any given client can be using X to Y of these custom fields. Outside of dumping a blanket index on all columns, the next best bet would be to dynamically create an index based on the columns in use for that cube.
We've accomplished this within the application using SQL AMO; however I have a need to do this from within TSQL. Actually, I could use AMO from Powershell which is what I'm going to start looking into shortly, but that will take longer. Was curious if this was possible from within TSQL and furthermore, any suggestions on this topic (outside of fixing our database/cube) are most welcome!
Thanks
March 13, 2013 at 12:07 pm
Found what I was looking for: http://asstoredprocedures.codeplex.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply