Obiee 简明教程
OBIEE – Business Layer
业务层定义了对象及其在业务模型和物理层中与架构之间的映射的业务或逻辑模型。它简化了物理架构，并将用户业务需求映射到物理表。
Business Layer defines the business or logical model of objects and their mapping between business model and Schema in the physical layer. It simplifies the Physical Schema and maps the user business requirement to physical tables.
OBIEE 系统管理工具的业务模型和映射层可以包含一个或多个业务模型对象。业务模型对象定义了业务模型定义以及对业务模型的逻辑表到物理表的映射。
The business model and mapping layer of OBIEE system administration tool can contain one or more business model objects. A business model object defines the business model definitions and the mappings from logical to physical tables for the business model.
业务模型用于简化架构结构并将用户的业务需求映射到物理数据源。它涉及在业务模型中创建逻辑表和列。每个逻辑表可以有一个或多个物理对象作为来源。
The business model is used to simplify the schema structure and maps the users’ business requirement to physical data source. It involves creation of logical tables and columns in the business model. Each logical table can have one or more physical objects as sources.
逻辑表有两个类别——事实和维度。逻辑事实表包含要进行分析的度量，而逻辑维度表包含架构中度量和对象的信息。
There are two categories of logical tables − fact and dimension. Logical fact tables contain the measures on which analysis is done and Logical dimension tables contain the information about measures and objects in Schema.
在使用 OBIEE 管理工具创建新存储库时，在定义物理层后，创建连接并识别外键。下一步是创建存储库的业务模型和映射 BMM 层。
While creating a new repository using OBIEE administration tool, once you define the physical layer, create joins and identify foreign keys. The next step is to create a business model and mapping BMM layer of the repository.
定义业务层涉及的步骤——
Steps involved in defining Business Layer −
-
Create a business model
-
Examine logical joins
-
Examine logical columns
-
Examine logical table sources
-
Rename logical table objects manually
-
Rename logical table objects using the rename wizard and delete unnecessary logical object
-
Creating measures (Aggregations)
Create Business Layer in the Repository
要在存储库中创建业务层，请右键单击 → 新业务模型 → 输入业务模型名称并单击确定。您还可以根据需要添加该业务模型的描述。
To create a business layer in the repository, right-click → New Business Model → Enter the name of Business Model and click OK. You can also add description of this Business Model if you want.
Logical Tables and Objects in BMM Layer
OBIEE 存储库中的逻辑表存在于业务模型和映射 BMM 层中。业务模型图应至少包含两个逻辑表，并且需要在它们之间定义关系。
Logical tables in OBIEE repository exist in the Business Model and Mapping BMM layer. The business model diagram should contain at least two logical tables and you need to define relationships between them.
每个逻辑表应有一个或多个逻辑列和一个或多个与之关联的逻辑表来源。您还可以更改逻辑表名称、重新排列逻辑表中的对象并使用主键和外键定义逻辑连接。
Each logical table should have one or more logical columns and one or more logical table sources associated with it. You can also change the logical table name, reorder the objects in logical table and define logical joins using primary and foreign keys.
Create Logical Tables Under BMM Layer
在 BMM 层创建逻辑表/对象有两种方法——
There are two ways of creating logical tables/objects in BMM layer −
First method 将物理表拖动到业务模型中，这是定义逻辑表的最快方式。当您将表从物理层拖动到 BMM 层时，它还会自动保留连接和键。如果您愿意，可以更改逻辑表中的连接和键，它不会影响物理层中的对象。
First method is dragging physical tables to Business Model which is the fastest way of defining logical tables. When you drag the tables from the physical layer to BMM layer, it also preserves the joins and keys automatically. If you want you can change the joins and keys in logical tables, it doesn’t affect objects in the physical layer.
选择物理层下要添加到业务模型层中的物理表/别名表，并将这些表拖至 BMM 层下。
Select physical tables/alias tables under the physical layer that you want to add to Business Model Layer and drag those table under BMM layer.
这些表称为逻辑表，列在业务模型和映射层中称为逻辑对象。
These tables are known as logical tables and columns are called Logical objects in Business Model and Mapping Layer.
Second method 是手动创建逻辑表。在业务模型和映射层中，右键单击业务模型 → 选择新建对象 → 逻辑表 → 逻辑表对话框将出现。
Second method is to create a logical table manually. In the Business Model and Mapping layer, right-click the business model → Select New Object → Logical Table → Logical Table dialog box appears.
转至常规选项卡 → 为逻辑表输入名称 → 输入表的描述 → 单击确定。
Go to General tab → Enter name for the logical table → Type a description of the table → Click OK.
Create Logical Columns
当您从物理层将表拖动到业务模型层时，BMM 层中的逻辑列将自动创建。
Logical columns in BMM layer are automatically created when you drag tables from the physical layer to the business model layer.
如果逻辑列是主键，则此列将显示为密钥图标。如果该列具有聚合函数，则它将显示为西格玛图标。您还可以在业务模型和映射层中重新排序逻辑列。
If the logical column is a primary key, this column is displayed with the key icon. If the column has an aggregation function, it is displayed with a sigma icon. You can also reorder logical columns in the Business Model and Mapping layer.
Create a Logical Column
在 BMM 层中，右键单击逻辑表 → 选择新建对象 → 逻辑列 → 逻辑列对话框将出现，单击常规选项卡。
In BMM layer, right-click on logical table → select New Object → Logical Column → Logical Column dialog box will appear, click General tab.
为逻辑列输入一个名称。业务模型和逻辑表名称将显示在列名称正下方的“属于表”字段中 → 单击确定。
Type a name for the logical column. The name of the business model and the logical table appear in the “Belongs to Table” field just below column name → click OK.
您还可以对逻辑列应用聚合。单击聚合选项卡 → 从下拉列表中选择聚合规则 → 单击确定。
You can also apply Aggregations on the logical columns. Click Aggregation tab → Select Aggregation rule from the dropdown list → Click OK.
在列上应用聚合函数后，将更改逻辑列图标以显示已应用聚合规则。
Once you apply Aggregate function on a column, logical column icon is changed to show Aggregation rule is applied.
您还可以在表中移动或复制逻辑列 -
You can also move or copy logical column in tables −
在 BMM 层中，您可以选择要移动的多个列。在移动列的来源对话框中，在操作区域中，选择一项操作。如果您选择忽略，则不会在表的来源文件夹中添加任何逻辑来源。
In the BMM layer, you can select multiple columns to move. In the Sources for moved columns dialog box, in the Action area, select an action. If you select Ignore, no logical source will be added in the Sources folder of the table.
如果您单击新建，则将在来源文件夹中创建包含逻辑列的逻辑来源副本。如果您从下拉列表中选择使用现有选项，则必须从表的来源文件夹中选择一个逻辑来源。
If you click on Create new, a copy of the logical source with the logical column will be created in the Sources folder. If you select Use existing option, from the drop-down list, you must select a logical source from the Sources folder of the table.
Create Logical Complex Joins / Logical Foreign Keys
BMM 层中的逻辑表使用逻辑连接相互连接。基数是逻辑连接中的关键定义参数之一。基数关系一对多意味着在第一个逻辑维度表中的每一行中，在第二个逻辑表中都有 0、1 或多行。
Logical tables in BMM layer are joined to each other using logical joins. Cardinality is one of the key defining parameter in logical joins. Cardinality relation one-to-many means that each row in first logical dimension table there are 0, 1, many rows in second logical table.
Conditions to Create Logical Joins Automatically
将物理层的所有表拖到业务模型层时，将在信息库中自动创建逻辑连接。这种情况下很少发生，仅在简单的业务模型中发生。
When you drag all the tables of the physical layer to business model layer, logical joins are automatically created in Repository. This condition rarely happens only in case of simple business models.
当逻辑连接与物理连接相同是，它们会自动创建。BMM 层中的逻辑连接以两种方式创建 -
When logical joins are same as physical joins, they are automatically created. Logical joins in BMM layer are created in two ways −
-
Business Model Diagram (already covered while designing repository)
-
Joins Manager
无法使用 BMM 层中的逻辑连接指定表达式或列来创建连接，就像在显示定义物理连接的表达式和列名的物理层中一样。
Logical joins in BMM layer cannot be specified using expressions or columns on which to create the join like in the physical layer where expressions and column names are shown on which physical joins are defined.
Create Logical Joins/Logical Foreign keys Using Join Manager Tool
首先让我们了解如何使用连接管理器创建逻辑外键。
First let us see how to create logical foreign keys using Join Manager.
在管理工具栏中，转到管理 → 连接。连接管理器对话框将出现 → 转到操作选项卡 → 新建 → 逻辑外键。
In the Administration Tool toolbar, go to Manage → Joins. The Joins Manager dialog box appears → Go to Action tab → New → Logical Foreign Key.
现在在浏览对话框中，双击一个表 → 将出现逻辑外键对话框 → 输入外键名称 → 从对话框的下拉表列表中，选择外键引用的表 → 选择外键引用的左表中的列 → 选择构成外键列的右表中的列 → 从类型下拉列表中选择连接类型。要打开表达式生成器，单击表达式窗格右侧的按钮 → 表达式显示在表达式窗格中 → 单击确定保存工作。
Now in the Browse dialog box, double-click a table → The Logical Foreign Key dialog box appears → Enter the name for the foreign key → From Table drop-down list of the dialog box, select the table that the foreign key references → Select the columns in the left table that the foreign key references → Select the columns in the right table that make up the foreign key columns → Select the join type from the Type drop-down list. To open the Expression Builder, click the button to the right of the Expression pane → The expression displays in the Expression pane → click OK to save the work.
Create a Logical Complex Join using Join Manager
与使用逻辑外键相比，在业务模型和映射层中推荐逻辑复合连接。
Logical complex joins are recommended in Business Model and mapping layer as compared to the use of logical foreign keys.
在管理工具栏中，转到管理 → 连接 → 连接管理器对话框出现 → 转到操作 → 单击新建 → 逻辑复合连接。
In the Administration Tool toolbar, go to Manage → Join → Joins Manager dialog box appears → Go to Action → Click New → Logical Complex Join.
它将打开一个逻辑连接对话框 → 为复合连接键入名称 → 在对话框的左侧和右侧的下拉表列表中，选择复合连接引用的表 → 从类型下拉列表中选择连接类型 → 单击确定。
It will open a logical Join dialog box → Type a name for the complex join → In the table drop-down lists on the left and right side of the dialog box, select the tables that the complex join references → Select the join type from the Type drop-down list → Click OK.
Note − 您还可以从下拉列表中将一个表定义为驱动表。当表大小过大时，这用于性能优化。如果表大小很小，少于 1000 行，则不应将其定义为驱动表，因为它可能导致性能下降。
Note − You can also define a table as driving table from the drop-down list. This is used for performance optimization when the table size is too large. If the table size is small, less than 1000 rows, it shouldn’t be defined as driving table as it can result in performance degradation.
Dimensions and Hierarchical Levels
逻辑维度存在于 OBIEE 存储库的 BMM 和展示层中。创建具有层次结构的逻辑维度使您能够定义随维度变化的聚合规则。它还提供了分析和仪表板中图表和表格的向下钻取选项，并定义了聚合源的内容。
Logical dimensions exist in BMM and Presentation layer of OBIEE repository. Creating logical dimensions with hierarchies allows you to define aggregation rules that vary with dimensions. It also provides a drill-down option on the charts and tables in analyses and dashboards, and define the content of aggregate sources.
Create logical dimension with Hierarchical level
以脱机模式打开存储库 → 转到文件 → 打开 → 脱机 → 选择存储库 .rpd 文件并单击打开 → 输入存储库密码 → 单击确定。
Open the Repository in Offline mode → Go to File → Open → Offline → Select Repository .rpd file and click on open → Enter Repository password → click OK.
下一步是创建逻辑维度和逻辑级别。
Next step is to create logical dimension and logical levels.
右键单击 BMM 层中的业务模型名称 → 新对象 → 逻辑维度 → 具有基于级别层次结构的维度。它将打开对话框 → 输入名称 → 单击确定。
Right click on Business model name in BMM layer → New Object → Logical Dimension → Dimension with level-based hierarchy. It will open the dialogue box → Enter the name → click OK.
要创建逻辑级别，请右键单击逻辑维度 → 新对象 → 逻辑级别。
To create a logical level, right-click on logical dimension → New Object → Logical Level.
输入逻辑级别的名称，例如：Product_Name
Enter the name of logical level example: Product_Name
如果此级别是总计级别，则选中该复选框，系统将默认将此级别的元素数设置为 1 → 单击确定。
If this level is Grand total level, select the checkbox and the system will set number of element at this level to 1 by default → Click OK.
如果希望逻辑级别向上汇总到其父级，则选中支持汇总到父元素复选框 → 单击确定。
If you want the logical level to roll up to its parent, select the Supports rollup to parent elements checkbox → click OK.
如果逻辑级别不是总计级别，并且不向上汇总，则不选中任何复选框 → 单击确定。
If the logical level is not the grand total level and does not roll up, do not select any of the checkbox → Click OK.
Parent-Child Hierarchies
您还可以通过以下步骤在逻辑级别中添加父子层次结构：
You can also add parent-child hierarchies in logical level by following these steps −
要定义子逻辑级别，请单击浏览对话框中的添加，选择子逻辑级别，然后单击确定。
To define child logical levels, click Add in the Browse dialog box, select the child logical levels and click OK.
您还可以右键单击逻辑级别 → 新对象 → 子级别。
You can also right-click on logical level → New Object → Child level.
输入子级别的名称 → 确定。您可以重复此操作来根据要求为所有逻辑列添加多个子级别。您还可以以类似的方式添加时间和区域层次结构。
Enter the name of child level → Ok. You can repeat this to add multiple child levels for all logical columns as per requirement. You can also add Time and Region hierarchies in a similar way.
现在把表格的逻辑列添加到逻辑层级→选择 BMM 层中的逻辑列，并将其拖放到要映射到的逻辑子级名称。同样，您可以拖动逻辑表的所有列来创建父级-子级层级。
Now to add logical columns of a table to logical level → select logical column in BMM layer and drag it to logical level child name to which you want to map. Similarly you can drag all the columns of logical table to create parent-child hierarchies.
当您创建子级时，可以通过双击逻辑层级进行检查，并且该层级显示在该层级的子级列表下。您可以使用这个框顶部的“+”或“X”选项添加或删除子级。
When you create a child level, it can be checked by a double-click on the logical level and it is displayed under child levels list of that level. You can add or delete child levels by using ‘+’ or ‘X’ option on top of this box.
Add Calculation to a Fact Table
双击逻辑事实表中的列名→转到聚合选项卡，然后从下拉列表中选择聚合函数→单击确定。
Double-click on the column name in logical Fact table → Go to Aggregation tab and select the Aggregate function from the drop-down list → Click OK.
度量表示可加的数据，例如总收入或总数量。单击顶部的保存选项以保存存储库。
Measures represents data that is additive, such as total revenue or total quantity. Click on save option at the top to save the repository.
可以使用多种聚合函数，例如求和、平均值、计数、最大值、最小值等。
There are various Aggregate functions that can be used like Sum, Average, Count, Max, Min, etc.