Sitemap

Amicuk Programming Answers

How to prevent OLAP from doing unneccessary aggredations during data load?

-0001-11-30   Views:0

Advertisement

Hi, I'm trying to create a relatively simple two-dimensional OLAP cube (you might want to call it "OLAP square"). My current environment is 11.2EE with AWM for workspace management. One dimension is date, all years->year->month->day, th

Hi,
I'm trying to create a relatively simple two-dimensional OLAP cube (you might want to call it "OLAP square"). My current environment is 11.2EE with AWM for workspace management.
One dimension is date, all years->year->month->day, the other one is production unit, implemented as a hierarchy with a certain machine at the bottom level. The fact is defined by a pair of  bottom-level values of these dimensions; for instance, a measure is taken once a day from each machine. I would like to store these detailed facts in a cube together with aggregates, so they could be easily drilled down to without querying the original fact table.
The aggregation rules are set to "Aggregate from level = default" (which is day and machine respectively) for both of my dimensions, the cube is mapped to fact table with dimension tables, the data is loaded, and the whole thing is working as expected.
The problem is with the load itself, I noticed it being too slow for my amount of sample data. After some investigation of the issue I found out a query in cube_build_log table, a query the data is actually being loaded with.
<SQL>
  <![CDATA[
SELECT /*+  bypass_recursive_check  cursor_sharing_exact  no_expand  no_rewrite */
  T4_ID_DAY ALIAS_37,
  T1_ID_POT ALIAS_38,
  MAX(T7_TEMPERATURE)  ALIAS_39,
  MAX(T7_TEMPERATURE)  ALIAS_40,
  MAX(T7_METAL_HEIGHT)  ALIAS_41
FROM
  SELECT /*+  no_rewrite */
    T1."DATE_TRUNC" T7_DATE_TRUNC,
    T1."METAL_HEIGHT" T7_METAL_HEIGHT,
    T1."TEMPERATURE" T7_TEMPERATURE,
    T1."POT_GLOBAL_ID" T7_POT_GLOBAL_ID
  FROM
    POTS."POT_BATH" T1   )
  T7,
  SELECT /*+  no_rewrite */
    T1."ID_DIM" T4_ID_DIM,
    T1."ID_DAY" T4_ID_DAY
  FROM
    RI."DIM_DATES" T1   )
  T4,
  SELECT /*+  no_rewrite */
    T1."ID_DIM" T1_ID_DIM,
    T1."ID_POT" T1_ID_POT
  FROM
    RI."DIM_POTS" T1   )
  T1
WHERE
  ((T4_ID_DIM = T7_DATE_TRUNC)
    AND (T1_ID_DIM = T7_POT_GLOBAL_ID)
    AND ((T7_DATE_TRUNC)  IN  < a long long list of dates for currently processed cube partition is clipped >  ) ) ) 
GROUP BY
  (T1_ID_POT, T4_ID_DAY) 
ORDER BY
  T1_ID_POT ASC NULLS LAST ,
  T4_ID_DAY ASC NULLS LAST ]]>>
</SQL>
Notice T4_ID_DAY,  T1_ID_POT in the top level column list - these are bottom-level identifiers of my dimensions, which means the query isn't actually doing any aggregation here, as there is only one fact per each pair of (ID_DAY, ID_POT).
What I want to do is to somehow load the data without doing this (totally useless in my case) intermediate aggregation. Basically, I want it to be something like
SELECT /*+  bypass_recursive_check  cursor_sharing_exact  no_expand  no_rewrite */
  T4_ID_DAY ALIAS_37,
  T1_ID_POT ALIAS_38,
  T7_TEMPERATURE  ALIAS_39,
  T7_TEMPERATURE  ALIAS_40,
  T7_METAL_HEIGHT  ALIAS_41
FROM etc...
without any aggregations. In fact, I can live even with this loading query, as the amounts of data are not that large, but I want things to work the right way (more or less ).
Any chance to do it?
Thanks.

The replay answer
Advertisement
I defined a primary key over all the dimension keys in the fact table but for some reason the build still uses an aggregation. Probably because the aggregation operator for the cube I'm currently playing with is actually set, and I don't see a way to undefine it from the UI toolbar you are referring to. This is a piece of mapping section from the workspace file I exported using AWM.:
<CubeMap
      Name="MAP1"
      IsSolved="False"
      Query="POT_BATH_T"
      AggregationMethod="SUM">
Looks like the build aggregates because it is clearly told to so by the AggregationMethod attribute? Any way to override it?

Go to See the other 5 answers

How to prevent OLAP from doing unneccessary aggredations during data load?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I'm trying to create a relatively simple two-dimensional OLAP cube (you might want to call it "OLAP square"). My current environment is 11.2EE with AWM for workspace management. One dimension is date, all years->year->month->day, th[More]

How to prevent multiple users from updating the same data in coherence

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a Java Web Application and for data cache am using coherence 3.5. The same data maybe shared by multiple users which maybe in hundreds. Now how do I prevent multiple users from updating the same data in coherence i.e. is there something in[More]

