It is very important for business users to understand the business names of the fields that are used inside the cube than the actual table field name. Using DMV queries we can pull most of the cube meta data but I failed to get the database source field used to generate the cube field. I wish Microsoft will take care this in future release.
I proposed below logic to the customer to track the source field and the business logic if any so that it is easy to automate and provide the business users through MS-Excel.
we can see description field inside dimension properties window as shown in Fig 1 which is a free text and it can accept very long text. So I urged the cube developers to provide all the source field name and the business logic used inside here so that this can expose to the MDX query.
 |
| Fig 1 |
deploy the cube and query the metadata and see the description field.
 |
| Fig 2 |
Now if we can access this details from SQL using open query, we can create view and use this view to populate the Excel report.
To access this metadata query from SQL, create linked server and hit the below query.
 |
| Fig 3 |
 |
| Fig 3 |
Comments
Post a Comment