Thursday, July 30, 2009

Hyperion Essbase Book


One good news for people who wants to learn Hyperion Essbase on their own.
There are not many books available on Essbase and I know only one book which is available 'Look smarter than you are with Hyperion Essbase' by Edward Roske.

Sarma and Joseph have written a 444 page book on Hyperion Essbase 9 with the title

'Oracle Essbase 9 Implementation Guide'.

The book covers Installation, Essbase cube designing and creation, loading data, Excel add-in, MDX, Max-L, calculation scripts, reports etc and explained them with real time examples in sufficient step by step details with the help of pictures, Notes and Hints.

The book is for students who are new to Essbase and Multidimensional OLAP concepts as well as professionals who are already working on the Essbase technology wants to refresh their knowledge.

For those who feel that Essbase is only for financial applications, this book will completely change their belief as at one place the author mentioned as
"Oracle Essbase is widely known as a financial analytical tool. We want to change the mindset just a bit, right here and now. Oracle Essbase absolutely is a superior financial OLAP tool, but it is an equally superior OLAP tool for just about any type of data analysis."
As the Authors have rich real time implementation experience in a Motor company, most of the examples are from that domain only. I am sure the readers would greatly benefit from these real time examples.

I am happy to see that Venkat has reviewed this book and feel proud to have worked with him in Oracle.

Though this is a great attempt to bring out such a nice book there are few points which I think should have been added in this book to make it more useful and practical.

1. As Essbase is not like other BI tools where you can visualise the schema and how dimensions are joined or related to facts, it becomes difficult in Essbase to visualise that. For that reason, it would have been helpful if a small cube have been designed with some small dimensions and facts and a relation is shown with a OLAP schema diagram. I am saying this because I myself was not able to understand how the data which is getting loaded into the Essbase cube is related to each other and it took me so much time to understand that. Please excuse me if I am the only one having this problem.

2. I do not think Essbase is complete without EIS(Essbase Integration Services) in version 9.3.1 or Essbase studio in Version 11.
Even though you can do almost all of the Essbase stuff without them but it would have been much better even if a small portion has been described about either of them in the book. As going forward Essbase studio is going to replace EIS, a small chapter on Essbase studio is desirable.

You can download one complete chapter Essbase Data and Design Considerations. By downloading this chapter in pdf format you can also see the list of chapters and table of contents.

All in all, a great attempt and thanks to people involved in bringing out a much needed book on Essbase.

Manohar Rana

Friday, July 24, 2009

Qlikview Personal Edition


Qlikview is the emerging name in the Business Intelligence world and becoming very popular among the user community due to its easy to use interface, fast deployment and very strong visualisation.

Qliktech released its version 9 recently with a new Edition called Qlikview 9Personal Edition having all the developer features.

Earlier Qlikview use to provide only 15 days of evaluation period for people who want to give it a try which is really a very small duration to try or evaluate a Business Intelligence Tool.
This limits the user to use it beyond that period.

Now with version 9, Qliktech allows to download the personal edition from their website for free and use the product for personal use for an unlimited period of time and does not require any key.

So, that in real sense a very good news for the student community as well as for startup businesses or small growing businesses to use Qlikview for their personal use.

As small business generally has lot of data in flat files or excel files, Qlikview becomes a very handy Business Intelligence Software as Qlikview is very easy and effective when you have lot of excel files or combination of flat files, desktop database and relational databases.

Now you can develop or get developed Business Intelligence applications and do your analysis to know more about your business. You can export the reports/charts to excel,pdf,gif or png images and email it to your business partners or other members of your Business.
You can also copy/send the complete Qlikview document to another machine where Qlikview free personal edition is installed.

And in future as your business grow and you have the right budget, you can get your personal edition converted to a licenced version without affecting your Qlikview Business Intelligence Application that you had created.

But there are some limitations of Qliview personal edition. As your Qlikview is not a licensed one, you cannot ask for any support from Qliktech which I think is not a big issue. You can manage your application without any support which generally is required for big deployments.

