Wednesday, April 26, 2017

ODI 12c R2 (12.2.1.2.6) version latest features.

ODI 12c R2  (12.2.1.2.6) version latest features.


Big Data investments include Spark Streaming support,
 Apache Kafka and Apache Cassandra support, enhanced support around Hadoop Complex Types and
Storage Formats in addition to enhancements to ODI’s Big Data Configuration
Wizard.


New Flatten and Jagged Components

 Oracle Data Integrator introduces the Flatten component for mappings which allows complex sub-structures to be processed as part of a flat list of attributes. Oracle Data Integrator also introduces the Jagged component which converts Key-Value lists into named attributes for furtherprocessing.





Support for Cubes and Dimensions
Core ETL – ELT enhancements have been made; where ODI now provides support for two types of dimensional
objects: Cubes and Dimensions. Users can create and use Cubes and Dimensions objects directly in Mappings to
improve developer productivity with out of the box patterns that automate the loading of dimensional objects. This
also allows for improved Type 2 Slowly Changing Dimensions and brand new Type 3 Slowly Changing Dimensions
support with ODI.




12.1.3 features.

Design-Time Experience and Productivity
New Model and Topology Objects Wizard
The Create New Model and Topology Objects dialog in the Designer Navigator provides the ability to create a new
model and associate it with new or existing topology objects, if connected to a work repository. This dialog enables
you to create topology objects without having to use Topology editors unless more advanced options are required.



Orchestration of Oracle Data Integrator Jobs using Oozie

Oracle Data Integrator supports both the traditional Oracle Data Integrator Agent and Apache Oozie as the
orchestration engine for Jobs – including: mappings, packages, scenarios, and procedures. Apache Oozie allows
native execution on a Hadoop infrastructure without installing an Oracle Data Integrator agent for orchestration.
Users can utilize Oozie tooling to schedule, manage, and monitor the Jobs. Oracle Data Integrator uses Oozie’s
native actions to execute Hadoop processes and conditional branching logic.




Oracle Data Integrator Console Enhancements
Now security tasks can be done in ODI Console.

The overall look and feel of the Oracle Data Integrator Console has been improved. Additionally, security tasks
such as creating users or profiles can now be performed using the Console. Release Management activities can be
performed through the Console, and functionality related to Topology tasks has been enhanced.






Enhanced Big Data Support
Oracle Data Integrator’s previous release brought together a series of advanced Big Data updates and features that
Oracle Data Integration is rolling out for customers to help take their Hadoop projects to the next level with support
for Apache Spark, Apache Pig, and orchestration using Oozie. With this release, we continue to enhance this
functionality by allowing users to choose between Task and Session execution modes for Oozie workflow
generation. Users can choose between Task and Session execution modes for Oozie workflow generation. The
new Session mode allows support for transactions, scripting, and loops in packages. Oracle Data Integrator will
automatically choose the correct mode based on the executed object, or the mode can be manually selected.



Enhanced Hive Driver and Knowledge Modules
Oracle Data Integrator includes the WebLogic Hive JDBC driver that provides a number of advantages such as full
JDBC compliance and improved performance. All Hive Knowledge Modules have been rewritten to take advantage
of this new driver. Also, Knowledge Modules whose main purpose is to load from a source are now provided as
Loading Knowledge Modules, enabling them to be combined in a single mapping with other Loading Knowledge
Modules. A new class of “_direct load_” Loading Knowledge Modules also allows loading of target without
intermediate staging. Additionally, the table function component has been extended to support Hive constructs.

New/Updated Hive Component Style Knowledge Modules:
  1. LKM SQL to HiveSQOOP
  2. LKM SQL to File SQOOPDirect
  3. LKM SQL to HBase SQOOPDirect
  4. LKM HBase to SQLSQOOP
  5. LKM File to SQLSQOOP
  6. LKM Hive to SQLSQOOP
  7. LKM File to Hive LOADDATA
  8. LKM File to Hive LOAD DATA Direct
  9. LKM HBase to HiveHBASE-SERDE
  10. LKM Hive to HBase Incremental Update HBASE-SERDE Direct
  11. IKM HiveAppend
  12. LKM Hive to FileDirect
  13. LKM Hive to OracleOLH-OSCH
  14. LKM File to Oracle OLH-OSCH Direct
  15. LKM Hive to OracleOLH-OSCH
  16. LKM Hive to Oracle OLH-OSCH Direct
  17. XKM Hive Sort






Cancelling of Import/Export and Reverse Engineering Operations
Oracle Data Integrator now provides the ability to cancel import/export and reverse engineering operations that may
run for a longtime.


Support for Analytic or Window Functions
Oracle Data Integrator supports Analytic or Window functions out of the box at the Mapping Level. Analytic functions
such as PERCENT_RANK, LAST, FIRST, or LAG can be used at the Mapping Expression level in any component.


Ability to View the List of Users Connected to the Studio/Repository
The Review User Activity menu item has been added to the Security Menu. This allows viewing, purging, and
saving of user activity record in the User Connections dialog. This is available in both the Oracle Data Integration
Studio and the Oracle Data Integrator Console.

