Why do we need a Data warehouse?
It is used for Reporting and Data Analysis. It provides a Central Repository with data integrated from one or more sources. It stores current and historical data.Why a Data Warehouse is Separate from Operational Databases (Transaction Systems)?
A Transactional system is designed for known workloads and transactions like updating a user record, searching a record, etc. however a Data Warehouse transactions are more complex and present a general form of data. A Transactional system contains the current data of an organization and Data warehouse normally contains the historical data. Transactional system supports parallel processing of multiple transactions. Concurrency control and recovery mechanisms are required to maintain consistency of the database. An Operational database query allows to read and modify operations (delete and Update), while an OLAP query needs only read only access of stored data (Select statement). Data Warehousing involves data cleaning, data integration, and data consolidations.What are the different types of Data Warehouse system?
Data Mart Online Analytical Processing (OLAP) Online Transaction Processing (OLTP) Predictive Analysis.What is data mart?
Data Mart is simplest form of Data Warehouse and it normally focus on a single functional area, such as sales, finance or marketing. As Data Mart usually focus on single system so they get data only from few data sources.What is difference between OLAP and OLTP?
Indexes − OLTP system has only few indexes while in an OLAP system there are many indexes for performance optimization.Joins − In an OLTP system, large number of joins and data is normalized however in an OLAP system there are less joins and de-normalized.
Aggregation − In an OLTP system data is not aggregated while in an OLAP database more aggregations are used.
What do you understand by Additive, semi additive and non-additive measures?
Additive − Measures that can be added across any dimension. Non Additive − Measures that cannot be added across any dimension. Semi Additive − Measures that can be added across some dimensions.What are common aggregate functions? Why do we use aggregate tables in DW?
Common aggregate functions include − Average() Count() Maximum() Median() Minimum() Mode() Sum() These aggregate tables are used for performance optimization to run complex queries in a Data Warehouse.What is difference between star and Snow flakes schema?
In a Star Schema, there are multiple dimension tables in de-normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose. These Schemas are multidimensional structures which are used further to create reports using BI reporting tools. In a Snowflakes Schema, there are multiple dimension tables in normalized form that are joined to only one fact table. These tables are joined in a logical manner to meet some business requirement for analysis purpose.What do you understand by Granularity in a table?
Granularity in a table represents the level of information stored in the table. High granularity of data means that data is at or near the transaction level, which has more detail. Low granularity means that data has low level of information. A fact table is usually designed at a low level of Granularity. This means that we need to find the lowest level of information that can store in a fact table. In date dimension the Granularity level could be year, month, quarter, period, week, and day.What is Slowly Changing Dimension SCD? Can you give one example?
Slowly Changing Dimensions refer to changing value of an attribute over the time. It is one of common concept in a Data Warehouse. Example Andy is an employee of XYZ Inc. He was first located in New York City in July 2015. Original entry in the Employee lookup table has the following record − Employee_IdNameLocation 10001LocationAndyLocationNew York At a later date, he has been relocated to LA, California. How should XYZ Inc. now modify its employee table to reflect this change? This is known as "Slowly Changing Dimension" concept.What is Oracle Business Intelligence Enterprise Edition (OBIEE)?
OBIEE stands for Oracle Business Intelligence Enterprise Edition is set of Business Intelligence tools and is provided by Oracle Corporation. It enables user to delivers the robust set of reporting, ad-hoc query and analysis, OLAP, dashboard, and scorecard functionality with a rich end-user experience that includes visualization, collaboration, alerts and many more options.What are the different OBIEE Server components?
Oracle BI (OBIEE) Server Oracle Presentation Server Application Server Scheduler Cluster ControllerWhat is the use of OBIEE Scheduler?
It is responsible to schedule jobs in OBIEE repository. When you create repository, OBIEE also create a table inside repository which saves all schedule related information. Also to run agents in 11g, this component is mandatory. All jobs which are scheduled by Scheduler can be monitored by job manager.What is the difference between ODBC and OCI?
ODBC stands for Open Database Connectivity and is a Universal data Connector. OCI stands for Oracle Call Interface and is used to connect Oracle data source.What is the use of Oracle BI Repository?
OBIEE repository contains all metadata of the BI Server and is managed through the administration tool. It is used to store information about the application environment like − Data Modeling Aggregate Navigation Caching Security Connectivity information SQL information The BI Server can access multiple repositories.
What is a Physical Join in OBIEE Repository?
When you create a Repository in OBIEE system, physical join is commonly used in Physical layer. Physical joins helps to understand how two table should be joined to each other. Physical joins are normally expressed with the use of Equal operator.
Can we use Physical Join in BMM layer?
Yes but it is rarely seen.
How can you sort in Reports in OBIEE 11g?
Click on modify and then click on sort (order by icon) on the relevant column in the criteria pane.
How will you execute Direct SQL in OBIEE?
To execute SQL, click direct database request below the subject area and you can execute Direct SQL in OBIEE.
What is caching concept and how it is linked with Query Performance?
To improve query performance, we disable BI server cache option. Open a browser and enter the below URL to open Fusion Middleware Control Enterprise Manager − http://<machine name>:7001/em Enter user name and password and click on login. In the left side, expand Business Intelligence → coreapplication → Capacity Management tab → Performance Disable Caching Enable BI Server Cache section is by default checked → Click on Lock and Edit Configuration → Close. Enable BI Server Cache Now deselect cache enabled option → It is used to improve query performance → Apply → Activate Changes → Completed Successfully.
What is the use of OBIEE Business Model and Mapping layer?
As mentioned in previous article, Business Layer defines the business or logical model of objects and their mapping between business model and Schema in Physical layer. It simplifies the Physical Schema and maps the user business requirement to physical tables. 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.
What are the different steps involved in defining Business Layer?
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 Deleting Unnecessary Logical Object Creating Measures (Aggregations)How do you create Logical tables in BMM layer?
There are two ways of creating Logical tables/objects in BMM layer − First one is dragging Physical tables to Business Model which is the fastest way of defining Logical tables. When you drag the tables from 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 and it doesn’t effect objects in Physical layer. 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.How do you perform testing of a repository? Where do you perform testing of OBIEE Repositroy?
You can check the repository for errors by using the consistency checking option. Go to File → click on Check Global Consistency → YesWhat is Query logging?
You can setup query logging level for individual users in OBIEE. Logging level control the information that you will retrieve in log file.What are the different query logging levels?
In normal scenario − User has a logging level set to 0 and Administrator has a logging level set to 2. Logging level can have values starting from Level 0 to level 5. Level 0 means no logging and Level 5 means maximum logging level information.How will you enable or disable caching in the system level and table level?
In the NQSConfig.ini file use ENABLE under CACHE Section for System Level For tables, if we want to enable the cache at table level , open the repository in offline mode This should be different from the current repository and click enable or disable the cache.What is the use of table alias in OBIEE 11g?
Table alias is used for creating self joins.How do you create table alias in OBIEE?
Table alias can be created by right clicking the table in the physical layer then click alias.Have you created Hierarchy in OBIEE 11g, how?
Yes, we can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. This can be done by right clicking the dimension table and click create dimension and then we can manually define the hierarchy and its levels.What are the different types of dimension hierarchies?
There are two types of dimensional hierarchies that are possible − Dimensions with level based hierarchies Dimension with Parent-child hierarchies In Level based hierarchies, members can be of different types and member of same type comes only at single level. In Parent-child hierarchies, all members are of same type.What do you understand by Level based measures?
Level based measures are created to perform calculation at a specific level of aggregation. They allow to return data at multiple levels of aggregation with one single query. It also allows to create share measures.
How Aggregation works in OBIEE?
When you execute a query in OBIEE, BI server looks for the resources which has information to answer the query. Out of all available sources, server selects the most aggregated source to answer that query.
What are the different types of Variable in OBIEE?
In OBIEE there are two types on variables that are commonly used − Repository Variables Session Variables Apart from this you can also define Presentation and Request variables.
What do you understand by Repository Variables? What are different type of Repository variables?
A Repository variable has a single value at any point of time. Repository variables are defined using Oracle BI Administration tool. Repository variables can be used in place of constants in Expression Builder Wizard. There are two types of Repository variables − Static Repository Variables Dynamic Repository Variables.
What is the difference between Static and Dynamic Repository variables?
Static Repository variables are defined in variable dialogue box and their value exists until they are changed by Administrator. Static repository variables contain default initializers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, etc.. You cannot use any other value or expression as the default initializer for a static repository variable. Dynamic repository variables are same as static variables but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You can also set up a schedule that the Oracle BI Server will follow to execute the query and refresh the value of the variable periodically. When the value of a dynamic repository variable changes, all cache entries associated with a business model are deleted automatically.What are the Session Variables?
Session variables are similar to dynamic repository variables and that they obtain their values from initialization blocks. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.What is the use of OBIEE Dashboards? What are the Dashboard alerts?
OBIEE Dashboard is a tool that enables end users to run ad-hoc reports and analysis as per business requirement model. Interactive dashboards are pixel perfect reports which can be directly viewed or printed by end users. OBIEE Dashboard is part of Oracle BI Presentation layer services. If your end user is not interested in seeing all the data in the dashboard, it allows you to add prompts to the dashboard that allows user to enter what he wants to see. Dashboards also allows end users to select from Drop-down lists, multi-select boxes and selection of columns to display in the reports.Dashboard Alerts Oracle BI Dashboard also allows you to set up alerts to sales executives that comes up on the Interactive Dashboard whenever company’s projected sales is going to be below forecast.
What is the use of filters in OBIEE?
Filters are used to limit the results that are displayed when an analysis is run, so that the results answer a particular question. Based on the filters, only those results are shown that matches the criteria passed in the filter condition. Filters are applied directly to attribute columns and measure columns. Filters are applied before the query is aggregated and affect the query and thus the resulting values for measures. Example − Suppose you have a list of members in which the aggregate sums to 100. Over the time, more members meet the set filter criteria, which increases the aggregate sum to 200.
What is the difference between Prompts and Filters?
A Prompt is a special type of filter that is used to filter analyses embedded in a dashboard. The main reason to use a dashboard prompt is that it allows the user to customize analysis output and also allows flexibility to change parameters of a report.
What is Named prompt?
The prompt created at the dashboard level is called a Named prompt. This Prompt is created outside of a specific dashboard and stored in the catalog as a prompt. You can apply a Named prompt to any dashboard or dashboard page that contains the columns, mentioned in the prompt. It can filter one or any number of analyses embedded on the same dashboard page. You can create and save these named prompts to a private folder or a shared folder. A Named prompt always appear on the dashboard page and user can prompt for different values without having to rerun the dashboard. A named prompt can also interact with selection steps. You can specify a dashboard prompt to override a specific selection step.
What are Inline prompts?
Inline prompts are embedded in an analysis and are not stored in the Catalog for reuse. An Inline prompt provides general filtering of a column within the analysis, and depending on how it is configured. Inline Prompt work independently from a dashboard filter, which determines values for all matching columns on the dashboard. An inline prompt is an initial prompt. When the user selects the prompt value, the prompt field disappears from the analysis.
What is the use of Column Prompts? Where they are used?
A column prompt is the most common and flexible prompt type. A column prompt enables you to build very specific value prompts to either stand alone on the dashboard or analysis or to expand or refine existing dashboard and analysis filters. Column prompts can be created for hierarchical, measure, or attribute columns at the analysis or dashboard level.What do you understand by connection pool and how many connection pools did you have in your last project?
Connection pool is needed for every physical database and it contains information about the connection to the database. We had multiple connection pools to save time of users.What do you understand by Data Level Security and Object Level Security?
Data level security controls the type and amount of data that you can see in a report. Object level security provides security for objects stored in the OBIEE web catalog like dashboards, dashboards pages, folder and reports.What is OBIEE Security? How do you define a Security policy in OBIEE system?
OBIEE security is defined by use of a role based access control model. Security in OBIEE is defined in terms of Roles that are aligned to different directory server groups and users. Security structure defines with below components − The directory Server User and Group managed by the Authentication provider. The application roles managed by the Policy store provide.What are different Application roles in BI system?
Security is normally defined in terms of Application roles that are assigned to directory server users and groups. Example − the default Application roles are BIAdministrator, BIConsumer, and BIAuthor.What is the difference between OBIEE 10g and OBIEE 11g administration?
In OBIEE 10g, most of OBIEE administration tasks were mostly performed either through the Administration tool, the web-based Presentation Server administration screen, or through editing files in the filesystem. You had around 700 or so configuration options spread over multiple tools and configuration files, with some options like users and groups were embedded in unrelated repositories (the RPD). In OBIEE 11g, all administration and configuration tasks are moved into Fusion Middleware Control also called as Enterprise Manager.What are the different ways of assigning permissions to OBIEE repository?
You can assign permissions in one of the following ways − To application roles − Most recommended way of assigning permissions and privileges. To individual users − This is difficult to manage where you can assign permissions and privileges to specific users. To Catalog groups − It was used in previous releases for backward compatibility maintenance.