Wednesday, January 28, 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

Sunday, January 25, 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.

Thursday, January 22, 2009

Gartner BI Magic Quadrant 2009

Yesterday just got a chance to look at Gartner's Magic Quadrant for Business Intelligence 2009.
I am really happy to see Qlikview almost touching the Leaders Quadrant Boundary and may be by next year Qlikview will join the leaders club.
So that's a good news for people who have invested in this technology.
This would be really interesting to watch how Qlikview will compete with the leaders. The strong points mentioned by Gartner are OK but I love to look at the weak points because thats the only area where you need to put more efforts and will eventually decide on your success.
1. Lack of statistical and predictive modelling: Thats the key area where Qlikview needs to improve heavily to be able to compete with other leaders. At present, Gartner named some of the competitors as Tibco and some other small products and while doing that it says it is behind them as well. If Qliktech fails to address this quickly before getting into leaders quadrant, it will become very difficult to move forward or maintain its position.
2. The fear of Qliktech being getting acquired will have some impact on the prospects. The prospects will get more cautious and may look for other options which are more certain and safe.
Prospects do not want to suffer from the change in policies, product names, re-architecturing etc and want to play safe wich is fair enough.
3. Gartner feels Qlikview still requires more examples of Large BI deployments and stressed on saying that it has not moved further in this area as compared to last year. I feel this will remain a challenge until Qlikview make some improvements in the architecture to deploy on large environment.
4. The last point may be very dangerous for Qliktech. People who were involved in large deployments understand the importance of Metadata management. making quick reports and good reporting capabilities are good but metadata management is the second pillar on which the deployment stands. If Qliktech fails to address this soon, it will definately be very difficult to get large deployment examples.

You may also want to read Qlikview vs Others which has some discussions on the pros and cons.

This was about Qlikview, the tool which I personally love.
The other interesting things which Gartner mentioned is inclusion of some open source BI tools like Jaspersoft and Pentaho. I hear a lot about Pehtaho and would love to include a review for this in my blog soon but before that I would like to try my hands on that or read some technology information whitepapers.
Now with the inclusion of open source BI, these tools will get some acknowledgement and people will have a choice to look at them as well.
Other open source which I am hearing a lot is Jaspersoft.

Another thing which Gartners mentioned is the SaaS(Software as a Service) BI tools. This may be good for products based on some properitery technology to store the data which has a potential risk of migrating the complete application if customer chooses to shift to a new technology or if the vendor plans to de support or does not provide a way to integrate with other technologies. In that case, customer has absolutely no choice other than to competely rebuild the entire application on different platform which I think will not be an easy and economical task.
I really dont have any idea how SAAS vendors make sure customers investment is not affected if anything of such sort happens. If someone can provide an insight would be helpful.
The new names which I never heard are Pivotlink, Lucid Era and Oco.
to be continued....

Thursday, January 8, 2009

Security Filter Essbase Tutorial


Today we will see how you can create a security filter in Hyperion Essbase and restrict a user to see only the permitted data.

Let's say I want to restrict a user to have access to only January Month.

We will first start with creating a user in Essbase native security with non-administrative rights. Then as our next step we will create a filter of January month and then assign this filter to the user we just created.

Log on to Hyperion Administrative services console with admin user. Under Essbase Server go to Security and expand it.
Right click on the users and select 'create user' option.

Type in your username and provide the password.

Select the default options. Click on Apply to finish.

So we have completed our first step. Now lets create a security filter.

Now select the database on which you would like to create this filter. I have selected 'Basic' Database under 'Demo' Application. Right click on the database and select create-filter.

Provide a name to the filter. On your left side you will see the all the dimensions available in the cube. On Right hand panel you will see 'Access' which allows to define type of access like Read, Write and 'Member Specification' allows you to difine the member( which in our case would be Jan Month).

Select 'Read' in Access as I just want the user to read information.

From left panel, Expand Year-Qtr1. Double click jan and it will get added in the member specification. If that does not come, simply type the member with double quotes.

Click on Verify button and you will see the success messege in the panel below. Don't forget to Save it.
So our security filter is created.
Now we have to assign this filter to the user we created in our first step.

Again under security, right click users and select 'Display user Tables'.

Select the user and click on Edit or simply double click the user.

Select 'App/Db Access' Tab and select 'Access database' option for 'Demo' Application from the list.

Select 'Filter' option for 'Basic' Database.

As soon as you select filter option for Basic, Filter just below that will get enabled and you can see all the filters you have created. Select the filter you just created. Click Apply to save.

Now we are ready to see the results. We will see the results in Excel Add-in.

connect to Excel Add-in.

Provide the username and password of the user we created for this purpose. You will notice only one application appearing in the list. Clock 'OK'.

Retreive the results.

Now you can see that the user is only authorized to view results for Jan month only. As we did not provided any options for products or other dimensions, it will by default grant access to all products(Visible on the screen above). You can further restrict user for a particular product or products or any other dimensions.
You can also create complex filters using substitution variables for situations like you may want to grant access to current quarter.

Manohar Rana