Complex File Enhancements
Oracle Data Integrator’s Native Format builder utility is now included with Oracle Data Integrator Studio and allows
you to create nXSD files without having to leave the Oracle Data Integrator user interface


Complex File, File, LDAP, JMS Queue XML, JMS Topic XML, and XML Technology Enhancements
Oracle Data Integrator’s JDBC properties for Complex File, File, LDAP, JMS Queue XML, and XML technologies
are now displayed att the Data Server level along with default values where applicable and a description of those
properties, in an effort to enhance usability.



OBIEE Interview Questions and answers

Define repository in terms of OBIEE
o Repository stores the Meta data information. OBIEE repository is a file system ,extension of the repository file. rpd.
o META DATA REPOSITORY
o With OBIEE Analytics Server, all the rules needed for security, data modeling, aggregate navigation, caching, and connectivity is stored in metadata repositories.
o Each metadata repository can store multiple business models. OBIEE Analytics Server can access multiple repositories

What is the end to end life cycle of OBIEE Analytics?
o OBIEE Analytics life cycle
1. Gather Business Requirements
2. Identify source systems
3. Design ETL to load to a DW if source data doesn’t exist.
4. Build a repository
5. Build dashboard or use answers for reporting.
6. Define security (LDAP or External table…)
7. Based on performance, decide on aggregations and/or caching mechanism.
8. Testing and QA.

What were you schemas? How does OBIEE Architecture works? Explain the three layers. How do you import sources?
o There are five parts of OBIEE Components
1. OBIEE Clients (OBIEE Administrator)
2. OBIEE Analysis (Answers)
3. OBIEE Dashboards
4. OBIEE Agent( Scheduler)
5. BI Publisher
o Metadata that represents the analytical Model Is created using the OBIEE Analytics Administration tool.
o Repository divided into three layer
1. Physical – Represents the data Sources
2. Business – models the Data sources into Facts And Dimension
3. Presentation – Specifies the users view of the model;rendered in OBIEE answer
If you have 3 facts and 4 dimension and you need to join would you recommend joining fact with fact? If no than what is the option? Why you won’t join fact to fact?
o In the BMM layer, create one logical table (fact) and add the 3 fact table as logical table source
What is connection pool and how many connection pools did you have in your last project?
o connection pool is needed for every physical database.
o It contains information about the connection to the database, not the database itself.
o Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass through .
o We can have multiple connection pools for each group to avoid waitin
Purpose of Alias Tables
o An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a logical table source, and inherits all its column definitions and some properties from the logical table source. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures, and select statements. An alias table can be a reference to any of these logical table source types.
o Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table:
” To reuse an existing table more than once in your physical layer (without having to import it several times)
” To set up multiple alias tables, each with different keys, names, or joins
o To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemas to Dimensional Schemas.
How do you define the relationship between facts and dimensions in BMM layer?
o Using complex join ,we can define relationship between facts and dimentions in BMM layer.
What is time series wizard? When and how do you use it?
o We can do comparison for certain measures ( revenue.,sales etc.. ) for current year vs previous year, we can do for month or week and day also
o Identify the time periods need to be compared and then period table keys to the previous time period.
o The period table needs to contain a column that will contain “Year Ago” information.
o The fact tables needs to have year ago totals.
o To use the “Time series wizard”. After creating your business model right click the business model and click on “Time Series Wizard”.
o The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
o The Time Series Wizard prompts you to select the period table used for the comparison measures
o Select the column in the period table that provides the key to the comparison period. This column would be the column containing “Year Ago” information in the period table.
o Select the measures you want to compare and then Select the calculations you want to generate. For ex: Measure: Total Dollars and calculations are Change and Percent change.
o Once the Time series wizard is run the output will be:
a) Aliases for the fact tables (in the physical layer)
b) Joins between period table and alias fact tables
c) Comparison measures
d) Logical table sources
o In the General tab of the Logical table source etc you can find “Generated by Time Series Wizard” in the description section
o Then you can add these comparision measures to the presentation layer for your reports.
o Ex: Total sales of current qtr vs previous qtr vs same qtr year ago
Did you create any new logical column in BMM layer, how?
o Yes. We can create new logical column in BMM layer.
o Example: Right click on fact table -new lgical column-give name for new logical column like Total cost.
o Now in fact table source,we have one option column mapping, in that we can do all calculation for that new column.

Can you use physical join in BMM layer?
o yes we can use physical join in BMM layer.when there is SCD type 2 we need complex join in BMM layer.

Can you use outer join in BMM layer?
o yes we can.When we are doing complex join in BMM layer ,there is one option type,outer join is there.
What are other ways of improving summary query reports other than Aggregate Navigation and Cache Management
” Indexes
” Join algorithm
” Mat/view query rewrite
” Web proper report design its optimal by making sure that it is not getting any addition column or rows

