-- -- $Header: onepcube.pls 30-may-2000.16:01:58 dthompso Exp $ -- -- onepcube.pls -- -- Copyright (c) Oracle Corporation 1900, 2000. All Rights Reserved. -- -- NAME -- onepcube.pls - cwm$olap$cube -- -- DESCRIPTION -- Define logical cube metadata, grouping measures and dimensions, and -- mappings to fact tables. -- -- RETURNS -- -- NOTES -- -- -- MODIFIED (MM/DD/YY) -- dthompso 05/30/00 - Correct Exceptions raised -- dthompso 05/24/00 - map_cube new parameter override_mappings. -- dthompso 05/01/00 - Add overloaded method to set dim dep by name -- dthompso 04/27/00 - Initial Version -- dthompso 01/00/00 - Creation -- create or replace package cwm$olap$cube as -- Create a new cube, a logical grouping of dimensions and measures -- -- param owner the owner of the cube -- param cube_name the name of the cube -- param display_name the display name for the cube -- param description description text for the cube -- -- raise invalid_name if cube name is invalid -- raise no_access_privileges if user has no privileges to create the cube -- raise cube_already_exists if a cube already exists with the same name -- raise user_not_found if the owner does not exist procedure create_cube(owner varchar2 , cube_name varchar2 , display_name varchar2 , description varchar2); -- Set a new display name for the cube -- -- param owner the owner of the cube -- param cube_name the name of the cube -- param display_name the display name for the cube -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist procedure set_display_name (owner varchar2 , cube_name varchar2 , display_name varchar2); -- Set a new description for the cube -- -- param owner the owner of the cube -- param cube_name the name of the cube -- param description description text for the cube -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist procedure set_description(owner varchar2 , cube_name varchar2 , description varchar2); -- Add a dimension use into the cube -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- param dimension_owner owner of the dimension -- param dimension_name name of the dimension -- param dimension_alias alias for the dimension use -- return number id of cube dimension use -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist -- raise dimension_not_found if dimension doesn't exist -- raise alias_not_unique if alias is not unique function add_dimension(cube_owner varchar2 , cube_name varchar2 , dimension_owner varchar2 , dimension_name varchar2 , dimension_alias varchar2) return number; -- Remove a dimension use from the cube -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- param dimension_owner owner of the dimension -- param dimension_name name of the dimension -- param dimension_alias alias for the dimension use -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist -- raise dimension_not_found if dimension doesn't exist procedure remove_dimension(cube_owner varchar2 , cube_name varchar2 , dimension_owner varchar2 , dimension_name varchar2 , dimension_alias varchar2); -- Specify another dimension use that this dimension use depends on for -- solve order within the cube -- -- param owner owner of cube -- param cube_name name of cube -- param cube_dimension_use_id id of use to set dependency on -- param dependent_dimension_use_id id of dependent use -- -- raise no_access_privileges if user has no privileges to edit cube -- raise cube_not_found if cube doesn't exist -- raise use_not_in_same_cube if uses are in different cubes -- raise dimension_usage_not_found if either use id is invalid procedure set_dimension_dependency(owner varchar2 , cube_name varchar2 , cube_dimension_use_id number , dependent_dimension_use_id number); -- Specify another dimension use that this dimension use depends on for -- solve order within the cube -- -- param owner owner of cube -- param cube_name name of cube -- param dimension_owner owner of dimension to set dependency on -- param dimension_name name of dimension to set dependency on -- param dimension_alias alias of dimension to set dependency on -- param dependent_dimension_owner owner of dependent dimension -- param dependent_dimension_name name of dependent dimension -- param dependent_dimension_alias alias of dependent dimension -- -- raise no_access_privileges if user has no privileges to edit cube -- raise cube_not_found if cube doesn't exist -- raise use_not_in_same_cube if uses are in different cubes -- raise dimension_usage_not_found if either use id is invalid procedure set_dimension_dependency(owner varchar2 , cube_name varchar2 , cube_dimension_owner varchar2 , cube_dimension_name varchar2 , cube_dimension_alias varchar2 , dependent_dimension_owner varchar2 , dependent_dimension_name varchar2 , dependent_dimension_alias varchar2); -- Set the default calculation hierarchy for a dimension use within a cube -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- param dimension_owner owner of the dimension -- param dimension_name name of the dimension -- param dimension_alias alias for the dimension use -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist -- raise dimension_not_found if dimension doesn't exist -- raise hierarchy_not_found if hierarchy doesn't exist procedure set_default_calc_hierarchy(cube_owner varchar2 , cube_name varchar2 , hierarchy_name varchar2 , dimension_owner varchar2 , dimension_name varchar2 , dimension_alias varchar2); -- Delete a cube. -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist procedure drop_cube(owner varchar2 , cube_name varchar2); -- Rebuild the cube mappings after fact table has been dropped and recreated -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- -- raise no_access_privileges if user has no privileges to edit the cube -- or has no access to referenced fact table -- raise dimension_not_found if a dependent dimension does not exist -- raise cube_not_found if cube doesn't exist -- raise column_not_found if column doesn't exist in referenced fact table -- raise rebuild_failed if the rebuild could not be achieved procedure rebuild(owner varchar2 , cube_name varchar2); -- Map the dimension use to the fact table - dimension table rely constraint -- -- param cube_owner owner of the cube -- param cube_name name of the cube -- param fact_table_owner owner of the fact table -- param fact_table_name name of the fact table -- param foreign_key_name name of the rely constraint to dimension table -- param level_name lowest level of dimension -- param dimension_owner owner of the dimension -- param dimension_name name of the dimension -- param dimension_alias alias for the dimension use -- param override_mappings override existing fact mappings if conflict -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube doesn't exist -- raise table_not_found if table doesn't exist -- raise foreign_key_not_found if rely constraint does not exist -- raise dimension_not_found if dimension does not exist -- raise level_not_found if level does not exist -- raise multiple_uses_exist if alias must be specified to qualify use -- raise cannot_override_fact_mappings if different fact mappings exist -- and override_mappings false procedure map_cube(cube_owner varchar2 , cube_name varchar2 , fact_table_owner varchar2 , fact_table_name varchar2 , foreign_key_name varchar2 , level_name varchar2 , dimension_owner varchar2 , dimension_name varchar2 , dimension_alias VARCHAR2 , override_mappings BOOLEAN := false); -- Lock the cube metadata for update -- -- param owner owner of the cube -- param cube_name name of the cube -- param wait_for_lock wait for lock if acquired by other user -- -- raise no_access_privileges if user has no privileges to edit the cube -- raise cube_not_found if cube does not exist -- raise failed_to_gain_lock if lock could not be acquired procedure lock_cube(owner varchar2 , cube_name varchar2 , wait_for_lock boolean := false); -- PRIVATE function for API to get the internal id of cube -- -- param owner owner of the cube -- param cube_name name of the cube -- -- raise cube_not_found if cube doesn't exist function get_cube_id(owner varchar2 , cube_name varchar2) return number; end; /