OBIEE SmartView Part 3 – Your own secure mini reporting book

Your own secure mini reporting Excel workbook or NOT?

OBIEE SmartView Part 1 – Creating your first OBIEE SV Report

OBIEE SmartView Part 2 – Connecting to Essbase

It is required to have the skills from Part 1 and Part 2 before you can fully understand what I am doing here. I will not provide screen shots for every step.

What you will achieve after reading this is a way to create your own mini reporting book or even a dashboard with full security and data refresh when needed.

First, you are required to create a new workbook and from Part 2, create Excel Table in sheet 1 (ExcelTable sheet in Part 2) and for the chart, click on cell H1, then click View Designer then follow steps in Part 2 to create the Line Chart. Your result should look like this:

OBIEE--SmartView-Part3-021

 

Your own mini report has been done. You can create a dashboard in the same way. You can even use Excel Sparklines to add to your report. Let’s save this workbook for now. Since I’m lazy, I’ll save it to desktop and use the default name, Book1.xlsx

OBIEE--SmartView-Part3-020

Securing your OBIEE SmartView Excel Book

After you save it, close the workbook and open it again. You’ll see that all data is kept there. Like below:

OBIEE--SmartView-Part3-019

I suggest that once you have finish designing your mini reporting workbook, use a feature called Mask Workbook Data. This way, the whole workbook gets data masked and that Refresh is required to reload the data every time you open the book.

OBIEE--SmartView-Part3-018

After clicking Mask Workbook Data, you will see that all figures are replaced by Need refresh. Save this workbook again. Since the workbook is saved, close it and open it, and you will see that it’s still the same screen as follows as expected.

OBIEE--SmartView-Part3-017

What you need to do to get data is to click Refresh Workbook Data.

OBIEE--SmartView-Part3-016

Since you closed the Excel and started again, it asks for your user name and password. Fill in the missing piece.

OBIEE--SmartView-Part3-015

Magic! But seems a little ruined. Let’s fix it if you see the problem.

OBIEE--SmartView-Part3-014

Click the Chart, then go to Chart Tools Design Ribbon, your charting theme should be highlighted already, all you need is to click that theme again.

OBIEE--SmartView-Part3-013

Done!

OBIEE--SmartView-Part3-012

 For the next part, I need you to close the Excel, leave it unsaved so that it’s still under Masked Data.

SmartView OBIEE Undercovers

Open the Excel with either 7zip, WinRAR or WinZip. We’re gonna take a look at how SmartView works with OBIEE.

OBIEE--SmartView-Part3-010

If we edit workbook.xml inside xl folder, you will be able to see that there’s a sheet called ChartData1. Funny thing is, if you tried unhiding it through Excel, you’ll need to write VBA script. Nonetheless, we can do it this way too, delete the words “state=veryHidden”, now save and close the editor.

OBIEE--SmartView-Part3-011

Close the 7zip, and allow it to save to the file.

OBIEE--SmartView-Part3-008

Oops, SmartView Hacked!

Now you can open the workbook Book1.xlsx and you just hacked SmartView since you see the tab with ChartData1.

OBIEE--SmartView-Part3-007

 

I gave it some thought whether I should disclose this information before writing this article since it’s affecting security. I decided to go for it and log SR so that this issue gets fixed. I feel that everyone should know how secure your data is with SmartView OBIEE before you decide to use it or not. Which way you should use it. If you look at ChartData1, you will see that all the data is here. No!!! I masked it didn’t I? Well, I think Oracle forgot to mask these data since it’s “VERY HIDDEN”.

OBIEE--SmartView-Part3-006

Let see if it really works. Delete the data and save the workbook.

OBIEE--SmartView-Part3-005

Close the workbook and open it again, refresh workbook data and magic, data is back! So in conclusion, yes, they can mask it.

OBIEE--SmartView-Part3-004

Let’s take a look at other xml items using 7zip again. If we look at table1.xml, we can see that it really does get masked.

OBIEE--SmartView-Part3-003

Let’s check item1.xml and we can see that ChartData1 does have all connection information and Answer column IDs.

OBIEE--SmartView-Part3-002

If we check customProperty1.bin, similarly we can see all connection information Column IDs, dimensions used etc.

OBIEE--SmartView-Part3-001

To conclude, SmartView OBIEE is excellent tool to create your own Excel dashboard. You can even share you Excels in the catalog and still retaining security if you used the Mask Data feature. The only flaw is when you use charts built using View Designer but I don’t think a lot of people will realize that you can get around to unhide the very hidden sheet.

That’s it for part 3.

Advertisements

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist

One thought on “OBIEE SmartView Part 3 – Your own secure mini reporting book”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s