What is level-base matrics?
o Leval-base matrics means, having a measure pinned at a certain level of the dimension. For Example, if you have a measure called “Dollars”, you can create a “Level Based Measure” called “Yearly Dollars” which (you guessed it) is Dollars for a Year. This measure will always return the value for the year even if you drill down to a lower level like quarter, month… etc. To create a level based measure, create a new logical column based on the original measure (like Dollars in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Year in Time Dim
o A LBM is a metric that is defined for a specific level or intersection of levels.
o Monthly Total Sales or Quarterly Sales are the examples.
o You can compare monthly sales with quarterly sales. You can compare customer orders this quarter to orders this year

What is logging level?Where can you set logging levels?
o You can enable logging level for individual users; you cannot configure a logging level for a group.
o Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging
o level of 1 or 2. These two levels are designed for use by OBIEE Analytics Server administrators.
o Set Logging Level
1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user.s user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging Level field
What is variable in sieble?
o You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a chainging data environment.The Administration Tool includes a Variable Manager for defining variables

What is system variable and non system variable?
o System variables
o System variables are session variables that the OBIEE Analytics Server and OBIEE Analytics Web use for specific purposes. System variables have reserved names, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for nonsystem session variables).
o When using these variables in the Web,preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.
o Nonsystem variables.
o A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the user.s sales region. You could then set a security filter for all members of a group that would allow them to see only data pertinent to their region.
o When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.SalesRegion.

What are different types of variables? Explain each.
o There are two classes of variables:
1. Repository variables
2. Session variables.
Repository variables.
A repository variable has a single value at any point in time. There are two types of repository variables:
static : This value persists, and does not change until a OBIEE Analytics Server administrator decides to change it.
dynamic:The values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization block or use a preexisting one that contains a SQL query. You will also set up a schedule that the OBIEE Analytics Server will follow to execute the query and periodically refresh the value of the variable.
Session Variables
Session variables are created and assigned a value when each user logs on. There are two types of session variables:
1.system
2.nonsystem.

