A question that I often get is "what is the difference between using ODBC and the MDX Provider for Oracle OLAP (from Simba Technologies) to query Oracle cubes"? Given that the Oracle cube is easily queried with SQL, it's a reasonable question.
The answer really boils down to leveraging meta data and automatic query generation.
With ODBC, it's up to the Excel user to write a SQL query to fetch data from the cube. Data can be returned in tabular format or a pivot table. When the data is viewed in a pivot table Excel will aggregate data, sometimes with unexpected results. For example Excel might choose to aggregate a measure such as Sales with COUNT or might try to SUM a measure such as Sales YTD Percent Change. Neither make any sense. It's up to the user to get it right.
With the MDX Provider, Excel understands what all the columns mean. It understands dimensions, hierarchies and levels. It's understand the difference between a key and a label. It knows what a measure is. It allows the server to calculate the data. Query generation is automatic. Business users just choose hierarchies and measures and the MDX Provider does the rest.
Here's a list of some of the advantages of using the MDX Provider for Oracle OLAP as compared to using ODBC and writing your own SQL.
Tuesday, April 3, 2012
Monday, April 2, 2012
Oracle OLAP Exadata Performance Demonstration
For a great paper on Oracle OLAP running on Exadata, see:
http://www.oracle.com/technetwork/database/options/olap/olap-exadata-x2-2-performance-1429042.pdf
The Executive Overview section of this paper provides an introduction:
This paper describes a performance demonstration of the OLAP Option to the Oracle Database running on an X2-2 Exadata Database Machine half rack. It shows how Oracle OLAP cubes can be used to enhance the performance and analytic content of the data warehouse and business intelligence solutions, supporting a demanding user community with ultrafast query and rich analytic content.
The demonstration represents users of a business intelligence application using SQL to query an Oracle OLAP cube that has been enhanced with a variety of analytic measures. The cube contains data loaded from a fact table with more than 1 billion rows.
Utilizing Exadata features such as Smart Flash Cache, Oracle Database supported a community of 50 concurrent users querying the cube with queries that are typical of those executed from a business intelligence tool such as Oracle Business Intelligence Enterprise Edition.
With each user querying the database non-stop (without waits between queries) with median query times ranged from .03 to .58 seconds, average query times ranged from .26 to 2.32 seconds, and 95 percent of queries returned in 1.5 to 5.5 seconds, depending on the type of query.
Query performance can be attributed to highly optimized data types and Exadata Smart Flash Cache. Cubes are designed for fast access to random data points, using features such as array-based storage, cost-based aggregation, and joined cube scans. Exadata Smart Flash Cache contributes significantly to cube query performance, virtually eliminating IO wait for the high volume, random IO typically seen with cube queries.
http://www.oracle.com/technetwork/database/options/olap/olap-exadata-x2-2-performance-1429042.pdf
The Executive Overview section of this paper provides an introduction:
This paper describes a performance demonstration of the OLAP Option to the Oracle Database running on an X2-2 Exadata Database Machine half rack. It shows how Oracle OLAP cubes can be used to enhance the performance and analytic content of the data warehouse and business intelligence solutions, supporting a demanding user community with ultrafast query and rich analytic content.
The demonstration represents users of a business intelligence application using SQL to query an Oracle OLAP cube that has been enhanced with a variety of analytic measures. The cube contains data loaded from a fact table with more than 1 billion rows.
Utilizing Exadata features such as Smart Flash Cache, Oracle Database supported a community of 50 concurrent users querying the cube with queries that are typical of those executed from a business intelligence tool such as Oracle Business Intelligence Enterprise Edition.
With each user querying the database non-stop (without waits between queries) with median query times ranged from .03 to .58 seconds, average query times ranged from .26 to 2.32 seconds, and 95 percent of queries returned in 1.5 to 5.5 seconds, depending on the type of query.
Query performance can be attributed to highly optimized data types and Exadata Smart Flash Cache. Cubes are designed for fast access to random data points, using features such as array-based storage, cost-based aggregation, and joined cube scans. Exadata Smart Flash Cache contributes significantly to cube query performance, virtually eliminating IO wait for the high volume, random IO typically seen with cube queries.
Monday, January 17, 2011
Script for Time Dimension Table
Note - This blog post was updated on Nov. 14, 2012 with a new script. This as been simplified a bit and includes half year.
One of the more common requests I get is a script for creating time dimension tables for Oracle OLAP. The following script will create a time dimension table for a standard calendar. It starts by creating a table with dimension members and labels. The second part of the script fills in end date and time span attributes. The section that creates end date and time span can be easily adapted for completing other calendars (e.g., fiscal) where the members have already been filled in.
One of the more common requests I get is a script for creating time dimension tables for Oracle OLAP. The following script will create a time dimension table for a standard calendar. It starts by creating a table with dimension members and labels. The second part of the script fills in end date and time span attributes. The section that creates end date and time span can be easily adapted for completing other calendars (e.g., fiscal) where the members have already been filled in.
--
-- Create time dimension table for a standard calendar year (day, month,
-- quarter, half year and year).
--
-- Drop table.
--
--DROP TABLE time_calendar_dim;
--
-- Create time dimension table for calendar year.
--
-- First day if the next day after TO_DATE('31/12/2010','DD/MM/YYYY').
-- Number of days is set in CONNECT BY level <= 365.
--
-- Values for end date and time span attributes are place holders. They need
-- to be filled in correctly later in this script.
--
CREATE TABLE time_calendar_dim AS
WITH base_calendar AS
(SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon')
|| '-'
|| TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
TO_CHAR(CurrDate,'Mon')
|| ' '
|| TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month'))
|| ' '
|| TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q'
|| UPPER(TO_CHAR(CurrDate,'Q')
|| '-'
|| TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 1
ELSE 2
END AS half_num_of_year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 'H'
|| 1
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
ELSE 'H'
|| 2
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
END AS half_of_year_id,
TO_CHAR(CurrDate,'YYYY') AS Year_ID
FROM
(SELECT level n,
-- Calendar starts at the day after this date.
TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
FROM dual
-- Change for the number of days to be added to the table.
CONNECT BY level <= 365
)
)
SELECT day_id,
day_time_span,
day_end_date,
week_day_full,
week_day_short,
day_num_of_week,
day_num_of_month,
day_num_of_year,
month_id,
COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,
MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
month_short_desc,
month_long_desc,
month_short,
month_long,
month_num_of_year,
quarter_id,
COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,
MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
quarter_num_of_year,
half_num_of_year,
half_of_year_id,
COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,
MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
year_id,
COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,
MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id;
);
--
COMMIT;
-- Create time dimension table for a standard calendar year (day, month,
-- quarter, half year and year).
--
-- Drop table.
--
--DROP TABLE time_calendar_dim;
--
-- Create time dimension table for calendar year.
--
-- First day if the next day after TO_DATE('31/12/2010','DD/MM/YYYY').
-- Number of days is set in CONNECT BY level <= 365.
--
-- Values for end date and time span attributes are place holders. They need
-- to be filled in correctly later in this script.
--
CREATE TABLE time_calendar_dim AS
WITH base_calendar AS
(SELECT CurrDate AS Day_ID,
1 AS Day_Time_Span,
CurrDate AS Day_End_Date,
TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
TO_CHAR(CurrDate,'DY') AS Week_Day_Short,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,
UPPER(TO_CHAR(CurrDate,'Mon')
|| '-'
|| TO_CHAR(CurrDate,'YYYY')) AS Month_ID,
TO_CHAR(CurrDate,'Mon')
|| ' '
|| TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,
RTRIM(TO_CHAR(CurrDate,'Month'))
|| ' '
|| TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,
TO_CHAR(CurrDate,'Mon') AS Month_Short,
TO_CHAR(CurrDate,'Month') AS Month_Long,
TO_NUMBER(TRIM(leading '0'
FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,
'Q'
|| UPPER(TO_CHAR(CurrDate,'Q')
|| '-'
|| TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 1
ELSE 2
END AS half_num_of_year,
CASE
WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
THEN 'H'
|| 1
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
ELSE 'H'
|| 2
|| '-'
|| TO_CHAR(CurrDate,'YYYY')
END AS half_of_year_id,
TO_CHAR(CurrDate,'YYYY') AS Year_ID
FROM
(SELECT level n,
-- Calendar starts at the day after this date.
TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
FROM dual
-- Change for the number of days to be added to the table.
CONNECT BY level <= 365
)
)
SELECT day_id,
day_time_span,
day_end_date,
week_day_full,
week_day_short,
day_num_of_week,
day_num_of_month,
day_num_of_year,
month_id,
COUNT(*) OVER (PARTITION BY month_id) AS Month_Time_Span,
MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
month_short_desc,
month_long_desc,
month_short,
month_long,
month_num_of_year,
quarter_id,
COUNT(*) OVER (PARTITION BY quarter_id) AS Quarter_Time_Span,
MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
quarter_num_of_year,
half_num_of_year,
half_of_year_id,
COUNT(*) OVER (PARTITION BY half_of_year_id) AS Half_Year_Time_Span,
MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
year_id,
COUNT(*) OVER (PARTITION BY year_id) AS Year_Time_Span,
MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id;
);
--
COMMIT;
Thursday, December 2, 2010
Simba previews Cognos8 Analysis Studio accessing Oracle Database OLAP Option cubes
Hot on the heels of support for BusinessObjects Voyager, and in addition to the native Excel 2003/2007/2010 pivot table access, Simba are previewing the same connectivity for Cognos8 Analysis Studio - the dimensionally aware UI in the Cognos BI suite.
Together with the unique SQL access to the same multidimensional data & calculations in Oracle Database OLAP cubes (meaning that *any* tool or application capable of connecting to Oracle and issuing simple SQL can leverage the power of Database OLAP - like Oracle Application Express for example), plus the existing support for Oracle's own BI tools including
- Oracle BIEE 10g and 11g (see http://oracleolap.blogspot.com/2010/07/first-look-at-obiee-11g-with-oracle.html ) and
- Oracle BI Discoverer Plus OLAP (see http://oracleolap.blogspot.com/2010/08/discoverer-olap-is-certified-with-olap.html ),
together with the big functionality and performance improvements in 11g , there is now every reason to move to Oracle Database 11gR2 and to fully exploit the OLAP Option - whatever your choice of front end tool(s).
For Cognos fans: Here is the Video on YouTube:
More information, see the Simba website : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm
Labels:
APEX (Application Express),
BI Tools,
Cognos,
connectivity,
Discoverer for OLAP,
Excel,
MDX,
Microsoft Office,
OBIEE,
OLAP,
OLAP 11g,
Simba,
SQL Access
Tuesday, November 16, 2010
Simba previews Oracle OLAP MDX Provider connectivity to SAP BusinessObjects Voyager
Simba technologies have released a short video to preview 'Using MDX Provider for Oracle OLAP to directly connect SAP BusinessObjects Voyager to Oracle Database OLAP Option'
This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.
You can get more details on the Simba website
This will be a great capability for users of both Oracle OLAP and BusinessObjects and will futher extend the reach of Oracle database embedded OLAP cubes.
You can get more details on the Simba website
Thursday, November 4, 2010
Microsoft Certifies Simba’s MDX Provider for Oracle OLAP as “Compatible with Windows 7”
Simba announced today that Microsoft has certified its MDX Provider for Oracle Database OLAP Option for Windows 7.
The press release is here : http://www.simba.com/news/MDX-Provider-for-Oracle-OLAP-Certified-for-Windows-7.htm
This is great news for MS Office connectivity to your Database OLAP cubes. Already supported was Excel 2010 with its cool new BI features, as well as 2007 and 2003 versions. With Windows 7 support too, even the most up-to-date PCs are in good shape to exploit the OLAP Option.
Remember - via the native Excel pivot table connectivity, other features of MS-Office can also leverage the power, performance and calculation functionality of Oracle OLAP. So for example, your Powerpoint presentation to the senior management team, or the one you use when talking to suppliers or key customers can now contain live charts onto the latest information available in the Oracle Database cubes. Instead of spending time cutting and pasting static images into your slide shows each day, week or period-end you can be focusing on the news in the data and doing a better analysis of the results.
This is the perfect complement to your use of Oracle BI Enterprise Edition (10g or 11g) onto the exact same cubes for the broader BI use-case.
In addition to Microsoft Excel, Simba is gradually certifying other MDX front ends for the OLAP Option MDX Provider - with SAP-BusinessObjects Voyager already available and others due soon.
You can learn all about it on the Simba web site : http://www.simba.com/MDX-Provider-for-Oracle-OLAP.htm
Connectivity into Oracle Database OLAP 11g is market leading: with the same cubes and calculations being accessible via MDX as well as a huge range of SQL based tools and applications, it has never been easier to deploy multidimensional analytics to the masses.
Labels:
Business Objects,
connectivity,
Excel,
MDX,
Microsoft Office,
OLAP 11g,
SAP,
Voyager
Friday, October 22, 2010
Cell level write-back via PL/SQL
A topic of conversation that regularly comes up when I talk to customers and developers about the OLAP Option is write-back to OLAP cubes. The most frustrating of these conversations usually involves someone saying 'but... the OLAP Option doesn't support write-back'. This is not the case and never has been.
Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.
At the most simple level, it is possible to write to a qualified cell:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')
In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.
This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')
You can also skip the aggregation:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')
or run multiple cell updates in one call:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,
SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,
SOLVE)');
You can also copy from one measure to another.
dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));
This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.
For more details, take a look at the PL/SQL reference documentation
Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.
At the most simple level, it is possible to write to a qualified cell:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')
In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.
This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')
You can also skip the aggregation:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')
or run multiple cell updates in one call:
dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,
SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,
SOLVE)');
You can also copy from one measure to another.
dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));
This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.
For more details, take a look at the PL/SQL reference documentation
Labels:
OLAP 11g,
OLAP API,
OLAP DML,
Oracle OLAP Option,
Write-back
Subscribe to:
Posts (Atom)
