I am here posting some of the common questions we are coming through an Interview with answers. I hope it will be helpful.
What is the previous name of OBIEE and in which version you are currently working for?
Siebel Analytics, Currently working with OBIEE 11.1.1.6.0
Define repository in terms of Siebel Analytics?
Repository stores the Meta data information. Siebel repository is a file system, extension of the repository file. rpd. which called as META DATA REPOSITORY
With Siebel Analytics Server, all the rules needed for security, data modeling, aggregate navigation, caching, and connectivity is stored in metadata repositories.
Each metadata repository can store multiple business models. Siebel Analytics Server can access multiple repositories.
What is the end to end life cycle of Siebel Analytics?
Siebel 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.
How does OBIEE Architecture works? Explain the three layers. How do you import sources?
There are five parts of Siebel Architecture.
1. Clients
2. Oracle BI Presentation Server
3. Oracle BI Server
4. Application Server (OC4J)
5. Data Source
Metadata that represents the analytical Model is created using OBIEE Administration tool.
Repository divided into three layers
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 Oracle BI 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?
In the BMM layer, create one logical table (fact) and add the 3 fact table as logical table source (LTS)
What is connection pool and how many connection pools did you have in your last project?
Connection pool is needed for every physical database.
It contains information about the connection to the database, not the database itself.
It Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass through.
We can have multiple connection pools for each group to avoid waiting.
Purpose of Alias Tables
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.
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
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 Schema.
How do you define the relationship between facts and dimensions in BMM layer?
Using complex join, we can define relationship between facts and dimensions in BMM layer.
What is time series wizard? When and how do you use it?
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.
Identify the time periods need to be compared and then period table keys to the previous time period.
The period table needs to contain a column that will contain “Year Ago” information.
The fact tables needs to have year ago totals.
To use the “Time series wizard”, After creating your business model right click the business model and click on “Time Series Wizard”.
The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
The Time Series Wizard prompts you to select the period table used for the comparison measures
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.
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.
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
In the General tab of the Logical table source etc., you can find “Generated by Time Series Wizard” in the description section
Then you can add these comparison measures to the presentation layer for your reports.
Ex: Total sales of current Quarter vs. Previous Quarter vs. same quarter year ago
Did you create any new logical column in BMM layer, how?
Yes. We can create new logical column in BMM layer.
Example: Right click on fact table -new logical column-give name for new logical column like Total Revenue.
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?
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?
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 based metrics?
Level-based metrics 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
A LBM is a metric that is defined for a specific level or intersection of levels.
Monthly Total Sales or Quarterly Sales are the examples.
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?
You can enable logging level for individual users; you cannot configure a logging level for a group.
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
level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.
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 OBIEE?
You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. The Administration Tool includes a Variable Manager for defining variables
What is system variable and non-system variable?
System variables:
System variables are session variables that the Siebel Analytics Server and Siebel 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 non-system session variables). 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.
Non-system variables:
A common use for non-system session variables is setting user filters. For example, you could define a non-system 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.
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.
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 Siebel 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 Siebel 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.non-system.
What is the cache management? Name all of them and their uses. For Event polling table do you need the table in your physical layer?
Monitoring and managing the cashe is cache management.There are three ways to do that.
Disable caching for the system.(NSQconfig INI file), Cache persistence time for specified physical tables and Setting event polling table.
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 Siebel 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.
Cache persistence time for specified physical tables:
You can specify a cacheable 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 cacheable 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.
Setting event polling table:
Siebel 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.
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.
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 Siebel Analytics Server authenticates each connection request it receives.
Operating system authentication
External table authentication
Database authentication
LDAP authentication
What is object level security?
There are two types of object level security:
What is data level security?
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 all regions, while a sales representative for a particular region sees only data for that region.
What is the difference between Data Level Security and Object Level Security?
Data level security controls the type and amount of data that you can see in reports. Object level security provides security for objects stored in the OBIEE web catalog, like dashboards, dashboards pages, folder, and reports.
What is the previous name of OBIEE and in which version you are currently working for?
Siebel Analytics, Currently working with OBIEE 11.1.1.6.0
Define repository in terms of Siebel Analytics?
Repository stores the Meta data information. Siebel repository is a file system, extension of the repository file. rpd. which called as META DATA REPOSITORY
With Siebel Analytics Server, all the rules needed for security, data modeling, aggregate navigation, caching, and connectivity is stored in metadata repositories.
Each metadata repository can store multiple business models. Siebel Analytics Server can access multiple repositories.
What is the end to end life cycle of Siebel Analytics?
Siebel 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.
How does OBIEE Architecture works? Explain the three layers. How do you import sources?
There are five parts of Siebel Architecture.
1. Clients
2. Oracle BI Presentation Server
3. Oracle BI Server
4. Application Server (OC4J)
5. Data Source
Metadata that represents the analytical Model is created using OBIEE Administration tool.
Repository divided into three layers
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 Oracle BI 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?
In the BMM layer, create one logical table (fact) and add the 3 fact table as logical table source (LTS)
What is connection pool and how many connection pools did you have in your last project?
Connection pool is needed for every physical database.
It contains information about the connection to the database, not the database itself.
It Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass through.
We can have multiple connection pools for each group to avoid waiting.
Purpose of Alias Tables
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.
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
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 Schema.
How do you define the relationship between facts and dimensions in BMM layer?
Using complex join, we can define relationship between facts and dimensions in BMM layer.
What is time series wizard? When and how do you use it?
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.
Identify the time periods need to be compared and then period table keys to the previous time period.
The period table needs to contain a column that will contain “Year Ago” information.
The fact tables needs to have year ago totals.
To use the “Time series wizard”, After creating your business model right click the business model and click on “Time Series Wizard”.
The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
The Time Series Wizard prompts you to select the period table used for the comparison measures
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.
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.
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
In the General tab of the Logical table source etc., you can find “Generated by Time Series Wizard” in the description section
Then you can add these comparison measures to the presentation layer for your reports.
Ex: Total sales of current Quarter vs. Previous Quarter vs. same quarter year ago
Did you create any new logical column in BMM layer, how?
Yes. We can create new logical column in BMM layer.
Example: Right click on fact table -new logical column-give name for new logical column like Total Revenue.
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?
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?
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 based metrics?
Level-based metrics 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
A LBM is a metric that is defined for a specific level or intersection of levels.
Monthly Total Sales or Quarterly Sales are the examples.
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?
You can enable logging level for individual users; you cannot configure a logging level for a group.
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
level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.
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 OBIEE?
You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. The Administration Tool includes a Variable Manager for defining variables
What is system variable and non-system variable?
System variables:
System variables are session variables that the Siebel Analytics Server and Siebel 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 non-system session variables). 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.
Non-system variables:
A common use for non-system session variables is setting user filters. For example, you could define a non-system 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.
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.
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 Siebel 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 Siebel 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.non-system.
What is the cache management? Name all of them and their uses. For Event polling table do you need the table in your physical layer?
Monitoring and managing the cashe is cache management.There are three ways to do that.
Disable caching for the system.(NSQconfig INI file), Cache persistence time for specified physical tables and Setting event polling table.
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 Siebel 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.
Cache persistence time for specified physical tables:
You can specify a cacheable 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 cacheable 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.
Setting event polling table:
Siebel 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.
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.
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 Siebel Analytics Server authenticates each connection request it receives.
Operating system authentication
External table authentication
Database authentication
LDAP authentication
What is object level security?
There are two types of object level security:
1.
Report Level
2.
Web Level
Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you 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.
Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you 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.
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 all regions, while a sales representative for a particular region sees only data for that region.
What is the difference between Data Level Security and Object Level Security?
Data level security controls the type and amount of data that you can see in reports. Object level security provides security for objects stored in the OBIEE web catalog, like dashboards, dashboards pages, folder, and reports.
good
ReplyDelete