What are the cache management? Name all of them and their uses. For Event polling table do u need the table in your physical layer?
o Monitoring and managing the cashe is cache management.There are three ways to do that.
o Disable caching for the system.(INI NQ config file), Cashe persistence time for specified physical tables and Setting event polling table.
o Disable caching for the system.(INI NQ config file :
You can disable caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI file and restarting the OBIEE Analytics Server. Disabling caching stops all new cache entries and stops any new queries from using the existing cache. Disabling caching allows you to enable it at a later time without losing any entries already stored in the cache.
o Cashe persistence time for specified physical tables :
You can specify a cachable attribute for each physical table; that is, if queries involving the specified table can be added to the cache to answer future queries. To enable caching for a particular physical table, select the table in the Physical layer of the Administration Tool and select the option Make table cachable in the General tab of the Physical Table properties dialog box. You can also use the Cache Persistence Time settings to specify how long the entries for this table should persist in the query cache. This is useful for OLTP data sources and other data sources that are updated frequently, potentially down to every few seconds.
o Setting event polling table :
OBIEE Analytics Server event polling tables store information about updates in the underlying databases. An application (such as an application that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Analytics server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables.
o For event polling table ,It is a standalone table and doesn’t require to be joined with other tables in the physical layer
What is Authentication? How many types of authentication.
o Authentication is the process by which a system verifies, through the use of a user ID and password, that a user has the necessary permissions and authorizations to log in and access data. The OBIEE Analytics Server authenticates each connection request it receives.
” Operaing system autentication
” External table authentication
” Database authentication
” LDAP authentication

What is object level security?
o There are two types of object level security: Repository level and Web level
o Repository level : In presention layar we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
o web level:thisprovides security for objects stored in the OBIEE anlytics web catlog,such as dashboards,dashboards pages,folder,and reportsyou can only view the objects for which you are authorized. For example,a mid level manager may not be granted access to a dashboard containing summary information for an entire department.
What is data level security?
o This controls the type an amount of data that you can see in a report.When multiple users run the same report the results that are returned to each depend on their access rights and roles in the organization.For example a sales vice president sees results for alll regions, while a sales representative for a particular region sees onlu datafor that region.
What is the difference between Data Level Security and Object Level Security?
o Data level security controls the type and amount of data that you can see in a reports.Objectlevel security provides security for objects stored in the OBIEE analytics web catlog, like dashboards,dashboards pages,folder,and reports.
How do you implement security using External Tables and LDAP?
o Instead of storing user IDs and passwords in a OBIEE Analytics Server repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes. The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for OBIEE Analytics Web users. The table could also contain the names of specific database catalogs or schemas to use for each user when querying data
o Instead of storing user IDs and passwords in a Obiee Analytics Server repository, you can have the Obiee Analytics Server pass the user ID and password entered by the user to an LDAP(Lightweight Directory Access Protocol ) server for authentication. The server uses clear text passwords in LDAP authentication. Make sure your LDAP servers are set up to allow this.
If you have 2 fact and you want to do report on one with quarter level and the other with month level how do you do that with just one time dimension?
o Using levelbase matrics.
Did you work on a stand alone Obiee system or was it integrated to other platforms?
o Deploying the Obiee analytics platform without other Obiee applications is called Obiee analytics Stand -Alone .If your deployment includes other OBIEE Analytics Application it called integrated analytics -You can say Stand-Alone OBIEE analytics
How to sort columns in rpd and web?
o Sorting on web column, sort in the rpd its sort order column
If you want to create new logical column where will you create (in repository or dashboard) why?
o I will create new logical column in repository.because if it is in repository,you can use for any report.If you create new logical column in dashboard then it is going to affect on those reports ,which are on that dashboard.you can not use that new logical column for other dashboard(or request)
What is complex join, and where it is used?
o we can join dimention table and fact table in BMM layer using complex join.when there is SCD type 2 we have to use complex join in Bmm layer.
If you have dimension table like customer, item, time and fact table like sale and if you want to find out how often a customer comes to store and buys a particular item, what will you do?
o write a query as “SELECT customer_name, item_name, sale_date, sum(qty) FROM customer_dim a, item_dim b, time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key = b.item_key AND d.time_key = c.time_key GROUP BY customer_name, item_name, sale_date”
You worked on standalone or integrated system?
o Standalone.
If you want to limit the users by the certain region to access only certain data, what would you do?
o using data level security.
o OBIEE Analytics Administrator: go to Manage -> Security in left hand pane u will find the user, groups, LDAP server, Hierarchy
What you can do is select the user and right click and go to properties, you will find two tabs named as users and logon, go to user tab and click at permission button in front of user name you have selected as soon as u click at permission you will get a new window with user group permission having three tabs named as general ,query limits and filter and you can specify your condition at filter tab, in which you can select presentation table ,presentation columns ,logical table and logical columns where you can apply the condition according to your requirement for the selected user or groups.
If there are 100 users accessing data, and you want to know the logging details of all the users, where can you find that?
o To set a user.s logging level
1. In the Administration Tool, select Manage > Security.
The Security Manager dialog box appears.
2. Double-click the user.s user ID. The User dialog box appears.
3. Set the logging level by clicking the Up or Down arrows next to the Logging Level field
How do implement event polling table?
o OBIEE Analytics Server event polling tables store information about updates in the underlying databases. An application (such as an application that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Analytics server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables.
” Can you migrate the presentation layer only to different server
o No we can’t do only presentation layer. And ask him for more information and use one of the above answers
o Create a ODBC connection in the different serve and access the layer.
o Copy the Rpd and migrate it to other server
Define pipeline. Did you use it in your projects?
o Yes, pipelines are the stages in a particular transaction. Assessment, finance etc.
How do you create filter on repository?
o Where condition on content tab.
How do you work in a multi user environment? What are the steps?
o Create a shared directory on the network for Multi-user Development (MUD).
o Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to point to the above directory.
o Define projects within the rpd to allow multiple users to develop within their subject area or Facts.
o Save and move the rpd to the shared directory setup in point 1.
o When users work in the MUD mode, they open the admin tool and start with
o MUD ->Checkout to checkout the project they need to work on (not use the File open as you would usually do).
o After completely the development, user checkin the changes back to the network and merge the changes.
Where are passwords for userid? Ldap,external table authentication stored respectively?
o passwords for userid are in OBIEE analytics server repository Ldap authentication in Ldap server external database in a table in external database
Can you bypass OBIEE analytics server security?if so how?
o yes you can by-pass by setting authententication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the 2 places
” Where can you add new groups and set permissions?
o you can add groups by going to manage>security>add new groups> You can give permissions to a group for query limitation and filter conditions.
what are the things you can do in the BMM layer?
o Aggrigation navigation,level base matrics,time series wizard,create new logical column,comlex join.
what is Ragged hierarchy? and how do u manage it
o Ragged Hierarchy is one of the different kinds of hierarchy.
o A hierarchy in which each level has a consistent meaning, but the branches have inconsistent depths because at least one member attribute in a branch level is unpopulated. A ragged hierarchy can represent a geographic hierarchy in which the meaning of each level such as city or country is used consistently, but the depth of the hierarchy varies.
o For example, a geographic hierarchy that has Continent, Country, Province/State, and City levels defined. One branch has North America as the Continent, United States as the Country, California as the Province or State, and San Francisco as the City. However, the hierarchy becomes ragged when one member does not have an entry at all of the levels. For example, another branch has Europe as the Continent, Greece as the Country, and Athens as the City, but has no entry for the Province or State level because this level is not applicable to Greece for the business model in this example. In this example, the Greece and United States branches descend to different depths, creating a ragged hierarchy.

What is the difference between Single Logical Table Source and Multiple Logical Table Sources?
o If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
o If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
o Ex: Usually Fact table has Multiple LTS’, for which sources will be coming from different Physical tables.
” Can you let me know how many aggregate tables you have in your project? On what basis have you created them?
o As per resume justification document
How do you bring/relate the aggregate tables into the OBIEE analytics Logical layer?
o One way of bringing the Aggregate Tables into the BMM layer is by bringing them as Logical Table sources for the corresponding Fact table.
o This is done by dragging and dropping the aggregate table into the corresponding fact table. After doing that establish the column mappings and the set the aggregation levels.
How do you know which report is hitting which table, either the fact table or the aggregate table?
o After running the report, go to “Administration” tab and go to click on “Manage Sessions”. There you can find the queries that are run and in the “View Log” option in the Session Management you can find which report is hitting which table.
Suppose I have report which is running for about 3 minutes typically. What is the first step you take to improve the performance of the query?
o Find the sql query of the report in Admin->manage Session-> run the sql query on toad ->read the explain plan output ->modify the SQL based on the explain plan output
Suppose you have a report which has the option of running on aggregate table. How does the tool know to hit the Aggregate table and for that what the steps you follow to configure them?
o Explain the process of Aggregate navigation
” Have you heard of Implicit Facts? If, so what are they?
o An implicit fact column is a column that will be added to a query when it contains columns from two or more dimension tables and no measures. You will not see the column in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.
o For example, there might be many star schemas in the database that have the Campaign dimension and the Customer dimension, such as the following stars:
” Campaign History star. Stores customers targeted in campaign.
” Campaign Response star. Stores customer responses to a campaign.
” Order star. Stores customers who placed orders as a result of a campaign.
In this example, because Campaign and Customer information might appear in many segmentation catalogs, users selecting to count customers from the targeted campaigns catalog would be expecting to count customers that have been targeted in specific campaigns.
” To make sure that the join relationship between Customers and Campaigns is through the campaign history fact table, a campaign history implicit fact needs to be specified in Campaign History segmentation catalog. The following guidelines should be followed in creating
” segmentation catalogs:
” Each segmentation catalog should be created so that all columns come from only one physical star.
” Because the Marketing module user interface has special features that allow users to specify their aggregations, level-based measures typically should not be exposed to segmentation users in a segmentation catalog.
What is aggregate navigation? How do you configure the Aggregate tables in OBIEE Analytics?
o Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems.
o If you are writing SQL queries or using a tool that only understands what physical tables exist (and not their meaning), taking advantage of aggregate tables and putting them to good use becomes more difficult as the number of aggregate tables increases. The aggregate navigation capability of the OBIEE Analytics Server, however, allows queries to use the information stored in aggregate tables automatically, without query authors or query tools having to specify aggregate tables in their queries. The OBIEE Analytics Server allows you to concentrate on asking the right business question; the server decides which tables provide the fastest answers.
(Assume you are in BMM layer) We have 4 dimension tables, in that, 2 tables need to have hierarchy, and then in such a case is it mandatory to create hierarchies for all the dimension tables?
o No, its not mandatory to define hierarchies to other Dimension tables.
Can you have multiple data sources in OBIEE Analytics?
o Yes.

How do you deal with case statement and expressions in OBIEE analytics?
o use expression builder to create case when…then.. end statement
Do you know about Initialization Blocks? Can you give me an example where you used them?
o Init blocks are used for instantiating a session when a user logs in.
o To create dynamic variable you have to create IB to write sql statement.
What is query repository tool?
o It is utility of Seibel/OBIEE Admin tool
o allows you to examine the repository metadata tool
o for example: search for objects based on name,type.
o Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer
What is JDK and why do we need it?
o Java Development Kit (JDK), A software package that contains the minimal set of tools needed to write, compile, debug, and run Java applets.
Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?
o an opaque view is a physical layer table that consists of select statement. an opaque view should be used only if there is no other solution.

Can you migrate the presentation layer to a different server?
o No we have to migrate the whole web & rpd files
How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?
o Dimension tables contain descriptions that data analysts use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and year values. Every table contains a primary key that consists of one or more columns; each row in a table is uniquely identified by its primary-key value or values
Why do we have multiple LTS in BMM layer?What is the purpose?
o to improve the performance and query response time.

What is the full form of rpd?
o repository definition file means it will contains the metadata.

How do i disable cache for only 2 particular tables?
o in the physical layer, right click on the table there we will have the option which says cacheable
How do you split a table in the rpd given the condition? ( the condition given was Broker and customer in the same table) Split Broker and customer.
o we need to make an alias table in the physical layer.
What type of protocol did you use in SAS?
o TCP/IP


OBIEE Performance Improvements Methods.

While working on OBIEE every one need some tips to increase performance, I searched the net but not found helpful tips. Hence I thought putting some of the points together that will be helpful for you.


Following are the few points which improve the performance of OBIEE.
Alias Tables
Using Aggregate Navigation
Using Cache
Connection Pool
Turning Off Log Level
Using Database Hints
Using Where Clause
1.)  Alias Tables
An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a Physical table, and inherits all its column definitions and some properties from the Physical table. Alias Tables can be an important part of designing a physical layer
OBIEE doesn’t support the self-join, so we can use alias table to implement self-join in OBIEE.
The following is a list of the main reasons to create an alias table:
To reuse an existing table more than once in your physical layer (without having to import it several times)
To set up multiple alias tables, each with different keys, names, or joins
To help you design sophisticated star or snowflake structures in the business model layer.
To create Alias table in Physical Layer, right click on Table -> New Object -> Alias, select source table if required.

ODI Interview Questions and Answers

1) What is ODI tool and how it works compare to other DWH Tools?
Ans:  ODI is DWH tool, it is working ELT architecture and other tools working as a ETL architecture.
We can find more features if we are using ELT architecture.

2) What is ELT and ETL Architecture and what are the differences?
Ans:
ETL: (Extraction , Transformations & Loading)
--------------------------------------------
ETL architecture is required separate staging server for transformations.
All ETL tools first it will extract data from Source systems, and loading into staging server and that staging available at ETL server level, All transformations
we are doing at ETL server level  then after finishing transformations we will load data into target server.
In ETL Architecture there are three servers,: Source server , Staging(i.e., ETL Server) server and Target servers

