Oracle BI metadata repository - IV. Create custom indicators
In " Oracle BI metadata repository - III. Creating Presentation Layer (Presentation Layer) "is described" step-by-step "approach based on both business model to build a presentation layer to which users will access analytical system. The aim of this article is to show ' Step-by-step "approach in how to create their own business model and how the calculations are then made available to end users.
The resulting presentation layer and business model accompanied by their own calculations
NOTE: Calculations can be created in two ways, namely:
A / Creation profits
first Select facts table "F_PRODEJ $"> right click> New Object> Logical Column ... 2nd Name index> select "Use existing logical columns as the source"> Expression Builder and create a calculation of B / creation order of revenues and expenses first Indicators of "Order of income" (VYNOS_PORADI) and "order cost" (NAKLAD_PORADI) to create the same way as earnings, instead of just "reading the two indicators" use the analytic function RANK (
)
2nd Move and rename the new indicators in the Presentation Layer
Note: Note that for the newly defined indicators did not have to set (or even not set) aggregate function. The reason is that the use of pre-logical indicators of aggregate functions which the particular indicator formed. Eg. indicator of profit is made up of logical indicators SUM (Revenue) - SUM (cost) .
The result is a presentation layer for new indicators added
The next part will be devoted to re-Business model - more settingshierarchies for dimensions and create level-based and financial ratios .
The resulting presentation layer and business model accompanied by their own calculationsNOTE: Calculations can be created in two ways, namely:
- based on existing logical columns (described in this article)
- based on physical columns, see more. here
A / Creation profits
first Select facts table "F_PRODEJ $"> right click> New Object> Logical Column ... 2nd Name index> select "Use existing logical columns as the source"> Expression Builder and create a calculation of B / creation order of revenues and expenses first Indicators of "Order of income" (VYNOS_PORADI) and "order cost" (NAKLAD_PORADI) to create the same way as earnings, instead of just "reading the two indicators" use the analytic function RANK (
The result is a presentation layer for new indicators added
The next part will be devoted to re-Business model - more settings
BI Administration Tool in command mode
Some events / operations within the BI Administration Tool can be called from the command line, for example, provide an automated process together (Merge) more BI metadata repository. To run the BI Administration Tool in command mode, you call with parameter / command and path to file (CommandFile ) contains the commands you want to execute in BI AdminToolu. CommandFile is a text file, and each command in it that must be stored on a new line. Example:CommandFile me_prikazy.txt contains a list of commands: Hide New Nove_rep.rpd Save Exit command AdminTool.exe / command me_prikazy.txt Administration Tool runs in hidden mode establishes a new repository called Nove_rep.rpd, stores it and then exits. The commands that can be used in CommandFile include:
- Open FileName [user [password]]
- opens the online repository
- New FileName
- Creates new repository offline
- Save
- saves opened repository
- SaveAs FileName
- saves repository opened under new name
- Close
- closes opened repository
- Exit
- closes AdminTool
- MessageBox [message]
- displays messagebox with the text, the default message is "Siebel Analytics Administration Tool"
- ImportRepository {online | offline} {FileName | DSNname} [user [password]]
- initiates import from The Other repository
- Project ImportRepositoryObject {| "Presentation Catalog" | User | "Security Group" | Variable {Name} | *} [True | False [True | False]]
- Imports object (s) from The Other repository
- ImportRepositoryExecute
- executes the import of repository defined by previous calls it ImportRepository and ImportRepositoryExecute
- Compare FileName [user [password [Outputfile]]]
- Compares current repository with another repository
- Merge FileName1 FileName2 [DecisionFile] [user1 [password1 [user2 [password2]]]]
- merges repositories
- ConsistencyCheck [outputFileName]
- Global Consistency check
- BusinessModelConsistencyCheck businessModelName [outputFileName]
- Consistency check for one business model
- CreateSubset NewRepositoryName MasterRepositoryName numberOfProjects Project1 [Project2 [project3 [...]]] [user [password]]
- Creates and opens a multi-user repository subset
- CheckinSubset ModifiedSubsetRepositoryName LockUserFullName [user [password]]
- checks in ModifiedSubsetRepository into the master repository
- Hide
- hides AdminTool
Oracle BI Metadata Repository - V. Set hierarchies for dimensions, creating a level-based ratios
In " Oracle BI metadata repository - IV. Creating your own indicators"described" step-by-step "approach in how to create their own business model and how the calculations are then made available to end users. The purpose of this article is to show a "step-by- step "approach as the business model of creating hierarchies for dimensions and how to create the basis of level-based and ratios.
The resulting presentation layer and business model supplemented by a hierarchy, level-based and Ratios
A / Creating hierarchies for dimensions Note: The hierarchy of the OBI allow end users to interactively drill / decay values within the dimension. In the current version is natively supported by the OBI-based Level (Balanced) hierarchy. If you need to deal with Value-Based (Unbalanced / Ragged) hierarchy, then instructions can be found here . Of course, in one dimension can have multiple hierarchies, as it can be found here .
1st Select a Business Model "Sales"> right click> New Object> Dimension ... 2nd Name hierarchy third Insert the highest level of the hierarchy in a dimension, a dimension, select the> right click> New Object> Logical Level ... 4th Name the level and mark it as possibly "Grand total level"Note: Mark footing Grand total level is not mandatory . Grand total level is only when you need to create a Level-based indicators for the highest level of dimensions, but the actual dimensions of this level ( "ALL") lacks.Eg. dimension for the "Period" is usual to have the highest level of years, ie. Economics is missing . 5th Insert the lower level of the hierarchy in a dimension, select the level of "ALL"> right click> New Object> Child Level ... 6th Name the level and estimate the number of elements Note: How to make / find the exact number of elements in the dimensions can be found here on the 7th Load the lowest level of the hierarchy in a dimension, select the level of "CELA_EU"> right click> New Object> Child Level ... 8th Name the level and estimate the number of elements Note:How to make / find the exact number of elements in the dimensions can be found here on the 9th Drag the column EU_CELKEM logical hierarchy to the level CELA_EU and the level of STAT STAT 10th Both columns (EU_CELKEM and STAT) as a mark "Logical Level Key" (ie the key through which the drill), select the column level> right click> New Logical Level Key ... 11th Same way, insert the other dimension hierarchy for PERIOD (YEAR> KVARTAL) and sales channels (PRODKANAL_VSE> PRODKANAL).Period hierarchy for the dimension currently do not select a Time Dimension! (will be explained in the following part) B / Creating level-based indicators Note: Level- based index is an indicator whose values are calculated on a specific dimension level or a combination of levels of different dimensions. Example: I want to create a report showing the ratio of "national income" to "revenue accruing to the whole EU." I was able to create it, I need to know the yield of individual states, the total sum (ie total earnings of all states), and the need for rate I get on the same line in the report as income the individual states - this ensures I Level-based index calculated on The whole dimension of the EU level Geography. 1st Select income indicator (table facts "F_PRODEJ $")> right click> Duplicate 2nd Rename the income indicator # 1, select it> right click> Properties ... > Name: VYNOS_ZA_ EU's third Switch to the Levels tab> set the level for the dimension Geography CELA_EU 4th Do the same for indicators of the costs and gains (the result is the location of pointers to the appropriate level of the hierarchy dimension Geography) C / formation ratios 1st Select facts table "F_PRODEJ $"> right click> New Object> Logical Column ... > Name pointer POMER_VYNOS_ZA_EU > select "Use existing logical columns as the source"> ... Expression Builder and create the calculation of income to revenue accruing to the EU .2nd In the same way, create Ratios costs to those of the EU and the profit to gains for the EU . 5th Move and rename the new indicators to the presentation layer (the hierarchy will be used automatically) results are based hierarchy for the dimension Geography, Period, and sales channel, then level-based and Ratios The next part will be re-dedicated business model - specifically how to set time dimension and how to create indicators for comparing the time of the "period" Ago and "period" Today .
The resulting presentation layer and business model supplemented by a hierarchy, level-based and RatiosA / Creating hierarchies for dimensions Note: The hierarchy of the OBI allow end users to interactively drill / decay values within the dimension. In the current version is natively supported by the OBI-based Level (Balanced) hierarchy. If you need to deal with Value-Based (Unbalanced / Ragged) hierarchy, then instructions can be found here . Of course, in one dimension can have multiple hierarchies, as it can be found here .
1st Select a Business Model "Sales"> right click> New Object> Dimension ... 2nd Name hierarchy third Insert the highest level of the hierarchy in a dimension, a dimension, select the> right click> New Object> Logical Level ... 4th Name the level and mark it as possibly "Grand total level"Note: Mark footing Grand total level is not mandatory . Grand total level is only when you need to create a Level-based indicators for the highest level of dimensions, but the actual dimensions of this level ( "ALL") lacks.Eg. dimension for the "Period" is usual to have the highest level of years, ie. Economics is missing . 5th Insert the lower level of the hierarchy in a dimension, select the level of "ALL"> right click> New Object> Child Level ... 6th Name the level and estimate the number of elements Note: How to make / find the exact number of elements in the dimensions can be found here on the 7th Load the lowest level of the hierarchy in a dimension, select the level of "CELA_EU"> right click> New Object> Child Level ... 8th Name the level and estimate the number of elements Note:How to make / find the exact number of elements in the dimensions can be found here on the 9th Drag the column EU_CELKEM logical hierarchy to the level CELA_EU and the level of STAT STAT 10th Both columns (EU_CELKEM and STAT) as a mark "Logical Level Key" (ie the key through which the drill), select the column level> right click> New Logical Level Key ... 11th Same way, insert the other dimension hierarchy for PERIOD (YEAR> KVARTAL) and sales channels (PRODKANAL_VSE> PRODKANAL).Period hierarchy for the dimension currently do not select a Time Dimension! (will be explained in the following part) B / Creating level-based indicators Note: Level- based index is an indicator whose values are calculated on a specific dimension level or a combination of levels of different dimensions. Example: I want to create a report showing the ratio of "national income" to "revenue accruing to the whole EU." I was able to create it, I need to know the yield of individual states, the total sum (ie total earnings of all states), and the need for rate I get on the same line in the report as income the individual states - this ensures I Level-based index calculated on The whole dimension of the EU level Geography. 1st Select income indicator (table facts "F_PRODEJ $")> right click> Duplicate 2nd Rename the income indicator # 1, select it> right click> Properties ... > Name: VYNOS_ZA_ EU's third Switch to the Levels tab> set the level for the dimension Geography CELA_EU 4th Do the same for indicators of the costs and gains (the result is the location of pointers to the appropriate level of the hierarchy dimension Geography) C / formation ratios 1st Select facts table "F_PRODEJ $"> right click> New Object> Logical Column ... > Name pointer POMER_VYNOS_ZA_EU > select "Use existing logical columns as the source"> ... Expression Builder and create the calculation of income to revenue accruing to the EU .2nd In the same way, create Ratios costs to those of the EU and the profit to gains for the EU . 5th Move and rename the new indicators to the presentation layer (the hierarchy will be used automatically) results are based hierarchy for the dimension Geography, Period, and sales channel, then level-based and Ratios The next part will be re-dedicated business model - specifically how to set time dimension and how to create indicators for comparing the time of the "period" Ago and "period" Today .
How to change the default OC4J port number 9704 to port number 80
If you choose to install the BASIC Oracle BI EE or install Oracle BI SE One, then for the actual running BI applications use the Oracle Container for Java (OC4J). The port on which OC4J listens to the default value of the 9704th This value can sometimes be problematic. Eg. if you want to go to BI customers / partners who are not in your network - ie, access via the Internet. In many cases it is only allowed on the firewall port 80 (the other ports can not log on) and process permits a port on the firewall in some organizations is almost impossible. If you want OC4J to reset a port as the most commonly used port number 80, then just edit a set of configuration files and restart OC4J, BI Server and BI PresentationEckhardt.
Oracle BI metadata repository - VI. Setting the time dimension and developing indicators for time comparisons
In " Oracle BI Metadata Repository - V. Set hierarchies for dimensions, creating a level-based ratios "as described" step-by-step "procedure as in the business model of creating hierarchies for dimensions and how to create the basis level-based and ratios. The aim of this article is to show the "step-by-step" process as the business model sets the time dimension, how to create indicators for comparing the time (eg Revenue showing the state before a certain period or YearToDate Revenue). Furthermore, as is easy to create indicators showing changes (eg, difference, percent, index), and finally, how all rozkategorizovat into folders and make available to end users for analytics.
The resulting presentation layer and Business Model by indicators for time comparisons
A / Set the time dimension Note: If you need to create a pointer of type 'obdobíAgo "or" obdobíToDate "then it is necessary to identify the hierarchy of the time dimension with the" Time dimension ".
In the case of actual physical table for the time dimension is appropriate for the data type for columns individual level (eg, YEAR = 2008, MONTH = 200,812) was a string (STRING) - you should not use a numeric type (NUMBER). The reason is that if you use a numeric type in the physical layer of the BI metadata repository used type DOUBLE (of course it depends on what type of physical data goes), which for the "YEAR = 2008" will display the value "2008.00". This then can cause malfunction and counting, "Ago" indicators in the drill values.
1st In Business Model "Sales" select an existing hierarchy time dimension DIM_OBDOBI> right click> Properties ... > Here and check the checkbox "Time dimension" second At the level of the hierarchy KVARTAL DIM_OBDOBI KVARTAL_ID add column (column dimension of $ D_OBDOBI - the PK table) and mark it as a new logical key (Drag & Drop to move the column from the dimension hierarchy to> right click> New Logical Level Key ...) Note : Chronological key is a column containing gradually increasing in value over time. The key data type does not matter, it might be the type of numeric, string or date. The key should be selected column, which is the primary key dimensional table, that through this column is the join between a table made dimensional and facts. 3rdColumn KVARTAL_ID Chronological mark as key (to select the level KVARTAL> right click> Properties ...> Keys tab) B / Creation of indicators showing the status before the first The facts table F_PRODEJ $ create a new logical column for VYNOS_PRED_CTVRTLETIM indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_PRED_CTVRTLETIM> check Use existing logical columns as the source), the second In the Expression Builder to create a calculation to show the state before the (Select Functions> Time Series Functions> Ago> Insert) third The AGO function as "Measure
"Add revenue to the" Level'Quarter level and "Number of Periods"Add" 1 "(one quarter back).
4th The result is a new indicator based VYNOS_PRED_CTVRTLETIM
5th In the same way load indicators VYNOS_PRED_ROKEM and NAKLAD_PRED_ROKEM (
B / Development Indicators YearToDate
first The facts table F_PRODEJ $ create a new logical column for VYNOS_YEAR_TODATE indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_YEAR_TODATE> check Use existing logical columns as the source)
2nd In the Expression Builder to create a calculation to display the status of Today (Select Functions> Time Series Functions> Today> Insert)
3rd The Today function as a "Measure" add revenue to the "Level" Year level.
4th The result is a new indicator based VYNOS_YEAR_TODATE
5th In the same way load indicator NAKLAD_YEAR_TODATE
C / Creation of indicators showing change between now and the past
Note: For purposes of comparison of two indicators is available
1st The facts table, select the column F_PRODEJ $ DECREE> right click> Calculation Wizard
2nd Select a metric to compare the yield indicator (selectVYNOS_PRED_CTVRTLETIM and VYNOS_PRED_ROKEM )
3rd For both indicators allow to generate indicators showing change and percentage change in
4th Calculation Wizard generates four new indicators
5th In the same way and follow the pointer Circulation C / categorization, move and rename the new ones in the presentation layer Note: To simplify the orientation of end users to design reports, presentations can be categorized into different metadata subfolders. The result may be similar to a tree structure:
1st Move and rename the newly created pointer to the presentation layer
2nd In the Presentation layer, select the field "Sales> right click> New Presentation Table
3rd Name the folder "- Revenues"
4th In the same way, create folders with names "- Cost" and "- Profit"
5th The newly created folders divide (Drag & Drop) as indicators of the importance of component sales result is a newly established indicators showing the status before the cumulative indicators showing YearToDate and indicators showing change between now and the past.
The next and final part will be devoted to how to put all this up and make available to end users for reporting and analysis .
The resulting presentation layer and Business Model by indicators for time comparisonsA / Set the time dimension Note: If you need to create a pointer of type 'obdobíAgo "or" obdobíToDate "then it is necessary to identify the hierarchy of the time dimension with the" Time dimension ".
In the case of actual physical table for the time dimension is appropriate for the data type for columns individual level (eg, YEAR = 2008, MONTH = 200,812) was a string (STRING) - you should not use a numeric type (NUMBER). The reason is that if you use a numeric type in the physical layer of the BI metadata repository used type DOUBLE (of course it depends on what type of physical data goes), which for the "YEAR = 2008" will display the value "2008.00". This then can cause malfunction and counting, "Ago" indicators in the drill values.
1st In Business Model "Sales" select an existing hierarchy time dimension DIM_OBDOBI> right click> Properties ... > Here and check the checkbox "Time dimension" second At the level of the hierarchy KVARTAL DIM_OBDOBI KVARTAL_ID add column (column dimension of $ D_OBDOBI - the PK table) and mark it as a new logical key (Drag & Drop to move the column from the dimension hierarchy to> right click> New Logical Level Key ...) Note : Chronological key is a column containing gradually increasing in value over time. The key data type does not matter, it might be the type of numeric, string or date. The key should be selected column, which is the primary key dimensional table, that through this column is the join between a table made dimensional and facts. 3rdColumn KVARTAL_ID Chronological mark as key (to select the level KVARTAL> right click> Properties ...> Keys tab) B / Creation of indicators showing the status before the first The facts table F_PRODEJ $ create a new logical column for VYNOS_PRED_CTVRTLETIM indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_PRED_CTVRTLETIM> check Use existing logical columns as the source), the second In the Expression Builder to create a calculation to show the state before the (Select Functions> Time Series Functions> Ago> Insert) third The AGO function as "Measure
B / Development Indicators YearToDate
first The facts table F_PRODEJ $ create a new logical column for VYNOS_YEAR_TODATE indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_YEAR_TODATE> check Use existing logical columns as the source)
C / Creation of indicators showing change between now and the past
Note: For purposes of comparison of two indicators is available
1st The facts table, select the column F_PRODEJ $ DECREE> right click> Calculation Wizard
5th The newly created folders divide (Drag & Drop) as indicators of the importance of component sales result is a newly established indicators showing the status before the cumulative indicators showing YearToDate and indicators showing change between now and the past.
The next and final part will be devoted to how to put all this up and make available to end users for reporting and analysis .
Subscribe to:
Posts (Atom)
Subscribe To
Posts
All Comments