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:

 

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

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:

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.

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.

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

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

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

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.

Done!

 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.

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.

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

Oops, SmartView Hacked!

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

 

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”.

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

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

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.

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

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

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.