Business Intelligence – Oracle

Archive for April, 2008

Oracle BI EE 10.1.3.3/2 – Understanding Dashboard Prompts – Passing Prompts to multiple Subject Areas – Aliases

Posted by Venkatakrishnan J on April 30, 2008

I got a question the other day with regard to passing dashboard prompt values to multiple reports from different Subject Area’s. The user’s questions was “If i have a dashboard prompt on a column from one subject area, will i be able to restrict the data on a report from another subject area?”. Thought the question is pretty straight forward, i thought this warrants a detailed explanation of how the dashboard prompts work. As always, lets understand this through an example. We shall be using the default Paint and Paint Exec subject areas. Also, as we move along we will build more subject areas to see the impact of a dashboard prompt on 4 different reports from 4 different subject areas. Lets first start with creating a simple dashboard prompt on the year column of the Paint subject area.

    

Note down the Presentation Table Name and Presentation Column Name of the above prompt. It is “Periods”.”Year”. Now, lets go to the Paint Subject area and create a simple report containing Year and Sales. Ensure that you have a “is prompted” clause filter on the Year column.

    

Similarly, create the same report using “Periods”.”Year” column and the sales measure column on the Paint Exec subject area. Also add the is prompted clause filter on the year column. Now, put the dashboard prompt and both of the reports in a dashboard page. Now, if you change the year value in the prompt, both the reports would get affected.

    

Now, you have noticed that both the reports get filtered because the Presentation Table and Presentation Column names for both Paint and Paint Exec Subject areas for the Year column are the same. Now, lets create a copy of the Paint Subject area and rename the Presentation Table of the Year column to something like “Periods New”.

    

Now, create the same report as shown above i.e Year and Sales report with an “is prompted” clause on the Year column. Add this report to the dashboard. Now, if you notice, the filter would not be applied to the third report.

    

This would be the typical use case. One would have different subject areas with different names and we would like to apply filter from a dashboard prompt on one column to a report on another column in another subject area. So, in order for the filter to be applicable without changing presentation Table and column names, we have something called as “Aliases”. In our case, add an alias to the Periods New presentation Table with same Presentation Table name of the column that was used in the prompt. In our case it is Periods.

    

Just by adding the filters, the filters would not be applied. Go back to the 3rd report we created. In the column formula of the year column (this is important. Aliases would not be resolved internally. We would have to put their names in the formula explicitly), change it from “Periods New” to “Periods”. Now you would notice that the filter would be applied on the 3rd report also.

    

    

In our example, all our subject areas come from a single BM. What if we have completely 2 different subject areas from 2 different Business Models? The above would work even in that case too. Ensure that your reports refer to the aliased columns instead of their original column names. The below screenshot shows a 4th report (SA from a different BM) filtered using the same technique.

    

So, in a nut shell, if you want a dashboard prompt filter to be applied to reports sourcing different BM or SA’s you basically would have to add an alias of the dashboard promt column’s Presentation Table name and the Presentation Column name to the report columns. This is not applicable if you are using presentation variables. Aliases are not required if you are filtering reports by presentation variables.

Advertisements

Posted in All Posts, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on multiple character columns in a Pivot Table

Posted by Venkatakrishnan J on April 29, 2008

Use this approach only if you have no other options. Also, this would work only if you have non-numeric columns(on which you want to apply conditional formatting). Not an elegant solution but would work neverthless. As the title of this blog suggests we shall be looking at an approach to enable cross column conditional formatting on Pivot Tables. You might probably be aware of the fact that BI EE does not support cross column conditional formatting in Pivot Tables. This is stated very clearly in the New Features Guide here. But what if your end user somehow wants this feature. In such a case, just follow the below mentioned procedure. Lets start with a simple Pivot table report as shown below

    

The above report gives a report on the Region, Brand and their corresponding sales. Now, our aim is to highlight all the Regions in Red whose Brand value is Enterprise. In a tabular view this is straightforward since it supports cross column conditional formatting. But since in our case this is a pivot table, go back to the criteria tab and edit the formula tab of the Region column. Type in the below formula.

