Cube hierarchy and its operations

Hierarchy
The elements of a dimension can be organized as a hierarchy, a set of parent-child relationships,
typically where a parent member summarizes its children. Parent elements can further be aggregated
as the children of another parent.


A Hierarchy is a set of logically related attributes with a fixed cardinality. While browsing the data, a hierarchy exposes the top level attribute which can be broken down into lower level attributes. For example, Year -> Semester – Quarter – Month is a hierarchy. While  analyzing the data, it might be required to drill down from a higher level to a detail level, and exposing data as a hierarchy.

example - dimemployee table have data in parent-child relation "employeeID" and "ParentEmployeeId".
SSAS example -  link soon provided


Operations to facilitate analysis.
Aligning the data content with a familiar visualization enhances analyst learning and productivity.
The user-initiated process of navigating by calling for page displays interactively, through the specification of slices via rotations and drill down/up is sometimes called "slice and dice".
Common operations include slice and dice, drill down, roll up, and pivot.

OLAP slicing
Slice is the act of picking a rectangular subset of a cube by choosing a single value for one of its dimensions, creating a new cube with one fewer dimension.

The picture shows a slicing operation: The sales figures of all sales regions and all product categories of the company in the year 2004 are "sliced" out of the data cube.
Query compare : equivalent to clause "where year = 2004"


OLAP dicing
Dice: The dice operation produces a subcube by allowing the analyst to pick specific values of multiple dimensions.

The picture shows a dicing operation: The new cube shows the sales figures of a limited number of product categories, the time and region dimensions cover the same range as before.
Query compare - equivalent to "where year in (2004,2005,2006) and category in ('bikes','helmets','acc.')"


OLAP Drill-up and drill-down
Drill Down/Up allows the user to navigate among levels of data ranging from the most summarized (up) to the most detailed (down).
Drill down presenting the data at lower level on hierarchy.
Drillup presenting the data at higher level on hierarchy.

The picture shows a drill-down operation: The analyst moves from the summary category "bike" to see the sales figures for the individual products.
Drill down and up can be possible if we have meaningful hierarchical data in schema.

Roll-up: A roll-up involves summarizing the data along a dimension. The summarization rule might be computing totals along a hierarchy or applying a set of formulas such as "profit = sales - expenses".


OLAP pivoting
Pivot allows an analyst to rotate the cube in space to see its various faces. For example, cities could be arranged vertically and products horizontally while viewing data for a particular quarter. Pivoting could replace products with time periods to see data across time for a single product.

The picture shows a pivoting operation: The whole cube is rotated, giving another perspective on the data.



Source: http://en.wikipedia.org/wiki/OLAP_cube

Some topic related posts
OLAP CUBE
Storage types of cube MOLAP, ROLAP, HOLAP
Advantage and disavantage of MOLAP, ROLAP
Create your first OLAP Cube in SSAS 

SSAS CUBES HIERARCHY examples

Getting Start with SSAS sqlserver analysis services
examples of start schema
1. Create hierarchy on the basis of regular dimension on self referencing table
    Complete with DIMEMPLOYEE table
   
2. Create cube with above dimension hierarchy
    using table "DIMEMPLOYEE" , "DIMTIME" , "FactSalesQuota"

3. Create hierarchy on the basis of user-define dimension on self referencing table
    Complete with "DIMEMPLOYEE" table    
   
4. Create cube with user-define dimension hierarchy   
    using table "DIMEMPLOYEE" , "DIMTIME" , "FactSalesQuota"
   
5. Hierarchy using dimsalesterritory table
    using table dimsalesterritory / dimcurrency / factresellersales

example of snowflake schema   
6. Hierarchy using dimproduct, category and subcategory
    using table dimproduct, dimeproductcategory, dimproductsubcategory
   
7. Hierarchy using NAMED QUERY in product table.

Note:-
In HIERARCHIES pane of dimension:
    The of of hierarchy column should in high to low level like below example
    Lets a product manufacturer company, manufacturer the product and
    1. level 1 - have two type of products (a) bikes and (b) accessories
    2. level 2 - Bike can be of different types (mountain bike , dirt bike, regular bike) and
    3. level 3 is bike names

    so correct order will be products > product type > product name (product > category > subcategory )
   
In ATTRIBUTE RELATIONSHIP tab the hierarchy should be set lower to higher (just do reverse of above).

ERROR 29 (HY000): File 'D:\ est.csv' not found (Errcode: 2 - No such file or directory)

ERROR 29 (HY000): File 'D:\     est.csv' not found (Errcode: 2 - No such file or directory)
I got this error while executing below command in mysql

LOAD DATA INFILE 'D:\test.csv'
INTO TABLE emp
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n';

