Why would you use OBIEE SmartView Connection and Connect to Essbase?
Before we continue, you need to know how to use SmartView connecting to OBIEE, which was part 1 and can be found here: http://goo.gl/mE23az.
So why would you use OBIEE SmartView instead of SmartView Provider Services when connecting to Essbase? For 3 simple reasons:
- Easy creation of Tables instead of Pivot Tables. This could be used for data export, or filtering dimensions that you don’t want to be displayed.
- Creating simple charts without too much Excel skills.
- Sharing your report through OBIEE Catalog.
I’m sure you will think of other reasons, but let’s just start with those common ones for now. You might also want to read OBIEE Integration with Essbase before you continue reading: http://goo.gl/idX4Ml.
Building your first Sample Basic Report Table
Let’s start by having a new excel workbook. On the first worksheet, Create a new Designer View using Sample Basic.
Add G3: Month, Measures – Default, G3: Product, G2: Region, G2: Scenario, Fact to Row.
Add Measures and G2: Scenario to Filters where Measures is in COGS; Marketing; Misc; Payroll; Profit. And G2: Scenario is in Actual; Budget
Click OK and you will get a nice Excel Table with filters and standard Excel Table features. Well, you never can get this type of table as simple as OBIEE SmartView. What you can do with this now is to Save as CSV or whatever you want.
Rename this sheet to ExcelTable if you like. We will start on another sheet for the next part.
Creating Charts with OBIEE SmartView (Connecting to Essbase Cube though)
On Sheet2, cell A1, click View Designer. Choose Line Graph as view type and Excel Chart. Place G3: Month on Group BY and Vary Color by G2: Product Group. Your data will be Fact.
Add Filters G1: Market Total is in Market, G2: Scenario is in Actual and Measures is in Sales.
Click OK and you’ll get an Excel Line Chart. Magic!!!
Click on the title and edit to make some sense. Also modify the Y Axis label to Sales.
Right click on Y Axis and choose format Axis.
In Axis Options, choose fixed for Minimum and Maximum then fill in 6000 and 10000 respectively.
Last of all, enlarge the chart and then go to Design Ribbon and choose the style you like.
Rename your sheet to LineChart. Your final outcome should be like this.
That’s it for Part 2.