‘<!– ‘|| Products.Brand||’ –>’||Markets.Region

    

Now, convert this column to a HTML format.

    

Once this is done, go to conditional format of this column and give the necessary conditional formats (You would have to choose all the 4 Enterprise Brands||Regions. This is the main drawback with this approach. It would really nice if conditional Formatting supports the containsAny operator ).

    

    

Now, if you go to the pivot table you would notice that all the regions which are under the Enterprise Brand would be highlighted. The idea is to basically concatenate the columns and then apply the conditional formatting. The formula above (which is nothing but HTML commenting) will avoid the display of the columns multiple times.

    

    

Not the best solution but can be useful if you somehow need to use cross-column formatting in pivot tables.

 

Posted in All Posts | 5 Comments »

Oracle BI EE 10.1.3.3.2 – Handling Sort Order in Hyperion Essbase 9.3.1 – EVALUATE and MDX

Posted by Venkatakrishnan J on April 28, 2008

Another common question that one would get while working with BI EE and Hyperion Essbase is “How to have the default sort order specified in an Essbase Cube to be available in BI EE reports?”. This is a very valid question since by default BI EE sorts the dimension attributes alphabetically. For example look at the screenshot below,

    

As you see above, BI EE by default has alphabetically sorted the channels report. But if we look at the outline of the channels dimension, the sorting order would be completely different. In most cases, we would like to have the same sort order as the cube (not an alphabetical sort).

    

In order to overcome this, there are 2 approaches. Both of them would involve the use of EVALUATE function and then passing an MDX function to the cube to fetch the rank of each dimensional attribute. So, lets look at the first approach. In this approach we shall be adding a new column and in the formula enter the below formula

EVALUATE(‘RANK(%1.dimension.currentmember,%2.members)’ AS INTEGER,CHANNELS.”Gen4,CHANNELS”,CHANNELS.”Gen4,CHANNELS”)

What this basically does is, it passes the member of the dimension to the MDX function RANK and retrieves the position of the member in the particular level.

    

    

Now hide this new column and apply sorting on it. This will give the default sort order as available in the Essbase Cube.

    

The problem with the above approach is that the sort order would have to be created for each and every report. In order to overcome that, create another logical column in the repository with the evaluate function and then apply sort order on the 4th level based on this column.

    

    

Also, ensure that you have assigned an hierarchy level to the new column.

    

    

Thanks to Alan Lee for sharing this. One can extend this to provide lot of different functionalites that BI EE does not offer out of the box.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – SA System Subject Area – Autoloading Profiles and Bursting

Posted by Venkatakrishnan J on April 25, 2008

Those users who have worked with Siebel Analytics 7.7 or above, they would be aware of a special subject area called SA System subject area that was necessary to set up Delivers. But with the advent of new releases(10.1.3.3 and above), SA System subject area is not mandatory any more. But still SA System Subject area can be very useful in a lot of scenarios. One of the major uses of this special subject area is that, by setting this up one can automatically populate the Delivery Profiles of all the users. If this is not setup, one would have to manually go into My Account section of each and every user and then set it up. For example, a typical use case is, you would be having the list of users and their corresponding email ids in an excel file. You do not want the users to manually go and enter their email ids. In such a case, just setup the SA System subject area, then all the users would automatically get their email ids, phone, pagers etc automatically assigned to their profiles. Today, we shall see how to go about setting this subject area. Lets start with a simple table script that would help us to store all the Users, their corresponding Groups and also their email ids, pager and phones. Remember, SA System subject expects you to specify all the shown columns below.