Prevent multiple users from updating coherence cache data at the same time

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a web application which have a huge amount of data instead of storing the data in Http Session are storing it in coherence. Now multiple groups of users can use or update the same data in coherence. There are 100's of groups with several t[More]

Is there any way to prevent fields from being overwritten when importing data via xdp-file?

Category:DefaultRelease time:2015-10-11Views:130

In an pdf-form designed with LCD everytime the form gets merged with an xdp-datafile content of all fields get overwritten, regardless which data-binding (normal, global, none) is assigned to the fields and regardless if the fields are exluded in the[More]

Pre-fill the OLAP cache for a query on Data change event  of infoprovider

Category:DefaultRelease time:-0001-11-30Views:130

Hi Gurus, I have to pre-fill the OLAP cache for a query,which has bad performance. I read a doc 'Periodic Jobs and Tasks in SAP BW' which suggested sum steps to do this i hav created the setting for Bex broadcasting for scheduling job Execution with[More]

How to debug an OLAP variant which is in Infopackage Data selection Tab

Category:DefaultRelease time:-0001-11-30Views:130

Hi experts, How can I debug an OLAP variant used in the data selection tab for an infopackage? The loading is done via process chains. I am trying to debug the SAP delivered OLAP variant 0P_PPER3 of infoobject 0FISCPER3 (SAP exit). If I am not mistak[More]

Preventive maintenance order actual completion time and date

Category:DefaultRelease time:-0001-11-30Views:130

Hey gurus, In preventive maintenance order the basic and scheduled start dates are picked from the maintenance plan. Now the actual start and end dates are in display mode. My client wants an overall completion time and date of the preventive order a[More]

How to prevent Evaluate User Policies to run for Bulk loaded users?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have an OIM 11G R2 environment, where i did a bulk load of abount 200,000+ users, and all the users' accounts were created using target recon. How do I prevent the evaluate user policies scheduler from running for these users? Any ideas are wel[More]

OLAP Io exception: End of TNS data channel

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I am running Oracle 9.2.0.1 on Sun 64. I applied the 9.2.0.4.0 and 9.2.0.4.1 patches and then created some relational cubes and dimensions and associated OLAP metadata. Everything validates correctly and I can see everything in OEM and AWM. Wh[More]

How to prevent Time Evaluation to run for past date?

Category:DefaultRelease time:2015-10-11Views:130

Dear Friends, I have a problem with Time evaluation issue. While time evaluation runs with PDC recalculation date in 2010 its creating two different quota for an absence. So client donu2019t want to run time evaluation for before 31.12.2010. Please l[More]

How to prevent setBoundingThemes returning null MBR when no data are found

Category:DefaultRelease time:-0001-11-30Views:130

I am using setBoundingThemes method in MapViewer bean to zoom in to query results in a JDBCTheme. It is possible however that the JDBCTheme doesn't contain any data when no rows match the criteria. The result is that the other themes are queried with[More]

Hot
Hello All, I have a CS5 Master Collection Student Teacher Edition installed on a Mac Pro, which is licensed, registered, and works perfectly. I recently purchased a Mac Book Pro running OS 10.9.2 which I installed the entire Master Suite Collection o [More]
I get an error message when I try open "After Effects CC", so I found this page on Adobe about "Lightroom" and I need completely this page, just with "After Effects CC" Error: "Unable to start correctly (0Xc000007b)" [More]
Hi, When I try to send a test message from RWB for a Synchrnous scenario (SOAP-XI-SOAP), I am getting the following error message from the "response" mapping. Please note that I am only showing first few lines of the error log. Did any body come [More]
Hello, I installed SOA Suite without error, using the defaults of the Basic Installation. When I start the SOA Suite, it starts up without error, but reports that ASG is Status=Down When I try to create an Integration Server in JDeveloper, and test i [More]
Hi, I'd like to know if there is a limit max of records that It possible insert into a single table. I must insert into table 90000000 of records. ThanksClick here for the documentation on logical database limits.Read other 3 answers [More]
My portal version is 3.0.9 I found an mod/plsql problem .I created a procedure named current_user like this : create or replace procedure current_users AS ignore boolean; BEGIN htp.htmlopen; htp.headopen; htp.title('Current Users'); htp.headclose; ht [More]
Hi guys, I know about task adapters and approval and provisioning flows in OIM. I would now like to learn about other aspects or features of OIM like schedule tasks, entity adapters, reconciliation. Which is the best site or e-books to use for learni [More]
When i "awaken" the computer, a small "window' appears that shows the Fire fox logo and is headed "express settings'. Where the space for entering any choices are to be made, it is all black and I cannot "X" it out or close t [More]
Hi, Does anyone know where to fnd detailed documentation on Multi-providers ? If no documentation available, can anyone recommend a book that explains how multi-providers behave in different design situations ? CheersHi Achin, here is your document: [More]
Does anyone one if R-Base will run on Virtual PC? ThanksAccording to the website's minimum requirements, it should run fine, depending on what Mac you are using. My Powerbook is emulating a PIII 299MHz machine. So IMO, it will work fine. Albeit a lit [More]