Problem : Path is not correctly define after INFILE
Solution : Use forward slash instead backward slash.
    LOAD DATA INFILE 'D:/test.csv'
   INTO TABLE emp
   COLUMNS TERMINATED BY ';'
   LINES TERMINATED BY '\r\n';

load data from csv file / text file or any external file

load data from csv file / text file or any external file
The LOAD DATA INFILE statement reads rows from a text file into a table.
LOAD DATA INFILE after using this command i am able to load all data/rows into a table, which is quite fast.

CREATE TABLE `emp` (
  `empno` decimal(4,0) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` decimal(4,0) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` decimal(2,0) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---DAta in my csv file / text file
3369;SMITH;CLERK;7902;1980-12-17;800
4399;ALLEN;SALESMAN;7698;1981-02-20;1600
5321;WARD;SALESMAN;7698;1981-02-22;1250


TYPE - 1 user variable can be used
LOAD DATA INFILE 'D:/test.csv'
INTO TABLE emp
 COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`)
set `comm` = 232 ,`deptno` = 20
Query OK, 3 rows affected (0.11 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


TYPE - 2
---DAta in my csv file / text file
3369;SMITH;CLERK;7902;1980-12-17;800;232;20
4399;ALLEN;SALESMAN;7698;1981-02-20;1600;232;20
5321;WARD;SALESMAN;7698;1981-02-22;1250;232;20

LOAD DATA INFILE 'D:/test.csv'
INTO TABLE emp
 COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.11 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

TYPE - 3 We can also use "IGNORE" in command it will ignore number of lines, after using below in command it will ignore firest line and will start insert from second.
In this case external file is same as above.
LOAD DATA INFILE 'D:/test.csv'
INTO TABLE emp
 COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 lines;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

more info at :
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

remove ^M from file

Sometimes file automatic get post-fix by ^M in every line, and it can leads to hassle to hard to handle the files, which are related to code and can cause unexpected error in
code.

How to remove ^M from whole file.

Open terminal and locate the file

vim /root/Desktop/test.txt
(press escape)
(press :)
full command is :%s/ctrl+v+m//g

(press CTRL, V, M all together)



:wq

(save and exit)

and then enter. thats it.

RMAN backup error, RMAN-03009 ORA-19809 ORA-19804

What i have followed:--------
cmd > RMAN
RMAN> connect target /
RMAN> backup database;

Starting backup at 14-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=E:\APP\GURJEET\ORADATA\PROD\USERS01.DBF
input datafile file number=00007 name=E:\APP\GURJEET\ORADATA\PROD\******.DBF
input datafile file number=00003 name=E:\APP\GURJEET\ORADATA\PROD\UNDOTBS01.DBF
input datafile file number=00001 name=E:\APP\GURJEET\ORADATA\PROD\SYSTEM01.DBF
input datafile file number=00002 name=E:\APP\GURJEET\ORADATA\PROD\SYSAUX01.DBF
input datafile file number=00005 name=E:\APP\GURJEET\ORADATA\PROD\EXAMPLE01.DBF
input datafile file number=00006 name=E:\APP\GURJEET\PRODUCT\11.2.0\DBHOME_2\DATABASE\*****.DBF
input datafile file number=00013 name=E:\APP\GURJEET\PRODUCT\11.2.0\DBHOME_2\DATABASE\*****.DBF
channel ORA_DISK_1: starting piece 1 at 14-APR-14
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/14/2014 13:20:42
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 4039114752 limit
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-APR-14
channel ORA_DISK_1: finished piece 1 at 14-APR-14
piece handle=E:\APP\GURJEET\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2014_04_14\O1_MF_NCSNF_TAG20140414T131547_9NQ4TMXX_.BKP tag=TAG20140414T131547 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/14/2014 13:20:42
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim bytes disk space from limit


Error     :
RMAN-03009: failure of backup command on ORA_DISK_1 channel at
ORA-19809: limit exceeded for recovery files                  
ORA-19804: cannot reclaim  bytes disk space from limit        
Problem   :
FRA (Flash Recovery Area) not have enough space to store the backup file.

Solution  :
1. We need to increase the size of FRA (Flash Recovery Area).
2. You can offer delete command. It will delete old backup files (RMAN RETENTION POLICY).


Explantion solution wise:
FOR SOLUTION 1. We need to increase the size of FRA (Flash Recovery Area).

• RMAN backup file physically store at FRA (Flash Recovery Area). While installation we assign space from total physical hard drive to FRA.

• We can also increase and decrease the allocation space to FRA using "alter system" command.

• After firing command "RMAN> backup database;" i need to wait for some time because the file is being written at location    "E:\app\gurjeet\flash_recovery_area\prod\BACKUPSET\2014_04_15"

         After some time oooopss error

• How to get estimated size increase for FRA.?. You can get a estimate size for your FRA using below table from column output_bytes/input_bytes.

    select * from V$RMAN_BACKUP_JOB_DETAILS order by 1;

• After error i checked the trace file which is showing error to the point:

    Errors in file e:\app\gurjeet\diag\rdbms\prod\prod\trace\prod_ora_848.trc:
    ORA-19815: WARNING: db_recovery_file_dest_size of 10156459008 bytes is 99.89% used, and has 11091968 remaining bytes available.
    ************************************************************************
    You have following choices to free up space from recovery area:
    1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
       then consider changing RMAN ARCHIVELOG DELETION POLICY.
    2. Back up files to tertiary device such as tape using RMAN
       BACKUP RECOVERY AREA command.
    3. Add disk space and increase db_recovery_file_dest_size parameter to
       reflect the new space.
    4. Delete unnecessary files using RMAN DELETE command. If an operating
       system command was used to delete files, then use RMAN CROSSCHECK and
       DELETE EXPIRED commands.
    ************************************************************************
    Deleted Oracle managed file E:\APP\GURJEET\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2014_04_15\O1_MF_NNNDF_TAG20140415T124319_9NSQ0JDF_.BKP

• You can use below command to check used/free space for flash recovery area:

    select name,
    floor(space_limit/1024/1024) "Size_MB",
    ceil(space_used/1024/1024) "Used_MB"
    from v$recovery_file_dest
    order by name
    /

• Offer below command in sql promt to increase the size of FRA. It will resolve the problem.

    ALTER SYSTEM SET db_recovery_file_dest_size=12G SCOPE=BOTH ;



SOLUTION 2. You can offer delete command. It will delete old backup files (RMAN RETENTION POLICY).
we can delete old backup file to free up the space or
can set lesser number of days to retain the archivelogs and backup files
RMAN> delete obsolete;

Increase the Size of flash recovery area

Flash Recovery Area feature lets you set up a location on disk where the database can create and manage a variety of backup and recovery files on your behalf.
Basically this is the setting of RMAN.
Files that could be found in the FRA include:

•     RMAN backups
•     control file
•     logs
• online redo log copies
• archived redo logs
•  flashback logs

     
Check the size:
select name,
floor(space_limit/1024/1024) "Size_MB",
ceil(space_used/1024/1024) "Used_MB"
from v$recovery_file_dest
order by name
/

Memory Usage of FRA:
Query the V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;


To change the size of the recovery area is given command.
ALTER SYSTEM SET db_recovery_file_dest_size=2G SCOPE=BOTH ;


More about FRA  http://docs.oracle.com/cd/B19306_01/backup.102/b14192/setup005.htm

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

I am trying to execute the command "backup database" in RMAN prompt, and it fails to execute and display below error.

Error:

RMAN-03009: failure of backup command on ORA_DISK_1 channel at   
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode


Cause: Your database is running into NOARCHIVELOG mode

Solution: Enable the ARCHIVELOG mode for your database and try agian.


How to enable archivelog mode:

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

execute below query one by one to enable archivelog mode.
SQL> shu immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
 
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

ArchiveLog & enable archivelog mode

ArchiveLog
When you enable this mode redo logs will be archived instead of overwritten.
Archive logs are used by RMAN, Data Guard, Flashback and others.

enable archivelog mode

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

execute below query one by one to enable archivelog mode.
SQL> shu immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
 
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

ORA-12704: character set mismatch

I have replicate the above error using below query :

Select case when job = 'CLERK' then 'C' else replace(upper(ename),' ' ,'') end || '_'||job  as job_type from emp;

Above query is working fine with "emp" table, if column ENAME , JOB having "VARCHAR" datatype.
It will throw error if changed it to NVARCHAR.

For this we need to modified the "emp" table:
alter table emp modify ename nvarchar2(10);
alter table emp modify job nvarchar2(10);


Select case when job = 'CLERK' then 'C' else replace(upper(ename),' ' ,'') end || '_'||job  as job_type from emp;
ORA-12704: character set mismatch

Solution: USE N FUNCTION (the N function converts the data to nvarchar at compilation time)
Select case when job = 'CLERK' then N'C' else replace(upper(ename),' ' ,'') end || '_'||job  as job_type from emp;


Casue:
source(ora-12704.ora-code.com)
Cause:     One of the following:

- The string operands(other than an nlsparams argument) to an operator or built-in function do not have the same character set.
- An nlsparams operand is not in the database character set.
- String data with character set other than the database character set is passed to a built-in function not expecting it.
- The second argument to CHR() or CSCONVERT() is not CHAR_CS or NCHAR_CS.
- A string expression in the VALUES clause of an INSERT statement, or the SET clause of an UPDATE statement, does not have the same character set as the column into which the value would be inserted.
- A value provided in a DEFAULT clause when creating a table does not have the same character set as declared for the column.
- An argument to a PL/SQL function does not conform to the character set requirements of the corresponding parameter.