Fact table mapping/loading

Advertisement
I am new to OWB and trying to use OWB 9.0.4 to create a star schema on a oracle 9.2 DB. Using OWB client, I successfuly created dims and a fact table on the target DB. But I got stuck with how to use OWB to map the fact table with dims and source tables, and using OWB to generate SQL loading scripts which contains PL/SQL statement shown below:
insert into "fact_table" ("fact_count") ( select count(*) from target_table where dim1='dime_value1' and dim2='dim2_value2');
here dim1 and dim2 are the dims assocated with the fact table and they are also mapped to the corresponding target table columns.
Advertisement

Replay

Frank,
source table -> aggregator -> target
As input into the aggregator, you specify dim1, dim2 and any numeric value. If you do not have one, use a numeric constant such as 1. In the aggregator, you specify dim1, dim2 as the group by clause (operator properties). You then add a numeric output attribute (edit function), and go to the attribute properties. As a function, specify count from the numeric input attribute.
That would load your measure.
Cube function is not currently supported in the mapping editor, but you could include that in a materialized view that is based on the table.
Mark.

Related Topics

  • Load fact table with null dimension keys

    Dear All, We have OWB 10g R2 and ROLAP star schema. In our source system some rows don't have all attributes populated with values (null value), and this empty attributes are dimension (business) keys in star schema. Is it possible to load fact table

  • How map to my particular table to the fact table in obiee 11g...

    Hi friends, I did this simple report in obiee 11g(i.e) "NATIONALITY COUNT IN DEPARTMENT WISE" For that i used the following tables: per_all_assignments_f----->fact table hr_all_organization_units----->dim table(containing departments) per_

  • Interesting facts about Nokia Map Loader 1.3 and 1...

    Hi there, I have done some interesting tests yesterday after upgrading the Map Loader to version 1.3. First, I tried to connect my N95-1 (v.20.0.015) with a microSD card (4 GB) to my PC with Nokia Map Loader 1.3 running. The software asks to update t

  • How to refer to fact table to update the same fact table

    Here is my scenario. Fact table is having measures basic_sal, Tax, net_sal with dimension key to employee dimension. I neet to update the net_sal = basic_sal - tax in fact table. I tried to do a map with fact_table as source and also target. but afte

  • Large Fact Tables

    I have some transactional tables that are quite large. Anywhere from 1 to 3 million records. I have been doing my best to find information on the best way to deal with these and I am having some trouble. I am a little tight for time and could use a l

  • Key figure units in fact tables of InfoCube are blank

    I run RSRV - Key figure units in fact tables for my cube and found the following error message. I found one SAP note 941895 - RSRV test fails with unknown error, but our system status is SAPKW70020, so this note is not useful to our case. Pls advise!

  • No Fact table exists at this detail level error

    Hi All, I am trying to develop an RPD and was trying to use a simple example of joining two fact tables in OBIEE through confirmed dimension. This is my example: Fact A: Student ID (FK)......Measure1, Measure2... Fact B: Student ID (FK)........Measur

  • Partitioning a fact table

    I am curious to hear techniques for partitioning a fact table with OWB. I know HOW to setup the partitioning for the table, but what I am curious about is what type of partitioning everyone is suggesting. Take the following example...Lets say we have

  • Problems with 2 fact tables

    Hi. I use cmw2_ procedures to create dimensions and cubes. My cube has three dimensions. I have 2 fact views - one includes 3 dimensions, another - 2 dimensions. I tried to map measures to these fact tables - error: exact fetch return more than reque

  • Performance - DIM and FACT tables

    Experts, I have performance issues where my DIM tables are bigger than the FACT tables by 50 %. Can any one please let me know what should be done in order to solve this issue I have already done below steps 1)Kept the small dimensions together 2)kep

  • Sizeof fact table and dimension table

    Hi Experts , Can you tell me how to measure the size of dimension table and fact tables and how to find the ratio of size of dimension table to the size of fact table. Thanks in advance, Thanks and Regards Ram KommineniHi, The ratio of size of fact t

  • Way to handle fact table

    hi, let;s say that we have a fact table with 250 millions rows of data. 9 dimensions. My question is ,even we make aggregates to all possible levels,the queries in low levels are really slow(30-60 seconds). We use indexes in the fact table in columns

  • Datamart as set of fact table and dim

    i have to develop the etl procedures for loading the data from flat file to datamart which consist of several dimensions and fact tables. please tell me how this technology work or what r the steps to proceede further, one imp question that my user a

  • Modelling 2 Fact Tables with Non-Conforming Dimension in OBIEE 11g

    Hi all, I have two fact tables (Fact 1 and Fact 2) and two dimension tables (Product and Rule). The Product dimension table is a conforming dimension and is used in both fact tables, but the Rule dimension is a non-conforming dimension which is used

  • NEW GL's Changed Tables  mapping

    Hi,                              I Need to know more about new GL's Changed Tables  mapping . Warm Regards, Sachin DarekarDear Sridher, Please cehck the SAP Note 812919 - My SAP ERP New G/L Migration. other wise you can do other way. but it's as on 0

  • How to empty Fact tables ???

    Hi everybody, How to supress data in fact table(but without delete data in dimension tables) before data loading ? I use a process chain to load the data. I know that manualy it is easy to do that, but with an automatic process how to do that ? Thank

  • Problem using same dimension in 2 Fact tables

    Hi, I am facing this strange problem with the out-of-box BMM metadata mapping : The "Dim_W_GL_ACCOUNT_D" is joined to "Fact_W_GL_OTHER_F" and "Fact_W_GL_BALANCE_F" facts. Both these fact tables have a inner join to "Dim_

  • Connection between two fact tables.

    I have a problem in OBIEE 10.1.3.2: I have two fact tables A and B. Both tables have three columns - time_id, position_id and valuea (table A), time_id, position_id and valueb (table B). I have dimensions TIME (id, time) and POSITION (id, post). Fact

  • Multiple fact tables, aggregation and model problems

    Hi, I am developing a OLAP Application with 2 dimensions (product,location) and a few measures (sales_qty, sales_value, sale_price, cost_price, promotion_price, average_market_price, etc). I also have a BiBean Crosstab to explore the data. I'm having