Good day everyone,
I have pretty long question to ask, if possible please help.
I have cube which contains a lot of information (40 mlns. rows).
Cube contains data as
Dimensions Key Figures Claim ID Activity ID Remit. Flag Subm. Date Remit. Date Gross Claimed 100001 9999901 01.01.2010 500 400 100001 9999902 01.01.2010 120 100 100001 9999903 01.01.2010 300 250 100001 9999904 01.01.2010 0 0 100001 9999905 01.01.2010 100 80 100002 9999906 10.05.2010 200 150 100002 9999907 10.05.2010 300 250 100002 9999908 10.05.2010 510 350 100002 9999909 10.05.2010 125 100Data constantly changes - claims have being remitted (reviewed) and after delta becomes like:
Dimensions Key Figures Claim ID Activity ID Remit. Flag Subm. Date Remit. Date Gross Claimed 100001 9999901 01.01.2010 500 400 100001 9999902 01.01.2010 120 100 100001 9999903 01.01.2010 300 250 100001 9999904 01.01.2010 0 0 100001 9999905 01.01.2010 100 80 100001 9999901 01.01.2010 -500 -400 100001 9999902 01.01.2010 -120 -100 100001 9999903 01.01.2010 -300 -250 100001 9999904 01.01.2010 0 0 100001 9999905 01.01.2010 -100 -80 100001 9999901 X 01.01.2010 500 400 100001 9999902 X 01.01.2010 120 100 100001 9999903 X 01.01.2010 300 250 100001 9999904 X 01.01.2010 400 350 100001 9999905 X 01.01.2010 100 80 100002 9999906 10.05.2010 200 150 100002 9999907 10.05.2010 300 250 100002 9999908 10.05.2010 510 350 100002 9999909 10.05.2010 125 100So not only measures change but dimension values also.
After compression data can be like this (but I am not doing compression):
Dimensions Key Figures Claim ID Activity ID Remit. Flag Subm. Date Remit. Date Gross Claimed 100001 9999901 01.01.2010 0 0 100001 9999902 01.01.2010 0 0 100001 9999903 01.01.2010 0 0 100001 9999904 01.01.2010 0 0 100001 9999905 01.01.2010 0 0 100001 9999901 X 01.01.2010 11.05.2010 500 400 100001 9999902 X 01.01.2010 11.05.2010 120 100 100001 9999903 X 01.01.2010 11.05.2010 300 250 100001 9999904 X 01.01.2010 11.05.2010 400 350 100001 9999905 X 01.01.2010 11.05.2010 100 80 100002 9999906 10.05.2010 200 150 100002 9999907 10.05.2010 300 250 100002 9999908 10.05.2010 510 350 100002 9999909 10.05.2010 125 100So in the end I have a lot of garbage with zero values, and it is bad for reporting because on this dimensions Queries logic has been built.
For example for 1st claim output will be:
Dimensions Key Figures Claim ID Remit. Flag Subm. Date Remit. Date Gross Claimed 100001 01.01.2010 0 0 X 11.05.2010 1420 1180But I need only second row with values.
I can not drop data and reload it again.
How to perform selective deletion?