Another limitation is that you cannot use an application developed in Licensed version in your personal edition however an application developed in personal edition can be used with a licenced version.
The application developed in a licenced version when opened in personal edition does not load the data hence a reload of the Qlikview application with the fresh data is required which effectively means that the person having the personal edition should have all the data(flat files or excel files) or access to relational databases to reload the application.

Another limitation is that you cannot publish your dashboards over the web to share it with other users as the personal edition does not provide the web component which allows the users to access the application over the web using Internet browsers.
However, you can use utilities like desktop sharing, remote login or web conferences to allow someone to use that application but in that case you will have to expose your complete application. The person accessing the application through remote login can delete the application or make any modifications.

These limitations may not be relevant to most of the Students or Business Owners to use Qlikview personal edition for learning Qlikview or Using for your analysis.

Replacing scattered excel files with one single source of information will be a great benefit in terms of managebility, reusability and productivity which will save lot of your time and efforts.
Top class visualisation in reports and dashboards and ease of perfoming analytic operations like drill down, Top N analysis, Pivoting,identifying problems etc will be an added advantage.

From Qliktech's perspective, they have made a very strategic move and will give them benefits in the coming years and will also help them in growing their prospects base and users base. This will definately make Qliktech's reputation much better in the market and help in developing Brand.

Qliktech's major install base or user base is small business or large business with small deployments and this move will make its presence more stronger in this area. Also, the buyers risk on investment will be greatly reduced.

Not many companies provide free unlimited period evaluation versions of their Busniess Intelligence softwares. I know only one name i.e Oracle. Even SAP Business Objects and IBM Cognos does not provide more than 30 days evaluation.

I am hopeful that Qliktech very soon will appear in Gartner's Business Intelligence Magic Quadrant and will give a direct competition to the bigger vendors.

Manohar Rana

Saturday, June 13, 2009

Brio Cell Double Click

Sometimes there is a requirement wherein users would like to see the detailed data by simply double clicking on a data cell value of a summary report. This feature is illustrated in the snapshots below.

Let's say this is a summary report in a dashboard showing total sales value for some particular product category.
Now for example if a user want to see the detailed breakup of value '250169.11' which is 3rd in the row.
Here the user just need to double click on the cell and the detailed breakup report would appear as shown below(This is just a portion of the detailed report).

Today we will see how we can do this in Brio or Hyperion Interactive Reporting.
Here the datasource for the summary report as well as the detailed report is same.
To perform this-
1. Create a query as usual by making a database connection and selecting tables. The request line items should contain all the columns that may be required in the summary report as well as in the detailed report.
2. Process the query to retreive the results.
3. Insert 'New Pivot' to Create a pivot having presenting summary level report which then will be displayed in a dashboard.
4. Insert 'Table report' to create a table report and select all the columns you want to display in detailed level report.
5. Insert 'New Report' to create a detailed report and here select the columns displayed under the table we just created. In the picture below, take the columns displayed under the 'Table1'.

6. Insert 'New Dashboard' and drag the 'Pivot1' we created for the summary report to this dashboard.
7. Under the design mode, Right click the pivot and select 'Scripts'.

