Marketing Segmentation, File Export and Dashboards
By @lex on Sep 16, 2008
While delivering an Oracle BI EE course, I came across an interesting requirement. End users want to download csv files and modify them on their workstations. External systems should access these files, too. Of course, this can be accomplished using the standard download links in Answers and Dashboards. However the number of records that these end users want to handle is in the millions, so any system in between (like Presentation Service or the browser itself) would be a bottleneck. Furthermore there are reports of browser crashes when users try to load very large tables.
Given the fact that the Marketing Segmentation module of Oracle BI EE has a standard functionality of producing large export files (comma-separated, fixed width, XML) and a nice interface to control the schema, the content and the output format of the files, it is an ideal candidate for a proof of concept.
First, you need to modify the rpd and create a Target Level, a List Catalog and the accompanying Qualified List Item, which is typically a primary key and glues the former two together. If you are not familiar with the marketing metadata in the rpd file, here is some documentation and there is also a nice training you can book.
Second, open the BI Client and navigate to More Products > Marketing > Create a List Format. List formats define the columns you would like to export and the output format (CSV, XML or even direct inserts into a database table).
Here we have created a simple list format. Make sure that you check the “Re-qualify list results against original segment criteria” check box to make your list format generic and it works with any segment. You can set Options such as output format, headers and footers and use the Preview tab to see how it works.
Next, we create a simple segment from our target level and associate it with the list format (List Preview File Format in the Advanced Options tab).
Click the Generate Lists button. In the popup dialog you can issue commands to run the list generation or to preview the list.
The final task is to enable end users to access this dialog. A simple solution would be to permit access to the segment designer, but let’s try a more elegant way to access the list generation from a dashboard page.
We could use the Embedded object feature but we need the URL behind the popup. So let’s launch good old firebug and inspect the popup.
In the Script tab you can expand the current URL and see that the following URL is used to retrieve the Preview popup:
It is interesting that the URL has two path parameters, one for the list format, the other one for the segment. So this allows for some automation scenarios, where one could think of variables passing the path of a segment.
Now we can embed the URL into a dashboard