Wednesday, August 31, 2005

Some Discoverer OLAP - D4O - queries answered

This is in response to a recent comment left behind by Anand in response to my post - "OLAP 10g R2 web seminar"

The answers here have been provided by Keith Laker (you may already know him from the BI Beans OTN Forum)

1. Does D4OLAP supports the attribute reporting?
OLAP currently supports dimensional selections based on attribute members. If you have an attribute called "Product Color" you can select all products where color is red. If you want to include the values of Product Color in your report you need to either add Product color as a dimension or as a measure (which would allow you to include product color in the databody of the report)

2. How do we do the Dimension based measures calculations (E.g.. ratio of Sales Value by Discount Value (where discount and sales are transaction type dimension members across value measure)) ?
The calculation templates provided within Discoverer, Excel, BI Beans are designed to provide the most commonly used calculations in an easy to use wizard. However, the OLAP engine supports many more functions, such as forecasting. Customers can also create their own functions to generate specific types of calculations using the extensive library of OLAP DML commands and associated functions. The easiest way to create this specific type of calculation is to use Analytic Workspace Manager and create the calculation definition directly within the AW.

3. How do we use the Parameters in OLAP reports?
OLAP Parameters are not supported within Discoverer in the current release. However, if you do need to create a specific report that does require parameter support you can access an Analytic Workspace using SQL via the OLAP_TABLE function. This would allow you to create relational queries against AW cubes. However, you would lose the multi-dimensional query model, therefore, I would only recommend this approach for use with specific types to reports that do not require detailed analysis by end-users.

4. Need to review the YTD function to check if the calculation is proper.
Not sure what the question is? If the customer wants to see the SQL generated by OLAPI the answer is we do not expose our SQL statements. If you want to ensure the data is correct, and that the calculation is actually performing the correct aggregations, then you could use AWM and compare the results from an OLAP DML report to the values in your report. However, I can assure you the calculation syntax for YTD is correct - assuming you have the correct time based metadata available. This is covered in the OLAP database documentation.

My note: Users are more likely to get answers by posting such queries to the appropriate OTN Forum. So do feel free to leave feedback, but for detailed responses like this, your best bet should still be the OTN Forums: