Dear leaders,
Man there is a problem, I am a developer, the inside of the bank statements to report development is to use oracle will appear when an agency some dimension due to less volume without the data, thus result in report development can't statistics to the dimension of data, but since the report style is line to so I need in the shape of a line to show the report come out, if no data is shown to make statements, so that it appears as the background without the data, but the report to display the data, at present my solution is, in fact table and dimension table, unrelated condition, let him produce cartesian product, and then through the cartesian product data and original minus the fact table, to achieve completion dimension data, but do have a problem, through the cartesian product result set data particularly big, resulting in a query is slow, it is such a situation,
Because we are presenting bank statements and bank data quantity is big, I take a few details about 1 to 200 million data, through the summary and logic processing, some statements I fact table before completion dimension was not after processing data is 2000 w, and the dimension of the dimension table needs to display 50, thus causes the cartesian product data volume is 2000 w *=10 w $50 data, I dealt with in the dimension of the completion of SQL about 30 minutes, do you have any brother provides a complete dimension, time can be compressed to 10 minutes, because I this report processing SQL is the slowest completion dimension where other logical processing through oracle a parallel basic solution for 1 minute,
The above said is database data processing situation,
Our side using cognos report development tool, although can through the table inside cognos connection and incoming parameters determine the front desk page only a set of statements showing data (for example, because the report is looking a customer to select a date institutions generally, currencies, such as the selection criteria, by the fact that these conditions associated with my table is finally find out the data of 50, so can be done through cognos connection outside dimension of the completion of the above situation, I, but I don't want to change to cognos, I want to know the oracle treatment completion inside dimension of this kind of circumstance should be how to deal with,)

But I want to know in the case of not change cogons, settled your completion dimensions in oracle, please brother give a train of thought,

CodePudding user response:

Institutions of dimension table and fact table left

CodePudding user response:

You said outside connection, I know, this can be implemented in cognos, but oracle side outer join use there will be a problem, because we acquire the data and is not a bank is a lot of ways to take data, fact table finally I will have a lot of data, but through them at the front desk removed after filtering condition, only 50, if you're outside connection to write cycle on my side, I feel that more slowly,

CodePudding user response:

Give a simplified tables and data, never done this function of people don't know what you're saying,

CodePudding user response:

refer to the second floor weixin_39869990 response:

outer join you said I know, this can be implemented in cognos, but oracle connection use there will be a problem, because we acquire the data and is not a bank is a lot of ways to take data, fact table finally I will have a lot of data, but through their front desk removed after filtering condition, only 50, if you're outside connection to write cycle on my side, I feel that more slowly,

create your own dimension table, updated daily