CREATE TABLE SA_SYSTEM
(
EMAIL VARCHAR2(100),
EMAIL_PRIORITY VARCHAR2(10) DEFAULT 'HNL',
EMAIL_TYPE VARCHAR2(50) DEFAULT 'html',
CELL_PHONE VARCHAR2(40),
CELL_PHONE_PRIORITY VARCHAR2(20),
PAGER VARCHAR2(20),
PAGER_PRIORITY VARCHAR2(30),
HANDHELD VARCHAR2(20),
HANDHELD_PRIORITY VARCHAR2(30),
TIMEZONE VARCHAR2(100),
GROUP_NAME VARCHAR2(20),
LOGON VARCHAR2(50) NOT NULL,
DISPLAY_NAME VARCHAR2(100),
LOCALE VARCHAR2(20) DEFAULT 'en',
LANGUAGE VARCHAR2(20) DEFAULT 'en'
)

    

Assume that we have 2 users <b>Administrator and Sample</b> in our repository. So, lets enter all their relevant details into the above table.

INSERT INTO SA_SYSTEM(EMAIL, GROUP_NAME, LOGON, DISPLAY_NAME) VALUES (<a href="mailto:'venkatakrishnan.janakiraman@oracle.com','Administrators','Administrator','Administrator'">'venkatakrishnan.janakiraman@oracle.com','Administrators','Administrator','Administrator'</a>);
INSERT INTO SA_SYSTEM(EMAIL, GROUP_NAME, LOGON, DISPLAY_NAME) VALUES (<a href="mailto:'krisvenky83@gmail.com','Administrators','Sample','Sample'">'krisvenky83@gmail.com','Administrators','Sample','Sample'</a>);
COMMIT;

Lets look at what each of the above columns do

EMAIL – Stores the email addresses of the user.
EMAIL_PRIORITY – Can take 3 values. ‘H’ for High, ‘N’ for Normal, ‘L’ for low. ‘HNL’ means High, Low and Normal. You can use these in any combination.
EMAIL_TYPE – Can take ‘html’ or ‘text’. This primarily tells whether the email client supports html or only text.
CELL_PHONE – Cell phone details
CELL_PHONE_PRIORITY – Same as EMAIL_PRIORITY
PAGER – Pager details
PAGER_PRIORITY – Same as EMAIL_PRIORITY
HANDHELD – Handheld details
HANDHELD_PRIORITY – Same as EMAIL_PRIORITY
TIMEZONE – This can be Null. Basically tells the timezone of the user.
GROUP_NAME – The actual GROUPs to which the Logon user belongs.
LOGON – User Login Name
DISPLAY_NAME – User Display Name
LOCALE – Default it to ‘en. It specifies the user Locale.
LANGUAGE – Default it to ‘en’. It specifies the user Language.

Now, import the above created table into the repository and design your BM and Presentation Layers. You should rename the Presentation Layer columns to the one shown below.

Cell Phone
Cell Phone Priority
Display Name
Email
Email Priority
Email Type
Group Name
Handheld
Handheld Priority
Language
Locale
Logon
Pager
Pager Priority
Time Zone

If you use any other names, SA System subject area would not work.

    

Once this is done restart the presentation services. Now login as Administrator and go to Settings -> My Account. You would notice that the delivery profile would automatically be populated and also the email id would also have been associated to the profile. Same would be the case for the Sample user.

    

    

Sometimes this can be very useful. But always remember, if any user goes and modifies their delivery profile, that would take precedence over the SA System Subject area.. One other advantage of using this SA system subject area is that you can use this to do email bursting of reports. For example, in most cases, not every user would have the delivery profile created. In such a case, just load up the SA System subject area and then assign the email ids to the users. Just create a simple report on the SA System subject area to have a display on the LOGON names.

    

Use this in the conditional request and pass the LOGON field to the list of recipients.

    

This would automatically do the bursting to all the users. This would be very handy while doing mass email deliveries.

 

Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3/2 – Overriding Autogeneration of Dimension Level Names of Hyperion Essbase 9.3.1

Posted by Venkatakrishnan J on April 23, 2008

I got a simple question from an user yesterday who basically wanted to know how to have custom names for different levels while importing an Essbase cube. If you are not sure what i mean, look at the screenshot below.

    

