I am creating a "time aware" (DAY, WEEK, MONTH, QUARTER, and YEAR) dimension using Analytic Workspace Manager.
Let me give you some background. I'm coming from a traditional "Oracle Express" OLAP background where all our data is stored in cubes and these are defined, populated and operated on using OLAP DML, there is no SQL or traditional relational tables involved.
I now want to pull data from relational tables into some OLAP cubes and am using Analytic Workspace Manager to do this (maybe this is not the best way?)
Let me explain what I'm trying to achieve. In OLAP worksheet I can type the following DML commands:
DEFINE MY_DAY DIMENSION DAY
MAINTAIN MY_DAY ADD TODAY '01JAN2011'
What this will do is create a "day dimension" and will populate it with values for each and every day between 1st Jan 2011 and today. It will be fully "time aware" and thus you can use date functions such as DAYOF to limit the MY_DAY dimension to all the Fridays etc. Similarly if I define a "month dimension" there will be an automatic implicit relationship between these two dimensions, this relationship and time aware cleverness is built into Oracle.
However, a dimension defined using DML commands (and indeed all objects created using DML language) is not visible in Analytic Workspace Manager (as there is no metadata for them?) and for the life of me I cannot work out how to create such a dimension using AWM. If I create a "Time Dimension" then, as far as I can tell, this is not a proper time dimension but merely a text dimension and I, presume, I have to teach it time awareness.
I have no issues creating, and populating cubes from relational tables using Analytic Workspace Manager, the only issue I have is creating a "proper" time aware dimension.
I am new to Oracle Workspace Manager. I have a trigger that fills my IDs every time i insert a record. I created the trigger before I enable the version of my table. After version was enabled in my table, I can no longer find my trigger but have these triggers instead:
OVM_DELETE_7 OVM_INSERT_7 OVM_UPDATE_7
What I wanted to do is Query the triggers that I created on my tables. Is there a way to do that without disabling the version on my table? I have too many version-enabled tables and that would be a hassle disabling the version in every table just for that query.
I'm currently having a problem with regards to Exporting data to another server. This is the Scenario:Source Server is Production Server with all of its Tables in the Schema are Version-Enabled.
Destination Server is a Test Server.I exported data from Production Server using EXP command. Then in my Test Server I imported my data using IMP command (I already created tablespace and user for the Schema).Import is successful in my Test server but when I execute my queries, There are no rows returned.
I checked my _LT tables and it contains my data. but when I query from the View created when version was enabled, no result is returned.Am I missing something when I exported and imported my Schema? Should I have included the WMSYS schema when I created the .dump file?
I have 2 workspaces, the one regard as Dev env, another regard as Pro. env. When the change need to be migrated, the some page of Dev workspace should be copy to Pro. one. I export the pages what I want to move from Dev., then try to import it into Pro. but I get the error as below:
Page Origin: This page was exported from a different application or from an application in different workspace. Page cannot be installed in this application. I don't want to copy the page one by one.
I Have installed the oracle database 11g, the oracle database mobile server and i use Glassfish as the application server. Everything is fine i have tested the mobile server configuration using Oracle mobile server workbench the mobile server runs but when i want to connect to the mobile manager workspace via the browser using
[URL].......
I have a server error HTTP 404- The requested resource () is not available .
My application consists of Oracle RAC and Oracle Enterprise Manage (OEM) on same two nodes. I am exploring the idea of utilizing Oracle Clusterware (which is already installed with Oracle RAC) to support OEM in active passive mode.
Here is the scenario.
Node A running Solaris 10.9 on SPARC and Node B running Solaris 10.9 on SPARC. Node A and Node B are both Oracle RAC Nodes ( Active Active ) which use Shared Storage for Oracle using ASM.
Node A and Node B will run Oracle Enterprise Manager (OEM) Application in active passive scenario which will be using Oracle RAC for its database. So, both Oracle RAC and OEM are sharing same server A and B for its clusters.
Now, OEM application need a third party clusterware to fail it over. It needs clusterware to provide floating VIP and shared folder of the size of 20GB which will keep software libraries. If node A goes down, node B have access to same libraries and will come up as active.
Do you know if oracle clusterware which comes with Oracle RAC can support OEM for failing over i.e. it can provide floating VIP and shared file system?
I have flattened customer dimension table and I would like to query it with other dimension table like address. I write a query, where I join address table twice to get permanent, secondary, and work addresses, but customer and address tables are huge that causing performance issue. Is any other ways to join flatten table with address dimensions than join it twice.
CREATE TABLE CUSTOMER ( cust_sk NUMBER NOT NULL , cust_src_id VARCHAR2(20) NOT NULL , rec_eff_dt DATE NOT NULL , last_name VARCHAR2(75) NULL , first_name VARCHAR2(30) NULL brth_dt DATE NULL ,
the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim LEVEL location IS (location.loc_id) LEVEL city IS (location.city) LEVEL state IS (location.state) HIERARCHY geog_rollup ( location CHILD OF city CHILD OF state CHILD )
Can I insert the following rows into the dimension: loc_id, city, state 1, Epping, NSW 2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
At moment we use range-hash partitioning of a large dimension table (dimension model warehouse) table with 2 levels - range partitioned on columns only available at bottom level of hierarchy - date and issue_id.
Result is a partition with null value - assume would get a null partition in large fact table if was partitioned with reference to the large dimension.Large fact table similarly partitioned date range-hash local bitmap indexes
Suggested to use would get automatic partition-wise joins if used reference partitioningWould have thought would get that with range-hash on both dimension.
The following code is a stored procedure I plan to use to populate a Data Warehouse dimension using data from two OLTP tables which already exist in my database. Notice that in my cursor select statement, I calculate an attribute using substr and instr, and I also assign a true or false value to a flag using a CASE statement.
CREATE OR REPLACE PROCEDURE populate_product_dimension AS v_Count NUMBER := 0; v_NumRecs NUMBER; /*Declare a cursor on the following query which returns mulitple rows of data from product and price_hist tables*/ [code]....
In my mind, Product_Code is declared correctly in the Cursor declaration Select statement.
SQL> ed Wrote file afiedt.buf 1 select * 2 from (select deptno, ename, sal 3 ,dense_rank() over (partition by deptno order by sal desc) rank 4 from emp)
[code]...
why is that i just added ename on the ORDER BY part of the DENSE_RANK and then SQL> ed Wrote file afiedt.buf 1 select * 2 from (select deptno, ename, sal 3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran
[code]...
ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?
I am trying to export a workspace from apex 3.2 to apex 4.2. After the workspace has been imported into apex 4.2, do I have to export and import the applications too? Don't applications reside on workspace and therefore if a workspace is imported, all the applications should have been imported by default?
How can I rewrite this without the analytic functions?
SELECT employee_ID, first_name, salary, RANK() OVER(ORDER BY salary desc) toprank_desc, RANK() OVER(ORDER BY salary ASC) toprank_asc FROM employees ORDER BY first_name
I have a table (events) with this structure: customer_id, event_id, ... For each customer_id there can be several rows in the table. I need to run a query of the format: select customer_id, expensive_function(customer_id),... from events.
The expensive_function to be applied to customer_id in the query is really expensive (a Java class calculating a check sum) and the events table has billions of rows.
Rows in events table have same customer_id for a few rows, then continue with a different customer_id ang again coming back to the first, etc.
I was thinking that it should be a way to trigger calculation of expensive_function only when customer_id changes, in order to reduce the number of calls. Only my knowledge about SQL is not going that far and I cannot use PL/SQL or any other procedural language, need to stick to standard SQL (or Oracle version of it).
I need to export my whole workspace/application from Oracle Application Express so if i was to move onto a new pc and install oracle again. I can import this file and everything will be as it was without need of editing anything.
Is this possible if so how? - Preferably export into 1 big file so importing will be with 1 mouse click instead of exporting to seperate database files etc.
I support an APEX app that has one workspace for Development and one for the live Production application. When I originally created the Dev instance I was able to copy tables and data by granting select privileges between the two workspaces (I did this one table at a time).
The development workspace data needs to be refreshed from production since it's been a long time since the initial refresh and is very out of date. I would like to do a couple things:
1) execute SQL to compare the tables in the two workspaces to ensure they're structurally identical 2) execute SQL to truncate Dev tables and refresh from Prod Is there a way to do this?
I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>... select distinct file_sid,filename,control_numb,processing_date,file_class, vendor_id,vendor_desc, c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024 THEN to_char(low_optimal_size/1024,'999999') || 'kb <= PGA < ' || (high_optimal_size+1)/1024|| 'kb' ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G). Is the critcal factor the use of distinct? or multiple windows? or something else?
I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.
There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.
Here's an example set that I would want to order by:
As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.
The result i'm looking to achieve will be ordered as follows:
As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.
With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff?
I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record
I don't want to wrap this select inside other select
I have a question regarding analytic functions. I've been working with some functions, but I can't achieve the one which gives me the pretend result. I know to resolve this without using a function, with a internal select, but I think the analytical function is faster and proper.
"representant" acct_id per group (about 300 rows total)acct_repres as( select distinct acct_id, origin_id, acct_parm_id from ( select a.* , source_id , dense_rank() over (partition by source_id origin_id order by acct_nbr nulls first, acct_id) as odr from account a join account_parm ap on (a.parm_id = ap.acct_parm_id) ) where odr = 1)select col1 , col2 , ( select accct_id from acct_repres ar where ar.acct_parm_id = t2.acct_parm_id) col3 , ( select count(1) from acct_repres) col4from some_table t1join other_table t2 on (....)
And here it comes.
The "acct_repres" subquery returns more than 300 rows when executed separately. But when used in CTE sometimes (depending on execution plan) it seems to have only one row - the value in the column col4 is "1",while value for col3 is NULL for most of the cases. It looks like the the dense_rank function and the condition "where odr =1" are evaluated at the very end.
When I use MATERIALIZE hint the result was the same. But when I put the result of account_repres into dedicated table and use that table instead of CTE the output is correct.
Is there a way to make a button conditional on the existence of another app in the same workspace. What I am trying to do is have a MAIN app and several other MODULE apps in the same workspace. Then a button on a form in the MAIN app that will redirect to a specific page in one of the MODULE apps. If the MODULE app does not exist, meaning not installed, then the button would be invisible.
Our apex currently has LDAP authentication scheme. We have many applications within the workspace. We are looking for a way to have one authentication scheme defined for all the applications so that user doesn't have to enter username/password for each application. How can I share my LDAP authentication scheme for multiple applications.
