Sometimes you need to see in the table and values for which there are no facts. For example, in a display requirement odbobí all countries in which the stores and their sales, including countries in which in a given period not sold anything. Given the requirement can be solved using OUTER JOIN, which is available in the Business Model metadata repository. But it is then used for each query and can affect the speed of queries returned from the database. An alternative is to simulate the Outer Join using "fictitious" facts table and let the BI Server to join the table between facts and "fictitious" facts table performing a Cartesian product. To First vytoříme facts table "DUAL" as a select: SELECT 1 FROM DUAL AS SHOW_ALL and add column "SHOW_ALL" into the columns. Then set the Complex Physical Joinbetween the tables of dimensions, where we simulate the Outer Join.wipe the table "DUAL" move to the Business model and set the Complex Join between the tables of dimensions. The column "SHOW_ALL" should be set aggregation rule, such as SUM, and add a column to the Presentation layer, so that users can use it to create and define reports. Sample reports of sales by sales channel and individual countries. Without the use of value "SHOW_ALL" we see only the values for the country and sales channels where some shops have been carried out. Adding column "SHOW_ALL" get an overview of where the sale took place. To SHOW_ALL column in the report out of the way, so it:
- the pivot table to give the excluded
- common set of tables in a format attribute to hide columns
Posts