As you see, this is nothing but the sample Demo cube which has been imported into BI EE. If you notice, for the Market Dimension, all the levels have names like Gen1, Market, Gen2, Market etc. The question from the client was how do we make BI EE to extract the names from the cubes themselves rather than manually updating them in BI EE. Hyperion Essbase users would know how to do this very easily since all we would have to do is to update the Generation Names for all the levels in the dimension. But for users who are not aware of what Generation and Levels mean in Essbase, the following screenshot would be useful.

    

As you see, Generations are nothing but the reverse of a Level. So, in order to import user defined level names into BI EE, we would have to open the outline of the specific cube using Essbase Administration Services. Then right click on the Market Dimension and click on Generations.

    

    

Now, specify a name for each level (number for the levels would be in descending order). So, use the names All Markets, Region and City for the 3 Generations.

    

Save the outline. Now, if you import the cube into BI EE, you would see that the names too get automatically imported.

    

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 1 Comment »

Hyperion Data Integration Management (DIM) – An option for loading data into Essbase cubes – First Impressions

Posted by Venkatakrishnan J on April 22, 2008

Before Oracle actually acquired Hyperion, Hyperion and Informatica had an OEM agreement(this is still in place like the OEM between Oracle and Informatica for BI Applications) which allowed Informatica to load data directly into Essbase Cubes. Also, Hyperion rebundled the software (under the purview of the OEM) as Hyperion Data Integration Management. What this suite basically does is, it provides ETL options to a variety of Hyperion related products like Essbase, Planning and Financial Management. This suite basically contains the following components.

     1.   Informatica PowerCenter Client
     2.   Informatica PowerCenter Server
     3.   Informatica PowerCenter Repository Server

Also, it contains adapters to the following Hyperion Components

     1.   Hyperion Enterprise
     2.   Hyperion Essbase
     3.   Hyperion Planning
     4.   Hyperion Financial Management
     5.   Hyperion Translation Manager

Using the above adapters, one can load data directly into Essbase, Planning etc using the Informatica. On the same lines, Oracle Data Integrator (formerly sunopsis) also has the above adapters. You can download the above components from Edelivery. Remember to read the licensing aggrement atleast once since this entails the use of a third party software (Informatica). The installation of the software is pretty straightforward. All we would need is to install the DIM first and then followed by the adapters. Then the adapters would have to be registered with the repository server in order for the connections to work. The architecture for the Hyperion Data Integration Management is shown below

    

One of the very good features of the Hyperion Data Integration Management is that it has the capability to automatically normalize an Hyperion Cube i.e if you want to load a data warehouse from a Hyperion Cube, all you would have to do is to use the Normalize option which would automatically split the cube into its corresponding dimension and Fact tables. For example. look at the screenshot below. This shows you a simple cube containing the Products and Channels Dimensions. As you would see at a high level, it contains the cartesian product of all the dimension attribute values (very similar to what we have in OLAP Views of Oracle OLAP). I just hope we can have this feature out of the box (instead of going through an adapter).

    

Below is another sample screenshot showing how the normalization would split up the cube into its corresponding relational sources (based on the demo application).

    

I can imagine a lot of use for this DIM product since it has excellent Administration as well as Data Load/Unload capabilities on a Hyperion Essbase Cube. I believe ODI (Oracle Data Integrator) also has this capability. Yet to give that a whirl. To summarize, there are different ways of loading data into an Essbase cube. They are

1. Using Hyperion Administration Services
2. Using Hyperion Integration Services
3. Using Hyperion Data Integration Management and its Essbase Adapter
4. Using Oracle Data Integrator and its Essbase Adapter

Depending on your needs/environment, you have that flexibility to use the tool of your choice. In later blog entries lets see how to go about loading an Essbase Cube using the Hyperion DIM product. 

Posted in All Posts, Hyperion Essbase, Informatica | 9 Comments »