3)Disadvantages for ETL Architecture:
-----------------------------------
If we are mainting ETL architecture that staging server different from target. while doing transformations we have to check parent data while loading child data,
that parent data available in target system. for this validations we have to connect target server for checking parent data. whenever we are doing data validations
we have to check at target server so this time data comparision will take more processing time and it is burdden on server and the same is may be using different
systems it will impact other systems as well.

example:
--------
Orders -- Parent Table
Order lines-  Child table.

While load order lines data before loading into target will check orders table having those orders in target server. if not available
we will throug error parent dont found and we will load only child data. for this we are connecting different severs like staging and target.
this data validations will considar both staging and target servers. it will impact processing time and server impact as well like CPU processing
and RAM utilization also.

4)ELT: ( Extraction Loading and Transformations)
---------------------------------------------
IN ODI we are using ELT architecture, this architecture first it will extract data from source system and it will load into directly target system
in target we are maintaing  staging , in staging we will all transformations and we will load into target tables. In ELT architecture all transformations
happening at target server with in target server we are maintaing staging and target schemas seperatly. While doing transformations we are doing with in the
server and different schemas or we can maintain both in one schema. this transformations happening at target server only it wont impact on processing time
and data validations and other transformations wont take much time and it wont impact on other servers as well.

example:
--------
Orders-  Parent table
Order lines -- Child table
while loading order lines data first we will load into staging then we will validate those orders exists or not in orders parent table.
if orders does not exist orders parent table we will throw error like parent not found. in this ELT architecture both staging and target tables are in
same server that is target server. itwont impact on processing time and CPU and Memory utilization.


