SQL Server division returns zero

While trying a small piece of code. I've happened to face a problem with division by any value on an integer is returning zero. 


Tried changing the destination value as decimal but still no effect.






print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost = @PrevEff  / @PrevUsefulCost  
print @finalcost

----------The above statements provided 0 as the output.....


Now, I've tried changing the manipulation part as Casting to float and process it... OOPS... it's working like a charm.....

print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost =  CAST(@PrevEff AS float) / CAST(@PrevUsefulCost  AS float)
print @finalcost


 Cheers,
Venkatesan Prabu .J

MDX Query to fetch the Measures available in the cube

Here is the query to fetch the possible measures available in the Knowledge Cubes 



SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME  ='MDXProjectCube'
 ORDER BY [MEASUREGROUP_NAME]

MDX Query to fetch the possible attributes in a Cube

Here is the MDX query to fetch all the possible attributes related to a cube in the database.


SELECT [CATALOG_NAME] as [DATABASE],

      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
      LEVEL_CAPTION AS [ATTRIBUTE],
      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
FROM $system.MDSchema_levels
WHERE CUBE_NAME  ='MDXProjectCube'  
AND LEVEL_NAME <> '(All)'
order by [DIMENSION_UNIQUE_NAME]

MDX Query to fetch all the Cubes in the database

Here is the query to fetch the available cubes in the SSAS database.


SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1

Cheers,

Venkatesan Prabu.J

Fetch tables from Knowledge Cubes in SQL Server Analysis Services

I've just found a new way of accessing the tables from the cube. Below is the query used to fetch the tables linked with a cube.



SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS 
WHERE [CUBE_NAME]='SourceDataView'

In addition, this view provides more information about the dimensions, table involved in the dimensions.

Cheers,
Venkatesan Prabu .J