Oracle BI EE 10.1.3.3/2 – Merging of Web Catalogs – 2-way merge with no parent

Posted by Venkatakrishnan J on April 18, 2008

Its recommended to use this approach only if you are completely aware of the Web Catalogs that you are merging. Merging of web catalogs is not the same as merging of repositories since there will be a lot of overlapping objects in any 2 web catalogs. So, its better to understand the implications of trying this. Also, always take backups of the web catalogs before doing a merge. But this can be very handy in quite a lot of scenarios.

The most common method of combining 2 different web catalogs is to copy over the dashboards, reports, prompts, filters etc over from one catalog to another. This is the recommended way of copying over catalog objects from one Web Catalog to another. If you are moving from a development to a test instance then all you would have to do is to copy over the web catalog folders from one machine to the other. Mark has an excellent article here on the above two approaches. But what we shall see today is something that we can use to automate the merging of 2 web catalogs. If you had looked at my blog entry here, i would have shown you an approach to merge 2 repositories. That concept can also be applied for Web Catalogs. This is not a widely used approach because of the possible implications and a lot of overlapping objects. But if you have a lot of reports/dashboards in 2 different instances of BI EE and if you are looking for an automated way of merging them, then read ahead. The concept of merging 2 web catalogs can be summarized by the picture shown below.

    

So, basically we have 2 web catalogs say Paint and BISE1 and we would like to merge the contents of both the web catalogs. Then in that case following would be the list of steps

1. Take Backups of all the web catalogs.
2. Create a blank folder (which will act as a dummy web catalog).

    

3. Open the Paint Web Catalog in the offline mode using the Catalog Manager.

    

4. Go to Tools – Upgrade Catalog. In the Original Presentation Catalog enter the folder path of the blank web catalog and in the modified presentation catalog enter the folder path of the bise1 web catalog.

    

When you do this, this will give you an error saying that shared folder does not exist in the blank folder. Just ignore it since the tool expects the web catalog to be in a proper format.

    

Then it will show you all the differences between the 2 catalogs from a version standpoint. You can choose which version you would like to have. Remember you might not get a lot of options as shown below if both the catalogs are on the same version. You need to ensure that you have the same security model for both the web catalogs. If not then you would have to reassign the privileges of the modified web catalog wherever needed.

    

Once this is done, you would notice that the bise1 catalog objects would now have been merged into paint web catalog.

    

As always ensure that your security setup is proper. The above said option is primarily used for upgrades. But since it uses the concept of the 3-way merge, this can also be used for merging 2 web catalogs. The only drawback is that you would have to thoroughly check for the completeness of the merge. Most of the times you would be better of copying the web catalog objects from one catalog to another. But this should give you another approach of merging if at all you are looking for an automated option.

Posted in All Posts, OBI EE Plus | 3 Comments »

Some News, Updates and Changes

Posted by Venkatakrishnan J on April 17, 2008

Well its been sometime since i last posted anything personal. Couple of weeks back i made an internal move within Oracle and moved into a full time Consulting role. Thngs got finalized yesterday and i might get to meet people more often. Previously my role involved part time consulting, part time biz oriented giving demos, presentations, architecture overviews etc. I hope this is a move in the right direction and i have some plans in place to make some changes to this blog (still not finalized yet. More news to come later).

As a first change in that regard, i have added one more page in this site here. The rationale behind creating that page is, i would like to make the content of the blog here to be driven by you. No, this is not just another forum. Please use the BI EE forums for questions that need quick, immediate answers. Again, this is not a substitute for Metalink which is where you should direct your production issues to. Please post questions that you think will be useful to others if the answer is in the form of a step by step guide. For example, a question like “How do we make BI Delivers to publish reports to a FTP server? How do we call BI Publisher APIs from BI Delivers?” would be of more relevance to that page. I would be blogging about those questions in the near future. You can also post the challenges that you face in using BI EE, BI Publisher, Hyperion etc. I will try to find an answer to your questions and i will blog about them here. If someone else has already blogged about it, i would point you there. But the idea is to have your questions answered in the form of a simple step by step guide. If it is a simple question which has been asked already in the forums, i would point you to the URL. So if you feel that your question warrants a step by step answer or an elaborate answer, feel free to post them there. This is again an attempt from my end to blog about relevant topics which people would need. This might or might not work. Lets see how this goes.