***************************

ELT Architecture major advantage is Staging we can maintain within target server we dont need seperate staging server. we can maintain staging
in target server itself. ODI staging supporting any Native SQL supporting Database.
OR we can maintain this staging not only target any other database servers like Source or target or other than source or target but that should
Database not ELT server.

This option we have at Interface level in Definition tab. STAGING AREA DIFFFERENT FROM TARGET, There we will select logical schema, default Target database schema we will select
if we want other than target we can select option STAGING OTHER THAN TARGET option and we can select that database logical schema.


5)ODI Other Features compare to Other ETL Tools:
----------------------------------------------
1) ELT Architecture and staging we are maintain at Database level.
2) Inbuilt Scheduling available
3) Easy Code moving from one environment to other environment. its everything files as xml.
4) Inbuilt version controlling.
5) Supporting Public Web services  and middleware features using Weblogic server or Websphere.
6) Web based accessing and scheduling and monitoring entire ODI using ODI Console (this we will get if we are using Middleware).
7) Programming Languages Integration like we can run direct Programming languages and we can call shell scripting as well using OS command
and other ODI Tools we can utilize.
8) Sending mail alter for individual  programs based on status like after finishing ur interfaces or programs we can make alert if success
or failure messages.

6)What is Repository and Types of Repositories?

Ans: A repository is a metadata matianance database user or schema. in this schema we will maintain all odi components information like
topology, security, designer and operator components information.

There are two types of repositories.
1) Master Repository
2) Work Repository

7)What is Master Repository?
Ans:  Master Repository will maintain or will store all Topology and Security components information. and Master will contain Work repository
information as well.

We can maintain only one master repository and multiple works repositories like Development and Execution type of work repositories.

8)What is Work Repository?
Ans: Work repositories will maintain all Designer and Operator components information. there are two types of work repositories.
1) Development
2) Execution
For Development work repository we can use for Development, Testing , UAT and other environments except production environment.
if we are using Development type work repository we can access Designer and Operator components.

For Execution work repository we will use for production environment. here we can access only Operator component.
In production we dont need Designer bcz we are not editing any mappings or interface we are only excuting. in Execution work repository
we will use only SCENARIOS AND LOAD PLANS, this scenarios are not editable.
If we want to edit we have to connect development type repository and we can edit any mapping and we can regenerate scenarios and we can move into
execution work repository.

Note:  In production we can edit load plans that load plans contains only scenarios and variables. But SCENARIOS cannoted edited it is ready
onlyobejcts we can use for only execution.

Note: If we are using Development type work repository as a production environment we can edit scenarios?
No, We cannot edit scenarios but we can edit interfaces and we can regenerate scenarios but it is not suggestible in production,
we have to edit in development and testing should properly and moving into production valid scenarios or interfaces.


9)What IS Security in ODI how we will maintain?
Ans:  In ODI security we are maintaing at SECURITY Component and we can create users in Security using SUPERVISOR Default adminstrator user.
and we can assign privileges through profiles.

10)What is Profile and Types of Profiles?
Ans:  A Profile is a set of objective privileges and we can assign these profiles to users, that user can get all privileges whatever profile.
There two types of profiles.
1) Generic Profile: Generic profiles having default granted privileges.
2) Non-Generic Profile: Non-Generic Profiles dont have default grant privileges, we can give based on our requirements i.e., customised profiles.
Can we create new profile?
YEs, We can create new profile and we can assign object privileges based on our requirements.

11)What is account expire option in Users?
Ans: This option we can use for temporary users, if they need only 1 or 6months access like contracts that time we will give only particular
time access after that they cannot access , they need request administrator for extending user access.

