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
I am getting the XULRunner error. I did a "clean install" of the latest version and am still getting the following error: >XULRunner >Error: Platform version '11.0' is not compatible with >minVersion >= 12.0 >maxVersion <= 12.0 [More]
Hi SD experts, Plz help me solving following issues 1) What do you mean by POD(proof of delivery) what is the use of pod? What are the con figuration you done in your project? 2) When I am punching order after entering data like material No and quant [More]
About two months back was the last time I remember my itunes working correctly. It was around the same time I downloaded Limewire. Whenever I tried to open it it would just show the user agreement page with "I agree" or "I decline" ans [More]
Hi everyone, using TCS2 on Win-7 64-bit. We have been using some conditional text tags in our writing, and now we are looking at adding a few new ones and taking a few away. Is there a way to remove a batch of conditional text tags from a whole bunch [More]
Hi Experts, I have a situation where i provide a list of values to the user using a format search to get the required serial number. the format search appears in the following manner Find |_<text to be found>___| <Search Results from the query> [More]
Hi All, I would like to change my JAVA Source in the DATABASE and compile it again. At the moment I can see only the code but have no actions to compile and generate it to classes. Is this planned for a future release? AS a LAYOUT idea ... if classes [More]
i want get period name on query to use it in report the query get period name on query to use it in report SELECT itm.item_number item_no, itm.description item_desc1, mtl.organization_code, cpt.cost_cmpntcls_code, cpt.cost_cmpntcls_desc, adj.cost_ana [More]
hi, I am using a mapping to connect a key to an array[]. When I want to get the array back how do I have an array. putting the key with the array ht.put(key, array); getting the array back Card newArray = new Card[n]; (where n is the number of elemen [More]
I am getting error FRM-40039 can not attach webutl while opening...... The path is set as per documentation. webutil is regenerated. frmwebutil.jar and jacob jar are both self-signed. compiled perfect. version I am running is 10.1.2.0.2. Please assis [More]
Hi again, i have a Select-Options. Per Default 4 buttons(cancel,Check,Reset,Copy) are also generated with this select-options. I dont want this buttons,only the ranges-table. How can i hide this buttons? ThanksHi Marco. There should be a method calle [More]