On a side note, lots of people have been asking me about presenting in events like Oracle Open User Group conferences which i have been neglecting so far due to some commitments. Well, i now have plans of attending and presenting in some conferences (yet to decide on which ones though). Any comments/feedback on which conferences to attend are most welcome. Also, feel free to post any feedback with regard to the changes which you would like to see in this blog.

Posted in All Posts, General | 5 Comments »

Oracle BI EE 10.1.3.3/2 – Querying Repository – Changing Icons of Objects in Repository and Getting a list of Repostitory objects

Posted by Venkatakrishnan J on April 16, 2008

I got this question via email yesterday. The question goes like this “How do we get the list of all the objects that are there in the repository and how do we change the icons of the objects in the repository?”. Though the answer to this question is pretty straight forward, i thought i would blog about it since the answer involves a very important utility that can come in handy to a lot of repository administrators. The utility is called as “Query Repository”. It is a very handy utilty and one can query the repository and its corresponding objects using this utility.

    

You can also get the list of all the variables inside the repository and of course, you can export that into a CSV file. You also have other options like you can save a query for future use, you can mark the objects which you use regularly etc. If you want to list down all the variables inside the repository, then just query for all the variables.

    

Coming to the 2nd part of the question i.e how to change the icons of some of the repository objects, just query your desired objects using the query repository option and then click on “Set Icon”. This will list down the alternative icons that are available and you can choose and assign the ones you like.

    

    

    

 

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.3.2 – Handling Attribute Dimensions of Hyperion Essbase 9.3.1 in BI EE

Posted by Venkatakrishnan J on April 15, 2008

One of the dimension types in Hyperion Essbase is an Attribute Dimension. If you are used to Oracle OLAP AWM, Attribute Dimension is nothing but a dimension which maintains all the attributes of different levels in a dimension. Essbase treats all the attributes seperately and one has to model a seperate dimension for handling attributes. Lets start with a simple example of an Attribute Dimension and see how BI EE treats this Dimension. We shall start with the OLAP Model first. In the model, we shall try creating attribute dimension for all the levels in the Product Dimension. Our Product dimension would have a hierarchy as shown below

Product Total ID – Product Category ID – Product Sub-Category ID

Now, we shall treat the following columns Product_Total, Product_Category_Desc and Product_SubCategory_Desc respectively as attributes to the ID columns which make up the above hierarchy. So, in order markup a column as an attribute click on the Dimension in the OLAP Model and go to Edit – Properties – Columns.

    

    

Now, save this OLAP Model and in the Metaoutline drag and drop the attribute columns from left frame to the right. This will automatically create attribute dimensions. We would have to double click on each of the columns to assign the level that an attribute is assigned to. For example, in our case, we need to assign PRODUCT_TOTAL to PRODUCT_TOTAL_ID level etc.

    

    

Now build this cube and import the cube to BI EE. If you drag and drop the cube to BM layer, you would notice that by default the attributes are not assigned to the specific levels since BI EE currently does not support auto assignment of attributes to their corresponding levels. In order to achieve that we would have to manually assign the attributes to the specific levels in the BM layer.

    

    

Lets remove the attribute hierarchies and put all the attributes into a single Logical table as the Product Logical Table. Also, redesign the product hierarchy to include the attribute levels in the hierarchy as shown below (remember, you need to check whether attribute dimension has data properly loaded. Generally when you assign attributes to a specific level the cube is loaded only to that level. There would not be any association between 2 different attributes which you would have to do it manually in BI EE).

    

 

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 1 Comment »