12)Topology:
---------
This topology we are using for to maintain all source and target applications , databases server configurations or connections information.

13)What is Dataserver?
Dataserver is main object to connect your source or target database servers connection information.
Here we will provide Driver details like Host, Port no, SID service name with Credentials like User name and password.

14)What is Physical Schema and Types of Physical Schemas?
Physical schema is your main database or application accessing schema or User.
There two types of Physical Schemas.
1) Data Schema (main schema) : Data Schema is your main database tables having schema
2) Work Schema: Work Schema is your staging tables having schema that is C$, I$,E$ and other staging tables.
We can use both Data schema and work schema same or we can use different schemas for data schema and work schema.


15)What is Context?
----------------
Context is mediator between Physical and logical architecture and this contexts we are using for to maintain Environments or location servers.
each context is assign to one physical schema based logical schema combination.
example:  DEV, TEST and PRODUCTION environments.

16)what is difference between Contexts and Repositories?
Ans: Repositories will maintain complete components information like  DESIGNER AND Operator
Then whatever we user using inside server in topology we will divide based on evironments and all servers using contexts.

Because for all environemnts we will use only one topology, and multiple work repositories, this context will divide all invironments
servers and it will be usable for all repositories.
example :  DEV , TEST and Production these all work repositories will connect only only topology but how this environemnts will identify servers
for Development and testing and production servers based Context only. context will divide servers based on environemnts, it will used seperate
for development, for testing for production in different work repositories.

Note: All environemnt servers we will configure in Topology, but while accessing we will use context for dividing these servers for
seperate work repositories. This repositories will identify physical serversthrogh context only. if development repository that will connect
development context, if production repository this will connect production context.


17)What is Logical Schema?
Logical schema is alias for your physical schemas and it will connect to your physical schemas throgh contexts.
In Designer  developer can access only logical schemas he con not access physical schemas, developer can access logical schemas and context combination he
can connect physical schemas.

18)Designer:
---------
Designer component we are using for complete development area. in design we will work for development like creating interfaces, packages, procedures,
variables, sequences and other objects, after finishing development we will generate schenarios and we can move this all scenarios  in to production.

19)What is Scenario?
A scenario is a compiled or executable object and it is not editable, in odi production environemnt we will use only scenarios for mataining
security and hiding. because this scenarios is not readeable  and not editable. this scenarios only schedules or executable.

20)What is Interface ?
-------------------
An interface we are using to load data from source to target , here we can apply all the transformations and validations and aggregations.
In odi 11g interface we can use only one target in one interface, if we want load data into multiple target tables we have to go for multiple
interfaces and use IKM Multiple table insert knowledge module.

In ODI 12c Interfaces we are calling Mappings and we can use multiple target tables in one mapping.

21)What is Yellow Interface?
--------------------------
Yellow interface we are using for without target table and we can create dynamically target table  if we dont have target table.
for this we need to enable CREATE_TARGET_TABLE option=> TRUE at IKM level.
This yellow interface we can use for source table as well.


This yellow interfaces we are calling Reusable mappings in 12c.

22)what is Lookup:
----------------
In odi lookup we using for to check parent data before loading child data. but in backgroup it will create leftouter join.
or sub query join.
lookup means looking into up table for data if data available in up table it will considarother wise it will ignore..

Compare to normal left outer join LOOKUP will give best performance because it is happening at odi level.

23)What is Filter?
---------------
Filters we are using for to filter data from source table that we can apply source or staging.


24)What is Optimization Context:
----------------------------
This context we are using at interface development time only, while execution we need to select in execution window that context is your
main context.  optimization context is usable only for interface development only not for execution.

25)What is Simulation option at interface?
---------------------------------------
This option we are using for to show complete interface process like LKM and IKM and CKM process like creating all $tables and
inserting into target table queries everything it will give a report.

Note:  Simulation will give only report it wont run in session or program.

26)What is CDC?
Ans:
CDC change data capture we are using for to extract changed data from source. if source accepting Triggers on source table we will use this CDC
feature if source system they are not allowing to create a triggers we can't use this CDC feature we will go for timestamp based filtering.

Note:  If source table having any timestamp or modified data column we can filtering if source table dont have timestamp or modified date column we have to
use CDC feature (journalization concept) if they are not allowing to create triggers on source table we have to select everying and we need to maintain seperate
staging table for checking data from source and our staging table data.

Note: If source table dont have timestamp column and they are not allowing to create CDc (triggers) then we need to create one staging table STG_ORDERS
with timestamp column first we will load full data from source table (SOURCE_ORDERS) table to our stagning table, but we will maintain this
staging data permanently and we will create one more interface to load this STG_ORDERS table to TARGET_ORDERS table based on timestamp column.
from next time onwards while select data from SOURCE_ORDERS table we will check STG_ORDERS table if STG_ORDERS table dont  have source data
that data only we will select and we will insert.

SELECT * FROM SOURCE_ORDERS  WHERE NOT EXITS ( SELECT 'X' FROM STG_ORDERS);

for this requirement we need two inerfaces one for SOURCE_ORDERS table STG_ORDERS table and another interface for
STG_ORDERS table to TARGET_ORDERS table.

