tag:blogger.com,1999:blog-3820031471524503731.comments2022-03-30T12:21:24.920-07:00Oracle OLAPBrian Macdonaldhttp://www.blogger.com/profile/18408740222558531436noreply@blogger.comBlogger44125tag:blogger.com,1999:blog-3820031471524503731.post-15550939252670260752015-11-28T22:32:38.849-08:002015-11-28T22:32:38.849-08:00is load prune method schedule a oracle job in orac...is load prune method schedule a oracle job in oracle job queue .Rudra Ghoshhttps://www.blogger.com/profile/02391339892672156538noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-59044353522285094722014-06-26T05:43:58.236-07:002014-06-26T05:43:58.236-07:00I have a question related with reaggrigation.
My ...I have a question related with reaggrigation.<br /><br />My scenario:<br />1) I have Product Dimension (dim) and Sales Cube.<br />2) In Sales Cube, product dim is used.<br />3) In first time, i have complete refresh of dim and cube.<br />4) But if i modify data in Product dim for one of hierarchy and complete refresh of dim. So new data will available in product dim and after that i have to complete refresh of Sales cube to get reflect new dim hierarchy data.<br />5) My question is, if i have changed data in dim for one of hierarchy and complete refresh. So is there any way that i can get this data in cube without complete refresh? Is there any re-aggregation functionality in oracle OLAP cube to get data reflect in cube without complete refresh?<br /><br />Please answer soon? <br />Thanks in adv.Anonymoushttps://www.blogger.com/profile/12819489256065670407noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-89176755532840774152013-07-16T21:25:38.705-07:002013-07-16T21:25:38.705-07:00Thanks for the article. I followed the exactly sam...Thanks for the article. I followed the exactly same steps but when I execute dbms_cube.create_mview command, the error message comes up "ora-20314: Invalid materialized view name"<br />Do you have any idea?<br /><br />Thanks in advance.Anonymoushttps://www.blogger.com/profile/13476250501473092629noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-15131619229702059052013-03-09T20:15:05.790-08:002013-03-09T20:15:05.790-08:00Bud, I tried to run your script aand I get a pop u...Bud, I tried to run your script aand I get a pop up message that says blocked. Then, I'm asked to Enter Bind Variable. I am very new to Oracle Has been working on a project to create date dimension table for over 2 weeks (class project). Got this pop up before. What does it mean?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-84354446675030387622012-11-14T08:50:53.019-08:002012-11-14T08:50:53.019-08:00Posted updated (see main body) with half years. Th...Posted updated (see main body) with half years. The SQL is also a bit streamlined. Credits to Bernard for the calculation of end date and time span in the CREATE TABLE (rather than as separate updates).Bud Endresshttps://www.blogger.com/profile/02590149552658898625noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-57653233820139227902012-11-05T03:09:51.056-08:002012-11-05T03:09:51.056-08:00could you include half yearly grain as wellcould you include half yearly grain as wellAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-22354918835715583302012-11-05T03:09:16.434-08:002012-11-05T03:09:16.434-08:00hi
please could you include half yearly as wellhi<br /><br />please could you include half yearly as wellAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-77026369018443782102012-11-05T03:07:01.823-08:002012-11-05T03:07:01.823-08:00Hi
Could you add a query to include half year gra...Hi<br /><br />Could you add a query to include half year grain as well.<br /><br />Thanks<br /><br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-44084562395437530892012-11-05T03:05:34.758-08:002012-11-05T03:05:34.758-08:00Hi Bernard,
This is an excellent query. Was just...Hi Bernard,<br /><br />This is an excellent query. Was just using it but i also need to add the half yearly data in my time dimension could you help me with the same <br /><br />Regards<br /><br />PeterAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-3373242314496905172012-10-02T04:42:11.191-07:002012-10-02T04:42:11.191-07:00Great series of presentations, it's good to se...Great series of presentations, it's good to see the OLAP Option getting more exposure as the power of the system is unmatched for BI workBruce Mcaawhttps://www.blogger.com/profile/13214256723453626519noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-70963887120180260392012-08-15T06:53:06.350-07:002012-08-15T06:53:06.350-07:00LOAD PRUNE will do a select distinct on the column...LOAD PRUNE will do a select distinct on the column representing the partitioned dimension. If there are rows for the partition, the partition will be processed. If there are no rows in the fact table/view for that partition, that partition will not be processed.<br /><br />Look to see that the updated data is not filtered out in your view.Bud Endresshttps://www.blogger.com/profile/02590149552658898625noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-83297461063440480632012-08-15T06:45:45.217-07:002012-08-15T06:45:45.217-07:00LOAD PRUNE will do a select distinct on the column...LOAD PRUNE will do a select distinct on the column representing the partitioned dimension. If there are rows for the partition, the partition will be processed. If there are no rows in the fact table/view for that partition, that partition will not be processed.<br /><br />Look to see that the updated data is not filtered out in your view.Bud Endresshttps://www.blogger.com/profile/02590149552658898625noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-59130472159418501902012-08-15T01:02:37.172-07:002012-08-15T01:02:37.172-07:00Hi Bud,
I have a cube with information of 2010...Hi Bud,<br /><br /> I have a cube with information of 2010,2011 and 2012.<br /><br /> I tried to implement LOAD PRUNE option, it works for new added records but doesn't update data that already exists in the cube,<br /><br />I'm mapping a view filtered by month<br /><br />This is the script:<br /><br />BEGIN<br /> DBMS_CUBE.BUILD('FINANCIAL.GL_CUBE USING (LOAD PRUNE, SOLVE PARALLEL)','S',false,8,true,true,false);<br />END;<br /><br />Is it correct?<br /><br />Thanks in advanceEddienoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-59522371948089108472012-06-13T21:05:25.776-07:002012-06-13T21:05:25.776-07:00This comment has been removed by a blog administrator.Davehttps://www.blogger.com/profile/06233866020771278274noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-76184264034280988152011-03-10T06:11:01.410-08:002011-03-10T06:11:01.410-08:00You can do it all in a single query. I've left...You can do it all in a single query. I've left the original placeholder column in comments.<br /><br />CREATE TABLE time_calendar_dim AS<br />SELECT CurrDate AS Day_ID,<br />1 AS Day_Time_Span,<br />CurrDate AS Day_End_Date,<br />TO_CHAR(CurrDate,'Day') AS Week_Day_Full,<br />TO_CHAR(CurrDate,'DY') AS Week_Day_Short,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,<br />TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,<br />UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,<br />-- 31 AS Month_Time_Span,<br />MAX(TO_NUMBER(TO_CHAR(CurrDate, 'DD'))) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) AS Month_Time_Span,<br />--to_date('31-JAN-2010','DD-MON-YYYY') AS Month_End_Date,<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Mon')) as Month_End_Date,<br />TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,<br />RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,<br />TO_CHAR(CurrDate,'Mon') AS Month_Short,<br />TO_CHAR(CurrDate,'Month') AS Month_Long,<br />TO_NUMBER(TRIM(leading '0'FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,<br />'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,<br />-- 31 AS Quarter_Time_Span,<br />COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_Time_Span,<br />-- to_date('31-JAN-2010','DD-MON-YYYY') AS Quarter_End_Date,<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'Q')) AS Quarter_End_Date,<br />TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,<br />TO_CHAR(CurrDate,'YYYY') AS Year_ID,<br />--31 AS Year_Time_Span,<br />COUNT(*) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) AS Year_Time_Span,<br />-- to_date('31-JAN-2010','DD-MON-YYYY') AS Year_End_Date<br />MAX(CurrDate) OVER (PARTITION BY TO_CHAR(CurrDate,'YYYY')) Year_End_Date<br />FROM<br />(SELECT level n,<br />-- Calendar starts at the day after this date.<br />TO_DATE('31/12/2010','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') CurrDate<br />FROM dual<br />-- Change for the number of days to be added to the table.<br />CONNECT BY level <= 365)<br />ORDER BY CurrDate;Bernardnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-69477394639800547232010-12-23T08:16:19.548-08:002010-12-23T08:16:19.548-08:00Ahh Thanks!Ahh Thanks!Unknownhttps://www.blogger.com/profile/07414857505998927264noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-12588235461357634312010-12-23T07:23:22.201-08:002010-12-23T07:23:22.201-08:00Hi Bob,
I am told that this command is simply und...Hi Bob,<br /><br />I am told that this command is simply undocumented at this moment in time.<br /><br />Thanks<br /><br />StuartStuart Bunbyhttps://www.blogger.com/profile/10781347144821555643noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-56435716340482481872010-12-22T09:24:03.873-08:002010-12-22T09:24:03.873-08:00I went to the documentation link you shared (11gR2...I went to the documentation link you shared (11gR2 DBMS_CUBE documentation) and looked at the build command. I assume this SET ... command goes into the SCRIPT parameter. I can see no documentation about the SET command. The apparent syntax of the script parameter is:<br /><br />object [ USING ( commands ) ][,...]<br /><br />where command is one of CLEAR, LOAD, SOLVE, COMPILE, ANALYZE, EXECUTE, MODEL or AGGREGATE. Is this an undocumented command or am I missing something? <br />THANKS!!!Unknownhttps://www.blogger.com/profile/07414857505998927264noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-54606377061611854932010-07-29T14:13:36.627-07:002010-07-29T14:13:36.627-07:00BTW - there is an example of how to manually confi...BTW - there is an example of how to manually configure OBIEE 10g for 10g AW's here:- http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/olap/index.htmlStuart Bunbyhttps://www.blogger.com/profile/10781347144821555643noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-90309215710281482522010-07-29T14:10:05.616-07:002010-07-29T14:10:05.616-07:00Yes. But the plugin shown in this posting will no...Yes. But the plugin shown in this posting will not work with a 10g AW so you will need to manually configure the OBIEE metadata. If you already have an 11g database, there are lots of advantages of using 11g AW's - especially for OBIEE integrationStuart Bunbyhttps://www.blogger.com/profile/10781347144821555643noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-18079222343880345032010-07-29T07:40:21.305-07:002010-07-29T07:40:21.305-07:00is OBIEE 11G able to connect to AW 10 G stored in...is OBIEE 11G able to connect to AW 10 G stored in Database 11G ?Jean Marcnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-6916095493980906632010-06-01T01:45:33.006-07:002010-06-01T01:45:33.006-07:00About Discoverer and OLAP - I seem to remember tha...About Discoverer and OLAP - I seem to remember that in 10g opening the first OLAP report in Discoverer (Plus and Viewer) could take some time because the initial load of the first worksheet has to attach the AW and validate the AW metadata. <br /><br />I would recommend reviewing the following: <br /><br />1) checking the number of objects in your AW - the more objects you have in the AW the longer this will take to validate when the AW is attached for the first time.<br /><br />2) Make sure the selection scripts you have created for your reports are not overly complicated. I think in 10g you could improve the performance of many reports by deleting all the selection scripts, saving the report and then defining new selection scripts that return the correct list of dimension members. I think if you "prototype" your reports building up the scripts over a number of attempts then this could create very large selection scripts where many of the steps were not actually needed. Creating new reports and defining the steps in one go could help.<br /><br />In general 11g will be much faster and the issue with reading the all AW metadata during the first attach has been much improved.<br /><br />Hope this helps.ASQLBaristahttps://www.blogger.com/profile/13350994132294695189noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-37934557022016616942010-05-31T08:41:21.570-07:002010-05-31T08:41:21.570-07:00Hi Keith,
I've been working on Oracle Express...Hi Keith,<br /><br />I've been working on Oracle Express OLAP 6.3.4 for a long time and recently we started develop in 10g<br />Here began our fight with performance issues, - particularly accessing OLAP cubes using Oracle Discoverer<br /><br />It takes long time (more then 2 minutes) open OLAP workpages in Discoverer.<br />We contacted Oracle on this issue but no feasible answer so far. They directed us to next Oracle 11g version<br />where, as they said, - lots of things done for performance improvement.<br /><br />Our environment is: <br />Oracle Database 10g Enterprise Edition Release 10.2.0.3.0, <br />OracleBI Discoverer 10g (10.1.2.3), <br />Analytic Workspace Manager 10.2.0.3<br /> <br />There are many articles with Discoverer performance found on web but no solid solution so far.<br /><br />At that point I would like ask you if you have had experience with the Discoverer and if not -<br />could suggest some sources with the solutions on front-end reporting?<br />Thanks,<br />OlegAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-16905687042328197112009-07-13T11:13:45.856-07:002009-07-13T11:13:45.856-07:00Hi Richard
I believe that this will be a feature o...Hi Richard<br />I believe that this will be a feature of the upcoming OWB "Database ETL" release which will ship with Database 11gR2.<br />Until then, you are right, OWB only deployes to 10g style AWs.<br />I would therefore recommend using AWM to do your cube designe etc, and have it generate PL/SQL scripts for all the different Maintenance tasks you may want to do, and then have OWB use these in its ETL work flows. Not pretty, but it works fine (not just for OWB, but for other ETL tools too).<br />KevinKevin Lancasterhttps://www.blogger.com/profile/06742628997065141834noreply@blogger.comtag:blogger.com,1999:blog-3820031471524503731.post-49421795665677523062009-03-09T09:57:00.000-07:002009-03-09T09:57:00.000-07:00Im using the latest version of OWB 11 (11.1.0.7.0)...Im using the latest version of OWB 11 (11.1.0.7.0) against an 11g DB. <BR/><BR/>Can OWB11 target 11g OLAP yet? OWB seems to deploy dimensions and cubes to a 10g AW.<BR/><BR/>I wish to use OWB to generate the MOLAP objects and then tweak the cubes to use query rewrite.<BR/><BR/>Regards<BR/>RichardAnonymousnoreply@blogger.com