I have to verify that olap cube data and the data from relational tables from where a cube is built is correct. And I will do so by writing the TSQL queries and compare the values with that of cube.
But, I got stuck in the course of determining which columns are used for measure. How do I figure out which columns are used for measures?
Help appreciated!
You need to look at the cube metadata.
For SSAS2005, take a look at the DSV (data source view) and mappings to dim and fact table values behind the scenes. This should allow you to see what is going on. If you don't have a project you can reverse engineer it using the 'import anslysis services template' (or some such) option from the new project dialog in BIDS.
Calcuated measures are defined in the cube script. If you have a reverse-engineered cube or cube project you can open the cube and see this in the 'calculations' tab.
For AS2000 you can open the cubes on the server (assuming sufficient permissions) and look at the mappings there. There is a tool called OLAPScribe that will help you do this for AS2000. Alternatively you can run a trace on the source database and capture the SQL generated by the cube as it is processed.