STG_ORDERS to TARGET_ORDERS table we can use timestamp filter condition on STG_ORDERS and it will get selected only modified data.
we cannot delete data from STG_ORDERS table.

27)What is SCD?
------------
SCD Slowly changing dimension we are using for to load changing dimension information data.
where are three types of SCD'same
1) SCD TYPE1:  No history, we will all columns SCD property as OVERWRITE ON CHANGE in ODI.
   SCD TYPE1: we can use IKM Oracle Incremental update KM or we can IKM Slowly Changing Dimension both we can use.
2) SCD TYPE2:  Here we can maintain no of times history, so we need to maintain below SCD properties in SCD TYPE 2 table.
1) ADD Row On CHANGE: this property will add a row whenever we are receiving changed data to maintain history.
2) STARTING_ TIME STAMP:  This will show record starting date and time
3) Ending Time stamp:  this will show record ending date and time
4) RECORD FLAG:  this will show active record if it is Y and if it is N for old rows or history rows.
3) SCD TYPE 3:  As of now ODI is not supporting we have to customise our IKM Slowly Changing Dimension.
it will maintain only one time history in different column like , CURRENT SALARY and PREVIOUS SALARY two columns.

28)What is Package?
-----------------
Package we are using for making work flow for our developed objects like interfaces, procedures and variables.
we can run multiple objects in package its may be sequential or parallel.

If it is directs objects like procedures, interfaces, variables and packages we can run only Sequential run we can’t run parallel.
if we want run parallel we have to use Scenarios for interfaces, procedures and packages and variables.
In scenario’s there is one option SYNCHRONOUS or ASYNCHRONOUS MODE.
SYNCHRONOUS:  Sequential run   ********************
ASYNCHRONOUS:Parallel run.  **************************

29)How to run Parallel in Package?
We have to only schenarios and set Asynchronous mode in package opations.
if it is direct object we can run only sequential.

30)What is Load Plan:
------------------
Load plan we can run only scenarios and variables but here we can make sequential run, parllel run and condition based and hierarichal run.
like one parllel inside parllel one sequential and inside sequential parllel or sequential run we can use this we can say hierarichal run.

31)Difference between Package and Load Plan? **********************************************
-----------------------------------------
Package we can direct objects like interfaces, procedures and packages and also we can scenarios for those objects.
and we can all  ODI Tools means,  SFTP GET SFTP PUT, FILE Copy, file move ... all odi tools we can use.

In load plan we can run only scenarios and we cant run direct objects and we cant use odi tools like SFTP Put, get and file related all odi tools.
we can use only scenarios for interfaces, procedures,  packages and variables.

In package we can use variables 4 types like  Declare variable, Set variable, evaluate variable and refresh variable.

But in load plan we can use only one type variable.

Direct package we cant run in production, we have generate scenario,

But Load plan direct we can run in product , there is no scenario for loadplans, load plan is direct executable or schedulable.


32)What is Procedure?
-------------------
ODI odi procedure we can use for technology specific code execution. we can any any technology direct code execution using this procedure.
command on source we will use for selection and command on target we will use all operations like DML, DDL and other operations.

33)What is Sequence and type of sequences?
---------------------------------------
Sequence we can use to generate sequence of values like number insertintion into tables.
there are three types of sequences.
1) Standard sequence: we can use increment and it will start with 0
2) Specific sequence:  we can use increment and it will start with our specific value like 5000 , 500000 .... any our own value.
3) Native Sequence:  we can use this our direct Database sequences integration.

34)What is User Functions?
-----------------------
User functions we are using for our won logic or code implementation and we can reuse in any programs or interfaces passing parameters.


35)What is Markers?
-----------------
Markers we are using for to show development progress or status or Priority of objects like high priority interface or low interfaces and smiles as well.

36)What is Difference between Global objects and Project objects?
--------------------------------------------------------------
Global objects we can use all projects and Project objects we can use only with in that project only. if we want to use project objects into other projects
wecant use , we have to create in global and use GLOBAL keywork before object calling.


37)What is SolutionS?
-------------------
Solutions we are using for to create version for entire project or individual objects.  This is we are using for mainly version controlling.

38)How to move your interfaces or other objects from Development to PRODUCTION or one work repository to another work repository?

Ans:  USing Smart export first we will export all required objects and we will save as .xml file then
we will login into different work repository then we  will use smart import feature and select exported xml file into new work repository.

Note:  While moving one work repository to another work repository ID's should not be same and both Repository versions should be same
if work repositories versions are different we have to migrate those entire work repository into new work repository version using ODI Migration utility tool.
If both work repository ID's are same we have regenerate or change new id in new work repository.


39)What is Agent?
---------------
An agent is a runtime component , this will come into the picture whenever we are running a program it will take request from client and it will
create session with assign one unique id  and chekcing source and target server (pinging source and target servers ) if servers are available it will transfer
our source to server and it will result and it will status success in operator and this complere log information it will store into work repository SNP_SESS_*LOG
tables. and all sessions it will maintain in SNP_SESSION table.

40)What are the types of Agents?
---------------------------------
There are two types of agents in ODI.
1) STand Alone: This will work with in odi level without Middleware
2) J2EE Agent: this will work within middleware and we will get additional featueslike , load balancing, highavailable and public services features.