Tuesday, May 27, 2014

Explore cube using microsoft excel

The cube we have created in SSAS in previous post, can be explore, drill down, pivot using microsoft excel.
Parallel excel shows the aggregated data and it also show the related graph for the same cube.

Below are the steps to connect to excel with ssas engine.

1. Open DATA> From Other Sources > From Analysis server
and you can see the below image.
- and the server name of sqlserver so that it can connect to the database

2. Next , now you can see the next page in the wizard. We have to select the database from drop down menu.

 3. Finsh the Wizard after selecting the database from drop down menu.

4. Below popup window now you can see, I select "PivotChart and PivotTableReport" means it will show us the data as well as the graphical chart for the report.

5. If you see the below image carefully, on very right hand side top, similar window you can see in excel and you can check the columns to add it into chart. Where i check the "sales Amount" , "Tax Amt" , "Profit" columns.

From column A i.e. Row Labels is a hierarchy column based on "product color".
Column E and F reflecting the KPI Goal and KPI status again i have check the value (using check box) from right hand side top window.

We can the dimension by check or uncheck the column and can observe the charts and we can track the organization goals.


No comments:

Post a Comment

web stats