8. Under 'Event Trigger', you will notice an event 'Cell Double Click'. Write the code in the main window to perform cell double click event. Please refer the sample code provided below and customise as per your object names and column names.
Sample Code:
SectionTable = ActiveDocument.Sections["Table1"]
SectionPivot = ActiveDocument.Sections["Pivot1"]
ReturnVal = 0ReturnVal = Application.Alert("Do You want to drill?", "Drill to Details?","Drill","Cancel")
if (ReturnVal == 1) {
SectionTable.Limits["Product Family"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Category"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Name"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Publisher"].SelectedValues.RemoveAll()
SectionTable.Limits["Day Key"].SelectedValues.RemoveAll()
for (i = 1; i <= SectionPivot.SideLabels.Count; i++) {
if (SectionPivot.SideLabels[i].Name == "Product Category") {SectionTable.Limits["Product Category"].SelectedValues.Add(Pivot1.SideLabelValues[i])}
if (SectionPivot.SideLabels[i].Name == "Product Name") {SectionTable.Limits["Product Name"].SelectedValues.Add(Pivot1.SideLabelValues[i])}}
for (i = 1; i <= SectionPivot.TopLabels.Count; i++) {
if (SectionPivot.TopLabels[i].Name == "Product Category") {SectionTable.Limits["Product Category"].SelectedValues.Add(Pivot1.TopLabelValues[i])}
if (SectionPivot.TopLabels[i].Name == "Product Name") {SectionTable.Limits["Product Name"].SelectedValues.Add(Pivot1.TopLabelValues[i])}}
9. Now check the syntax and deselect the design mode and in the dashboard, double click a cell value and you would be able to see the detailed break up of the selected value.
Manohar Rana

Tuesday, February 3, 2009

Essbase Application Migration


Here we will see how you can migrate an Essbase application from one server to another using Hyperion Shared Services Console Migration Utility version when two systems are not connected to each other.
We will fist create an application on the target Essbase server and then migrate the application to a file system and finally migrating to the target server.

I have named it MSR_TEST for the demo purpose.

Now lets go to shared services console.

Expand the Essbase server to see the list of all available application.
Also notice the 'File System' menu below essbase.
Select the Application you want to migrate. When you select the application, on the right main window you will notice the list of substitution variable and databases.
If you expand the database, you will see the list of all other artifacts like Outline and rule files.

Check all and click the button Define Migration below. The tabs on the left hand side will inform you about the steps involved.

here we are trying to migrate 'Demo' application. Click next to select the destination.

Now specify the name for the folder you want to create in the file system. Just in case you want to migrate this application to another server connected to the network, you will have to add that Essbase server in the administration services console and then those applications will appear here.

This is displaying the details of the destination selected. Click Next to start the migration process.

If you want to see the status of the migration, launch migration status report.

This report will show all the previously conducted migrations. The first migration is the current one and it is showing 'In Progress'. Click on the refresh button to see the current status.

Our migration has been completed sucessfully.
Now let's see where exactly the files have been saved.

The default directory where files are saved is Hyperion_Home/common/import_export/admin@native_directory.

Just refresh the file system list and you will notice the application 'MSR_TEST' under it. The application migration does not export the cube data to the file system. However while migrating from one Essbase server to another Essbase server, Utility gives you an option to migrate the cube data.
For our workshop, now we will have to copy the entire folder 'MSR_TEST' and transfer it to the same directory of Target server though ftp or pendrive or CD or any other media.
Once you do that, it will appear in the file system.
Select the application 'MSR_TEST' and you will again see the list of artifacts on the main window.
Click on Define Migration button.

click next to select the destination. This time you will only see the list of applications and not the file system as we already have the artifacts in the file system.

Select the application 'MSR_TEST' we created in the EAS (Administrative Services) in th first step for this purpose.
Click next to see the destination options.

Here we get the option to restructure the cube and retain the cube data. I did not tried this as of now hence not 100% sure whether it exports the data. I will try to show that some other time.
Click next to proceed.

Click Execute migration to start.

Launch the migration status report.

Click refresh to get the updated status.

Migration is successful. Lets check whether it appeared in the EAS or not.

Yes, it is all there. But there is no data in the cube.
Let's try to export the data as well.
Right click the cube on the source system which has the data and select export.

You can export the cube data to a file in your system. Provide the path where you want to export and provide the file extn as .bin.

on the message panel below, it will display the status. In our case it is successful.
Now go to the target system cube and right click to select load data. Click on find data file.

Locate the file in the file system tab and select it.

click on Ok to import.

It will display the status as success.
Lets check whether it displays the data or not.
Right click the cube and select 'preview data'.

You can see now that we successfully migrated an application and exported the data also using Migration utility from shared services console.

Manohar Rana

Tuesday, January 27, 2009

Interactive Reporting with Essbase


Today we will see how Hyperion Interactive Reporting treats Essbase.
First open the IR studio and make a connection with Essbase server.

Make a new database connection.

Select Essbase in both the drop downs.
Clicking on next and provide the Essbase server and username details.

Here you will be asked to select the cube and dimensions. Select any dimension and finish.

This is how the cube dimensions will appear and on the main window you can drag and drop the dimensions or members.

You can analyse cubes this way but I did not see the options enabled to create charts while working with Essbase and functionalities looked limited.
Then I thought of exporting the cube to a flat file from IR and then import it again.

This is how it came.

Now you can work normally with the flat file and create queries and charts with this.

Though I did not like the way it works with Essbase but I hope this would improve in future releases.
Today I tried something which again basically flattens the Essbase cube. I am sharing it with you.

Once you define your query, go to Query-Download results.

This will flatten the Essbase cube and your results will get displayed. Now go ahead and create your charts and dashboard as usual.

Now this is pretty good.

Manohar Rana

Hyperion Financial Reporting- Row Headings Before


Some of my customers saw this type of report in one of the Oracle's financial reporting datasheet and asked me whether that has been made in Financial reporting studio only and if I can show him how to do that.

So just thought to share it with all of you as well.

Today we will quickly see how we can create such reports in Hyperion Financial Reporting.

Select your rows and columns as shown below. Those who are working with the Financial reporting for the first time or do not know how to reach this stage, Please refer
Financial Reporting Tutorial

You need to have two columns for this purpose. Select the grid and you will see some options comeing up on your right side.

Under "row headings before", select 'column B'. This means that you are asking to place the rows before the column B which is "marketing" in my case.
See the report preview and you report will look something like the first image.
Similarly you can make it more complex by adding more columns.

This will show me the results something like this.

You can try more complex and more meaningful reports.

Manohar Rana

Saturday, January 24, 2009

Pentaho - Open Source BI


From past few days I was really very anxious to try my hands on Pentaho.

I heard a lot about the open source Business Intelligence Tools and two names that comes to my mind are Pentaho and Jaspersoft.

So I simply logged on to the Pentaho Website and looked for some demos. Then I looked at the hosted environment. The website is bit confusing and a new visitor like me found it really difficult to search for things may be because there are lot of things there.
Anyway, the good thing I found is this 'Try our Hosted Demo' which not many websites has.

The first screen after logging in looked pretty simple.

This is good. End users generally gets confused if you have so many buttons and they start loosing insterest from the first step itself. If the interface is simple, it gives them some sort of relief and confidence. The same is true with me.
Upper Left side pane shows the folder available and below it will show the files available in that.
The three icons in the middle of the center window provides you options to choose to create a simple tabular report(or a print quality report) or to create some charts. The third button is unclear.
Let's try on the first option first.

OK. This is a wizard. End users love wizards. If you can do something by simply clicking on Next button using defaults, nothing like that. so far so good.
Here I am not considering how the datasets are selected or from where the data is coming simply because the hosted demo environment does'nt have anything like that. So that's a seperate story and I will try to uncover that as well.
So selecting a Business Model, I think will be selecting a cube created for the purpose. That means we are doing OLAP. Now its not clear as of now what technology Pentaho uses, I mean ROLAP or MOLAP. That's a question as of now.
Let's move on and see what's there in Next.

that's a standard wizard. Available items and selected items. There is a distinct selection check box. I think here we do not have option to select which items we need to have distinct values. Checking this option will give distinct values for all items. Little confused here. No worry. let's move ahead.

Here we have an option to categorise the items under levels and put filters as well. Though I did not checked these options, I expect them to be standard.

Formatting options. Good. Users can do such things in the wizard itself. We have a huge list of paper options as well which can help in getting better print quality. You can preview the report in various standard formats. HTML, Pdf, Excel etc. Now the Next button has got disabled, that means we are all set to go and see some action now. Let's click on GO.

Here is my report. Not looking that bad. If I can get a report like this just by clicking on Next button, I am fine with that. But now I know I can make it much better. Interesting so far.
Lets move ahead and create an analysis report.
Simply click on the button having a chart icon on the top bar.

A small window appeared. choose the schema and the cube. I think this schema is similar to Essbase application which has multiple cubes inside it. The application generally is based on the functional aspect of an organisation like sales, finance and inventory etc..
For this demo we just have one schema and a cube inside it. Let's click on OK.

Now wait for a minute and take a look a it. Some buttons appeared on the top. In a way this is good to present the necessary buttons only. I mean when we were creating the static report, these buttons were not there and that saved a lot of confusion probably. Adding things could be easy but to make things clean and simple is really difficult. Let's appreciate this.
Let's click on all regions(+).

I generally like this kind of pivoting using +_ signs. Not every tool have this. I did not see this in Oracle OBIEE ans while talking to customers, I have observed that this is the most preferred way of having a pivot report. Also, one more thing I liked here is the ability to expand the departments for a particular region.

Now let's see what does these buttons means. The first button looking like a cube is OLAP Navigator. From this what I understand is you an add or modify the cube members. Click on it.

The icons are self explanatory but I did not understood there use until I tried them. So here you can change them to appear in rows and columns. By default, dimensions are under rows and measures under columns which is pretty logical. You can rearrange the order of their appearance and also apply filters. So I created a filter to have only one region.

Here is some difference. Here you can check or uncheck the members. But I did not see anything to select multiple members. I need to tick them individually to select. Here if we had that available and selected sections, it would have been much better. Because in actual scenarios, you have a huge list of members in your dimensions and in those cases it would be practically difficult to tick each member manually.
Also, I wanted to remove or hide Positions dimension from my report and failed to find any option to do that. Quite possible that I am not able to find it but if it not there, Pentao will earn one more negetive point.
Ok let's move ahead and look at the next option. The button says MDX. let's look at it.

It seems the database is Multidimentional but which one is not clear. Quite possible that Pentaho can connect to multidimensional sources directly. In that case, this would be very useful tool.
Other few options are very general like sorting and changing the layout, swapping rows and columns.
I am very eager to see some charts now. let's click on the chart button. I hope this would be some wizard based.

I clicked on it. no wizard but nothing less than that. A chart got created automatically. That's OK.
I actually wanted to see the options. There is another button besides the chart button. That is to edit the properties.

Options looking pretty OK. But I did not see anything in case I want to have the numbers or figures appearing on the bars or atleast on the chart against legends. Almost all standard chart types are available.

Two uncommon charts which I found are Pie charts by column and by rows. Not all the tools does have these charts. At the same time I did not see other advance charts like scatter charts. I am not sure if it has gauge charts and sliders. Quite possible that this may be available in some other version or component. One thing wrth mentioning is drill through capability. In properties only there is an option Enable drill through. I checked it. Lets see how does it work.

Here I selected pie charts by columns just to see how does it look. Charts are fine. Enabling drill through activated the hyperlink on the charts. clicking on the chart will create the drill through report.

This is fantastic. Generally creating drill through reports are not so easy. But this is something really easy and user friendly.
Now lets take a look at some sample dashboard.

Here on Dashboards, I see the values are displayed. But I am not sure whether these are text boxes created for the purposes or available in properties. The charts were also not interactive. I mean when I clicked on the region, though the sales trend chart below changed but didnt show any option to drill down. My guess is the first chart(Territory) is static with values pasted pasted on it. In that case the dashoard needs to be made more functional and interactive.
Another dashboard which attracted my attention is this one.

I dont know how this has been made or how difficult it is to create something like this but it's looking pretty interactive. On the map, if you click on any region, the values will pop up like this.

Overall, the tool is user friendly, easy to use, easy to understand, easy to manage and having good reporting, ad-hoc query, dashboard and OLAP analysis capabilities.

Though I did not checked its scheduling, notification and advance functionalities like row level security which may be out of the scope of this onlie demo. But I will try to gather all these information and probably will try to download the tool and loo at it from the scratch.

Any inputs from experts are welcome. Please spare me if you find anything incorrect and let me know.