Tableau interview questions

Top most important Tableau interview questions and answers by Experts:

Here is a list of  Top most important Tableau interview questions and answers by Experts.If you want to download tableau interview questions pdf free ,you can register with RVH techguru. Our experts prepared these tableau interview questions to accommodate freshers level to most experienced level technical interviews.

 If you want to become an expert in tableau ,Register for Tableau online training here.

1) Who are the founders of Tableau?
The company was founded in Mountain View, California in January, 2003 by Chris Stolte, Christian Chabot and Pat Hanrahan.
2) What is Tableau Software?
Tableau is business intelligence software that allows anyone to easily connect to data, then visualize and create interactive, sharable dashboards. It’s easy enough that any Excel user can learn it, but powerful enough to satisfy even the most complex analytical problems. Securely sharing your findings with others only takes seconds.
3) What are the five main product offered by Tableau company?
Tableau offers five main products: Tableau Desktop, Tableau Server, Tableau Online, Tableau reader and Tableau Public.
4) What is the current latest version of Tableau Desktop(as of Feb.2015)?
Current versions: Tableau Desktop version 9
5) What is data visualization?
Data visualization refers to the techniques used to communicate data or information by encoding it as visual objects (e.g. points, lines or bars) contained in graphics.
6) What is Tableau Desktop?
Tableau Desktop is based on breakthrough technology from Stanford University that lets you drag & drop to analyze data. It is great data visualization tool, you can connect to data in a few clicks, then visualize and crate interactive dashboards with a few more.

7) What is Tableau Server?
Tableau Server is browser- and mobile-based insight anyone can use. Publish dashboards with Tableau Desktop and share them throughout your organization. It’s easy to set up and even easier to run.
8) What is Tableau Public?
Tableau Public is a free service that lets anyone publish interactive data to the web. Once on the web, anyone can interact with the data, download it, or create their own visualizations of it. No programming skills are required. Be sure to look at the gallery to see some of the things people have been doing with it.
9) Why Tableau?
Whether your data is in an on-premise database, a database, a data warehouse, a cloud application or an Excel file, you can analyze it with Tableau. You can create views of your data and share it with colleagues, customers, and partners. You can use Tableau to blend it with other data. And you can keep your data up to date automatically.
10) How does Tableau perform with huge datasets?
Tableau Performance is based on Data source performance. If data source takes more time to execute a query then Tableau must wait up to that time
11) What are the differences between Tableau Software GoodData and Traditional BI (Business Objects, etc.)?
At high level there are four major differences.How to view sql which is generated by Tab
12) What are Dimensions and Facts?
Dimensions is nothing but the descriptive text columns and facts are nothing but measures (numerical values) dimension ex: Product Name, City. Facts:Sales, profit
13) What is the difference between heat map and tree map?
A heat map is a great way to compare categories using color and size. In this, you can compare two different measures. Tree map is a very powerful visualization, particularly for illustrating hierarchical (tree – structured) data and part – to – whole relationships.
14) How to view sql which is generated by Tableau Desktop?
The Tableau Desktop Log files are located in C:\Users\\My Documents\My Tableau Repository. If you have a live connection to the data source, check the log.txt and tabprotosrv.txt files. If you are using an extract, check the tdeserver.txt file. The tabprotosrv.txt file often shows detailed information about queries.
15) How will you publish and schedule workbook in tableau server?
First create a schedule for particular time and then create extract for the data source and publish the workbook for the server. Before you publish, there is a option called Scheduling and Authentication, click on that and select the schedule from the drop down which is created and publish. Also publish data source and assign the schedule. This schedule will automatically run for the assigned time and the workbook is refreshed.
16) How Does Tableau Work?
While Tableau lets you analyze databases and spreadsheets like never before, you don’t need to know anything about databases to use Tableau. In fact, Tableau is designed to allow business people with no technical training to analyze their data efficiently.Tableau is based on three simple concepts:
Connect: Connect Tableau to any database that you want to analyze.
Note that Tableau does not import the data. Instead it queries to the database directly.
Analyze: Analyzing data means viewing it, filtering it, sorting it, performing calculations on it, reorganizing it, summarizing it, and so on.Using Tableau you can do all of these things by simply arranging fields of your data source on a Tableau worksheet. When you drop a field on a worksheet, Tableau queries the data using standard drivers and query languages (like SQL and MDX) and presents a visual analysis of the data.
Share: You can share results with others either by sharing workbooks with other Tableau users, by pasting results into applications such as Microsoft Office, printing to PDF or by using Tableau Server to publish or embed your views across your organization.
What are the difference between tableau 7.0 and 8.0 versions?
New visualizations are introduced like tree map bubble chart and box and whisker plot
We can copy worksheet directly from one workbook to another Workbook
Introduced R script
17) What are the features of Tableau 8.3?
With Kerboros support, Tableau 8.3 advances enterprise-grade data analysis with these enhancements:
Provides seamless, single sign-on experience from Tableau client to back-end data sources
Protects sensitive data with delegated access and viewer credential management
Connects to live data sources through stable, automated back-end authentication
Leverages existing IT investments in enterprise-grade authentication and data security
Supports smart card authentication
18) How do I automate reports using Tableau software?
You need to publish report to tableau server, while publishing you will find one option to schedule reports.You just need to select the time when you want to refresh data.
Speed
How fast can you get up and running with the system, answer questions, design and share dashboards and then change them? This is Where systems like Tableau and GoodData are far better than old – school business intelligence like Business Objects or Cognos. Traditional systems took months or years to intelligence like Business Objects or Cognos. Traditional systems took months or years to implement, with costs running to millions. Tableau has a free trail that installs in minutes and GoodData is cloud – based, so they are faster to implement by orders of magnitude. They are also faster to results: traditional BI requires IT and developers to make any changes to reports, so business users are struck in a queue waiting to get anything done. Tableau and GoodData provide more of a self – service experience.
Analysis layer
This is where Tableau excels. It has a powerful and flexible drag & drop visualization engine based on some technology from Stanford. Traditional BI typically provide some canned reports but changing them requires significant time and money.
Data layer
This is where the three options are most different:
GoodData requires you to move your data to its cloud. Traditional BI typically requires you to move your data to its data warehouse system. Tableau connects to a variety of existing data source and also provides a fast in – memory data engine, essentially a local database. Since most enterprises have their data stored all over the place, this provides the most choice and lets companies use the investment they’ve already made.
Enterprise readiness.
19) What is a parameter in Tableau ? And how it works.?
Parameters are dynamic values that can replace constant values in calculations and can serve as filters
What are Filters? How many types of filters are there in Tableau?
Filter is nothing but it is restricted to unnecessary, it is showing exact data. Basically filters are 3 types.
Quick filter
Context filter
Datasource filter
20) What is the difference between context filter to other filters?
Whenever we crate context filter >> Tableau will create a temporary table for this particular filter set and other filters will be apply on context filter data like cascade parameters… suppose we have crated context filter on countries >> we have chosen country as USA and India >> Tableau will create a temporary table for this two countries data and if you have any other filers >>other will be apply on this two countries data if we don’t have any context filter >> each and individual record will check for all filters
21) What is disadvantage of context filters?
The context filter is not frequently changed by the user – if the filter is changed the database must recomputed and rewrite the temporary table, slowing performance.
When you set a dimension to context, Tableau crates a temporary table that will require a reload each time the view is initiated. For Excel, Access and text data sources, the temporary table created is in an Access table format. For SQL Server, My SQL and Oracle data sources, you must have permission to create a temporary table on your server. For multidimensional data source, or cubes, temporary tables are not crated, and context filters only defined which filters are independent and dependent.
What is the Difference between quick filter and Normal filter in tableau?
Quick filter is used to view the filtering options and can be used to select the option. Normal filer is something you can limit the options from the list or use some conditions to limit the data by field or value.
22) What is benefit of Tableau extract file over the live connection?
Extract can be used anywhere without any connection and you can build your own visualizations without connecting to Database.
23) How to combine two excel files with same fields but different data (different years)?
I have 5 different excel files (2007.xls, 2008.xls..2011.xls) with same fields (film name, genre, budge, rating, profitability) but with data from different year (2007 to 2011). Can someone tell me how can I combine the film name, genre and profitability so that I can see the visualization of 2007 to 2011 in a single chart.
24) What is the Max no of tables we can join in Tableau?
We can join max 32 table, it’s not possible to combine more than 32 tables.
25) How does the integration of Tableau with R works?
R is a popular open-source environment for statistical analysis. Tableau Desktop can now connect to R through calculated fields and take advantage of R functions, libraries, and packages and even saved models. These calculations dynamically invoke the R engine and pass values to R via the Rserve package, and are returned back to Tableau.
Tableau Server can also be configured to connect to an instance of Rserve through the tabadmin utility, allowing anyone to view a dashboard containing R functionality.
Combining R with Tableau gives you the ability to bring deep statistical analysis into a drag-and-drop visual analytics environment.
26) What is Page shelf?
Page shelf is power full part of tableau That you can use to control the display of output as well as printed results of output.
27) How can we combine database and flat file data in tableau desktop?
Connect data two times, one for database tables and one for flat file. The Data->Edit Relationships
Give a join condition on common column from db tables to flat file
28) How to add custom Color to Tableau?
Create Custom Color code in “Preferences.tps”
Navigation ::: Documents » My Table Repository »Preferences.tps
Add custom color code Note: In tableau 9.0 version we have color picker option.

29) How to design a view to show region wise profit and sales.I did not want line and bar chat should be used for profit and sales?
Generate the Map using cities –>then Drag the Profit and sales to the Details–>Add the state as Quick filter

30) How to create cascading filters without context filter ?
I have filterl and filter2..Based on filterl I need to filter2 data
Ex: Filterl as Country and Filter 2: States
I have chosen country as USA and filter2 should display only USA states
Choose options of Filter2 states :
select option of “Only relevant values “
31) What is dual axis?
To display two measure in one graph
32) What is blended axis?
Multiple Measures are shown in single axis and also all the marks shown in single pane
Drag a dimension in a column
Drag the first measure in column
Drag 2nd measure in existing axis
Http://onlinehelp.tableau.com/current/pro/online/mac/en-
Us/multiplemeasures_blendedaxes.html

33) What is Data Visualization?
A much advanced, direct, precise and ordered way of viewing large volumes of data is called data visualization. It is the visual representation of data in the form of graphs and charts, especially when you can’t define it textually. You can show trends, patters and correlations through various data visualization software and tools; Tableau is one such data visualization software used by businesses and corporates.
34) What are the differences between Tableau desktop and Tableau Server?
While Tableau desktop performs data visualization and workbook creation, Tableau server is used to distribute these interactive workbooks and/or reports to the right audience. Users can edit and update the workbooks and dashboards online or Server but cannot create new ones. However, there are limited editing options when compared to desktop.
Tableau Public is again a free tool consisting of Desktop and Server components accessible to anyone.
35) Define parameters in Tableau and their working.
Tableau parameters are dynamic variables/values that replace the constant values in data calculations and filters. For instance, you can create a calculated field value returning true when the score is greater than 80, and otherwise false. Using parameters, one can replace the constant value of 80 and control it dynamically in the formula.

36) Differentiate between parameters and filters in Tableau.
The difference actually lies in the application. Parameters allow users to insert their values, which can be integers, float, date, string that can be used in calculations. However, filters receive only values users choose to ‘filter by’ the list, which cannot be used to perform calculations.
Users can dynamically change measures and dimensions in parameter but filters do not approve of this feature.
37) What are fact table and Dimension table in Tableau?
—>Facts are the numeric metrics or measurable quantities of the data, which can be analyzed by dimension table. Facts are stores in Fact table that contain foreign keys referring uniquely to the associated dimension tables. The fact table supports data storage at atomic level and thus, allows more number of records to be inserted at one time. For instance, a Sales Fact table can have product key, customer key, promotion key, items sold, referring to a specific event.
—>Dimensions are the descriptive attribute values for multiple dimensions of each attribute, defining multiple characteristics. A dimension table ,having reference of a product key form the fact table, can consist of product name, product type, size, color, description, etc.

38) What are Quick Filters in Tableau?
Global quick filters are a way to filter each worksheet on a dashboard until each of them contains a dimension. They are very useful for worksheets using the same data source, which sometimes proves to a disadvantage and generate slow results. Thus, parameters are more useful.
39) State limitations of parameters in Tableau.
Parameters facilitate only four ways to represent data on a dashboard (which are seven in quick filters). Further, parameters do not allow multiple selections in a filter.
40) What is aggregation and disaggregation of data in Tableau?
Aggregation and disaggregation in Tableau are the ways to develop a scatterplot to compare and measure data values. As the name suggests, aggregation is the calculated form of a set of values that return a single numeric value. For instance, a measure with values 1,3,5,7 returns 1. You can also set a default aggregation for any measure, which is not user-defined. Tableau supports various default aggregations for a measure like Sum, average, Median, Count and others.
Disaggregating data refers to viewing each data source row, while analyzing data both independently and dependently.
41) What is Data Blending?
Unlike Data Joining, Data Blending in tableau allows combining of data from different sources and platforms. For instance, you can blend data present in an Excel file with that of an Oracle DB to create a new dataset.
42) What is Content Filter?
The concept of context filter in Tableau makes the process of filtering smooth and straightforward. It establishes a filtering hierarchy where all other filters present refer to the context filter for their subsequent operations. The other filters now process data that has been passed through the context filter.
Creating one or more context filters improves performance as users do not have to create extra filters on large data source, reducing the query-execution time.
You can create by dragging a filed into ‘Filters’ tab and then, Right-Click that field and select ‘’Add to Context”.

43) What are the limitations of context filters?
Tableau takes time to place a filter in context. When a filter is set as context one, the software creates a temporary table for that particular context filter. This table will reload each time and consists of all values that are not filtered by either Context or Custom SQL filter.
44) Name the file extensions in Tableau.
There are a number of file types and extensions in Tableau:
• Tableau Workbook (.twb)
• Tableau Packaged Workbook (.twbx)
• Tableau Datasource (.tds)
• Tableau Packaged Datasource (.tdsx)
• Tableau Data extract (.tde)
• Tableau Bookmark (.tdm)
• Tableau Map Source (.tms)
• Tableau Preferences (.tps)
45) Explain the difference between .twb and .twbx
.twb is the most common file extension used in Tableau, which presents an XML format file and comprises all the information present in each dashboard and sheet like what fields are used in the views, styles and formatting applied to a sheet and dashboard.
But this workbook does not contain any data. The Packaged workbook merges the information in a Tableau workbook with the local data available (which is not on server). .twbx serves as a zip file, which will include custom images if any. Packaged Workbook allows users to share their workbook information with other Tableau Desktop users and let them open it in Tableau Reader.
46) What are Extracts and Schedules in Tableau server?
Data extracts are the first copies or subdivisions of the actual data from original data sources. The workbooks using data extracts instead of those using live DB connections are faster since the extracted data is imported in Tableau Engine.
After this extraction of data, users can publish the workbook, which also publishes the extracts in Tableau Server. However, the workbook and extracts won’t refresh unless users apply a scheduled refresh on the extract. Scheduled Refreshes are the scheduling tasks set for data extract refresh so that they get refreshed automatically while publishing a workbook with data extract. This also removes the burden of republishing the workbook every time the concerned data gets updated.
47) Name the components of a Dashboard
• Horizontal- Horizontal layout containers allow the designer to group worksheets and dashboard components left to right across your page and edit the height of all elements at once.
• Vertical- Vertical containers allow the user to group worksheets and dashboard components top to bottom down your page and edit the width of all elements at once.
• Text
• Image Extract: – A Tableau workbook is in XML format. In order to extracts images, Tableau applies some codes to extract an image which can be stored in XML.
• Web [URL ACTION]:- A URL action is a hyperlink that points to a Web page, file, or other web-based resource outside of Tableau. You can use URL actions to link to more information about your data that may be hosted outside of your data source. To make the link relevant to your data, you can substitute field values of a selection into the URL as parameters.
48) How to view underlying SQL Queries in Tableau?
Viewing underlying SQL Queries in Tableau provides two options:
• Create a Performance Recording to record performance information about the main events you interact with workbook. Users can view the performance metrics in a workbook created by Tableau.
Help> Settings and Performance> Start Performance Recording
Help> Setting and Performance > Stop Performance Recording
• Reviewing the Tableau Desktop Logs located at C:\Users\\My Documents\My Tableau Repository. For live connection to data source, you can check log.txt and tabprotosrv.txt files. For an extract, check tdeserver.txt file.
49) What is Page shelf?
Tableau provides a distinct and powerful tool to control the output display known as Page shelf. As the name suggests, the page shelf fragments the view into a series of pages, presenting a different view on each page, making it more user-friendly and minimizing scrolling to analyze and view data and information. You can flip through the pages using the specified controls and compare them at a common axle.
50) How to do Performance Testing in Tableau?
Performance testing is again an important part of implementing tableau. This can be done by loading Testing Tableau Server with TabJolt, which is a “Point and Run” load generator created to perform QA. While TabJolt is not supported by tableau directly, it has to be installed using other open source products.
51) Explain the concept of Dual Axis.
Dual Axis is an excellent phenomenon supported by Tableau that helps users view two scales of two measures in the same graph. Many websites like Indeed.com and other make use of dual axis to show the comparison between two measures and their growth rate in a septic set of years. Dual axes let you compare multiple measures at once, having two independent axes layered on top of one another.

52) How many maximum tables can you join in Tableau?
The maximum number of 32 tables can be joined in Tableau. A table size must also be limited to 255 columns (fields).
53) How to remove ‘All’ options from a Tableau auto-filter?
The auto-filter provides a feature of removing ‘All’ options by simply clicking the down arrow in the auto-filter heading. You can scroll down to ‘Customize’ in the dropdown and then uncheck the ‘Show “All” Value’ attribute. It can be activated by checking the field again.

54) What different products Tableau provide?
• Tableau Server : on-premise or cloud-hosted software to access the workbooks built
• Tableau desktop: desktop environment to create and publish standard and packaged workbooks.
• Tableau Public: workbooks available publicly online for users to download and access the included data.
• Tableau Reader: get a local access to open Tableau Packaged workbook

55) How can you display top five and last five sales in the same view?
Create two sets, one for top 5 another for bottom 5 and the join these two sets displaying a unique set of total 10 rows.
56) What is TDE file?
TDE is a Tableau desktop file that contains a .tde extension. It refers to the file that contains data extracted from external sources like MS Excel, MS Access or CSV file.
There are two aspects of TDE design that make them ideal for supporting analytics and data discovery.
• Firstly, TDE is a columnar store
• The second is how they are structured which impacts how they are loaded into memory and used by Tableau. This is an important aspect of how TDEs are “architecture aware”. Architecture-awareness means that TDEs use all parts of your computer memory, from RAM to hard disk, and put each part to work what best fits its characteristics.
57) How to use group in calculated field?
By adding the same calculation to ‘Group By’ clause in SQL query or creating a Calculated Field in the Data Window and using that field whenever you want to group the fields.
• Using groups in a calculation. You cannot reference ad-hoc groups in a calculation
• Blend data using groups created in the secondary data source: Only calculated groups can be used in data blending if the group was created in the secondary data source.
• Use a group in another workbook. You can easily replicate a group in another workbook by copy and pasting a calculation.
58) Can parameters have dropdown list?
Yes, parameters do have their independent dropdown lists enabling users to view the data entries available in the parameter during its creation.
We will continuously update tableau interview questions and answers in this site with real time scenarios by tableau experts.You can request for tableau interview questions and answers pdf in the Contact us form.

 

 

Microstrategy interview questions

 Top most important Microstrategy interview questions and answers by Experts:

Here is a list of Top most important Microstrategy interview questions and answers by Experts.If you want to download Microstrategy interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Microstrategy interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Microstrategy ,Register for Microstrategy online training here.

 

1) What are different types of metrics available in microstrategy?
Level Metric: Level metric defined the level at which the metric aggregates. By default it is the report level. This is a bit huge concept and more information could be found in the manuals.

Transformation Metric: Transformation applied to a metric. Transformation is a schema object which is used in a metric for time based analysis (Example: Year-over-Year, Month-to-Date, Year-to-Date, etc.). There are two types of transformation – table based and expression based.

Pass through Metrics: Metric created using pass through functions (example: ApplySimple). Pass through functions are executed at the database level.

Adaptive Metric: A metric defined on a fact which is mapped to two columns in two tables (detail and aggregate) with different functions applied on both the colu mns. This is achieved with pass through functions (ApplySimple and ApplyAgg).

Non-aggregate metric: By default metrics aggregate to a higher level based on the attributes on a report, the default aggregate function is “sum”. This aggregation can be set to none, so that the metric does not aggregate to any level.

Smart Metric: When a compound metric is defined with other metric objects using arithmetic operatic (like sum(M1/M2)) the sub total of the metric can be calculated in multiple ways. Case where they are calculated row by row are smart metric. Example: In the above example, if the total are calculated using the mentioned expression, it is defined as smart metric – “Sum (M1) / Sum (M2)”

Derived Metric: A metric created within a report (local to that report) using the report objects of the same report. Derived metric are OLAP services and are calculated on the I-Server and do not reflect in the SQL. Example: If a report has two metrics, M1 and M2. A derived metric can be defined as M1+M2 or M1/M2 and so on.

Embedded Metric: Embedded metrics are objects whose definitions and object IDs are unique to and exist only in the context of the MicroStrategy Report in which they reside. An embedded metric will have a different object ID than that from which it originated. As its name implies, an embedded metric does not exist outside the report object. In other words, that particular object’s definition and ID do not exist independently in the metadata object info and object definition tables and, therefore, cannot be used in other reports. Instead, the report definition contains an “embedded objects” folder as part of its definition (not a freestanding folder), and the embedded metric exist only in this folder. This is not an Derived metric. Embedded metrics are created when there exists a prompted filter in a conditional metric and where the report is saved after answering those prompts. The metric will have same definition as that of original metric but its ID will be different than that of the original metric. Hence any changes made to the original metric will not be reflected to the report. You need to remove the embedded metric and add again the original metric  1. What is MicroStrategy Intelligence Server™?MicroStrategy Intelligence Server is the industry’s most advanced, secure and scalable business intelligence server.

2) What does MicroStrategy Intelligence Server allow users to do?
MicroStrategy Intelligence Server provides reporting and OLAP analysis for the whole enterprise. All business users can obtain scorecards and dashboards, operational reports, queries and OLAP and predictive analyses without learning any programming or database syntax.

3) What are the benefits of using MicroStrategy Intelligence Server?
MicroStrategy Intelligence Server provides one centralzed architecture for all users’ monitoring, reporting and analysis requirements. MicroStrategy Intelligence Server also provides scalability to analyze any amount of data, support for any number of users and a 24 X 7 operating environment, with robust security.

4) Does MicroStrategy Intelligence Server run on Windows®, UNIX®, or Linux®?
 MicroStrategy Intelligence Server is certified on Windows, UNIX, and Linux operating systems. MicroStrategy Intelligence Server has been designed to be a completely open architecture built on industry standards and compiled to run on multiple operating systems.

5) How do you ensure simultaneous releases across multiple operating systems?
 MicroStrategy Intelligence Server is engineered to be platform-independent on a single code base and is simply compiled appropriately for each certified operating system. As a result, MicroStrategy can release Intelligence Server on all certified operating systems at the same time with the same features.

6) Does MicroStrategy Intelligence Server run on 32-bit and 64-bit systems?
 Yes. MicroStrategy Intelligence Server provides an industry first — a common code base architecture for all supported 32- and 64-bit operating systems. This single code base ensures that the latest functionality is always available on all these operating systems at the same time.

7) What are the advantages of running a BI server on a 64-bit operating system?
 A BI application can access much greater memory resources when running on a 64-bit systems than it could on a 32-bit system. More addressable memory results in more BI applications, more sophisticated applications, more reports, larger reports, better performance and more users — all with fewer servers. Organizations can combine MicroStrategy’s industry leading scalability with the advantages of 64-bit UNIX to deploy the richest BI applications, with the highest performance and the most users.

8) Do users need to learn any programming languages or database syntax to run business queries?
No, business users can run any query without learning any programming languages or database syntax. MicroStrategy Intelligence Server automatically and appropriately processes all business questions by accessing a report cache or Intelligent Cube, or by generating dynamic SQL or MDX that is optimized to each data source platform.

9) Can I deploy a single BI application in multiple languages?
 Yes. Every component of a BI application can be translated and presented in multiple languages, including all MicroStrategy user interfaces, metadata objects such as the names of reports and documents, and data within a data warehouse.

10) Can I access multiple data sources transparently from a single data model?
Yes. MicroStrategy MultiSource Option is a new and fully integrated add-on component of MicroStrategy Intelligence Server. It allows users to seamlessly report, analyze, and monitor data across multiple sources through a single business model.

11)  What is a cache?
 A cache contains the properties and data of a report once a report has been run. Caches can be stored in memory and/or on disk. When users ask for a report that is cached, the Intelligence Server will retrieve the data from disk or memory instead of running a query on the data source. Cache creation and usage securely leverages other users’ work, increases query performance and reduces the workload on the data warehouse.

12) What are Intelligent Cubes?
Intelligent Cubes are in-memory caches stored by the Intelligence Server. While accessing an Intelligent Cube, users can easily add or remove report objects (such as attributes and metrics), add new metric calculations and filter their view of the data — all in an ad hoc fashion with speed-of-thought response times. Data stored outside an Intelligent Cube is automatically accessed using the ROLAP engine when drilling to more details.

13) Can MicroStrategy Intelligence Server access any amount of data?
Yes. MicroStrategy Intelligence Server can scale to any amount of data by leveraging your database’s strengths. Using Very Large Database (VLDB) drivers, MicroStrategy Intelligence Server automatically tunes its operations to leverage the unique strengths of your database software and hardware.

14) Can MicroStrategy Intelligence Server scale easily and cost effectively based on my growing needs?
Yes. MicroStrategy Intelligence Server provides out-of-the-box clustering capabilities that allow corporations to leverage their initial investments and gradually scale as more users and data are added to the system, or when different styles of BI are required.

15) Can my users perform on-demand analysis without any downtime?
Yes. MicroStrategy Intelligence Server provides complete fault tolerance and fail-over support to maximize the uptime of your application.

16) Will my users get the performance they need?
 Yes. MicroStrategy Intelligence Server maximizes system performance at all layers of your system. Intelligence Server:

· Leverages the unique strengths of each database software and hardware using VLDB drivers.

· Uses four levels of report and dataset caching to optimize performance for documents, reports, analyses and prompt lists.

· Increases analytic performance with its Intelligent Cubes. Users or administrators can schedule creation or refreshing of    Intelligent Cubes during low usage time periods.

· Minimizes network traffic by sending users only the result sets they want to view.

17) What types of analysis does MicroStrategy Intelligence Server support?
MicroStrategy Intelligence Server users can easily perform simple to sophisticated analysis such as basic performance indicators, market basket, churn, retention and decile analysis. Users can make use of a complete library of statistical, mathematical, financial and OLAP functions. Other types of analysis include hypothesis testing, regressions, neural networks, decision tress, clustering and bond calculations.

18) Can I use my homegrown functions or integrate with existing third-party statistical and data mining software?
Yes. MicroStrategy Intelligence Server allows enterprises to integrate their homegrown functions as well as third-party software using an advanced plug-and-play architecture. Predictive Models created by third-party data mining software can also be imported using PMML (Predictive Markup Language).

19) What type of security does MicroStrategy Intelligence Server provide?
 MicroStrategy Intelligence Server provides a highly robust security model at four different layers in your system:

1.User level – Login and ID authentication ensure users have authorization to access the     MicroStrategy system.

2.  Application level – Access control lists and user privileges ensure proper access to MicroStrategy objects and functionality.

3. Data transmission level – Encryption and dual firewall configuration ensure safe transmission of data.

4. Data level – Security filters ensure users access only the data they have authorization to view.

20) Can users access MicroStrategy Intelligence Server using LDAP, data source or Windows authentication?
 Yes. MicroStrategy Intelligence Server integrates with LDAP and Windows security systems. Users will only need to login with their LDAP or Windows login to access MicroStrategy projects through Intelligence Server.

21) Can my administrator easily maintain and manage MicroStrategy Intelligence Server?
Yes. MicroStrategy Intelligence Server’s centralized architecture provides one console from which all maintenance and administration can be performed. In addition, a standardized data dictionary for enterprise reporting and OLAP analysis is stored in a metadata repository and enables reusable reporting objects and business rules.

22) Does MicroStrategy have Windows- or UNIX-based administration tools?
MicroStrategy has both Windows- and UNIX-based administration tools. UNIX and Linux administration is provided through a Java-based Control Center. Both the Windows administration tools and the Java-based Control Center provide local or remote administration and monitoring of all Intelligence Servers in the BI implementations.

23) What are the supported languages for MicroStrategy Intelligence Server?
MicroStrategy Intelligence Server is available in English (US and UK), French, German, Spanish, Italian, Portuguese (Brazilian), Swedish, Chinese (Simplified and Traditional), Korean and Japanese.

24) Does MicroStrategy Intelligence Server integrate with other system management software?
 Yes. MicroStrategy Intelligence Server integrates with other system management software. In fact, all inner statistics of Intelligence Server are published to Performance Monitor and diagnostic logs. As a result, third-party system management software such as IBM® Tivoli® or CA’s Unicenter® can remotely monitor these statistics.

25) Does MicroStrategy Intelligence Server support scheduling? 
Yes, MicroStrategy Intelligence Server supports time-based and event-based schedules. These schedules automatically trigger report execution and place a notification message in the user’s History folder upon completion.

26) Why is MicroStrategy Intelligence Server better than competitor’s products?
MicroStrategy Intelligence Server wins over its competitors because it solves all issues that corporations face when implementing a query, reporting and analysis solution. MicroStrategy Intelligence Server provides the solutions required for a successful enterprise business intelligence system: full range of analysis, support for any number of users, support for any amount of data, iron-clad security, world-class reliability, easy administration and maintenance, and high performance.

27) How long does it take to deploy MicroStrategy Intelligence Server?
 MicroStrategy Intelligence Server installs in minutes and automatically presents a configuration wizard to connect to the database. Within an hour, the installation and setup can be completed.

28) Can Intelligence Server integrate with my existing data structure?
Yes. MicroStrategy Intelligence Server easily integrates with your existing data structure with its support for the major types of schemas like Star, Snowflake, Hybrid, Denormalized, Normalized and Aggregated Schema. MicroStrategy Intelligence Server also integrates with SAP® BW InfoCubes and QueryCubes.

29) How does Intelligence Server integrate with the rest of the MicroStrategy platform?
MicroStrategy Intelligence Server is the analytical server that processes all requests from all MicroStrategy end users. Intelligence Server is the central product that links all MicroStrategy components to the data warehouse.

30) What data sources does MicroStrategy Intelligence Server support?
 MicroStrategy Intelligence Server can access data in all major databases including Oracle®, IBM DB2®, Microsoft® SQL Server, Microsoft Access®, Microsoft Excel®, Teradata®, Sybase®, Red Brick®, Informix®, HP NeoView, HP NonStop SQL/MP, Netezza®, MySQL, PostgreSQL, Greenplum, Kognito, Vertica, Aster nCluster, text files, and other ANSI-92 compatible relational databases. It can also access multidimensional data sources such as SAP BW, Hyperion Essbase, and Microsoft Analysis Services. Additionally, Intelligence Server can access data from EII sources including Composite, Oracle Transparent Gateways, IBM WebSphere Information Integrator, and MetaMatrix.

31) To save time, can many reports be run at the same time and at the user level?
 Yes. MicroStrategy Intelligence Server can execute multiple reports simultaneously. When report results are ready they are saved in a personal History folder.

32) Does MicroStrategy Intelligence Server support XML? 
Yes. MicroStrategy Intelligence Server uses XML to transfer data. This enables organizations to present information from Intelligence Server to end users through multiple mediums including the Web, email and web services.

33) How can I create statements, report books, or dashboard books in MicroStrategy?
 Users have the ability to maintain separate documents and combine them into a single, finalized document (report book, statement, or dashboard book). The individual documents can be maintained by different users and they can have the following unique qualities:

·  Page groupings
·  Layout
·  Page Orientation
·  Vertical or horizontal repeating sections
·  Conditional formatting
·  Pagination
·  Datasets

Enabling users to group together multiple documents into one makes it significantly easier to develop and maintain individual pieces of the unified document.

34) What OLAP capabilities do I have within a MicroStrategy Report Services document?
 Users have the ability to drill within the document, drill anywhere, or disable drilling. Drilling capabilities can be applied to a grid or graph, enabling users to perform an investigative analysis directly on a dashboard, scorecard, or enterprise report. Users can drill out of the dashboard to investigate across the data warehouse or multiple data sources and generate new information-rich reports.Using the multi-select feature, business users can dynamically select any group of elements and create derived element groups on the fly. Elements can be removed or added dynamically and users can create their own calculations between various groups or individual items.

35) I want only one version of the dashboard to view in Flash and DHTML (AJAX), but I have Flash-advanced visualizations in the dashboard. How can I design this dashboard for DHTML? 
Only one design of the dashboard is required. Flash-advanced visualizations have flash plug-ins that are maintained within the container of the advanced visualization. Flash-advanced visualizations can be rendered in both Flash and DHTML, and the same controls are made available to users, enabling a seamless integration between Flash and DHTML design.

36) How can users design dashboards quickly and without programming experience or training?
 MicroStrategy offers out-of-the box templates that provide a great starting point for dashboard designers. Each template contains a series of containers, or panel stacks, that enable users to quickly add components to each panel stack, such as: text boxes, shapes, reports, grids, graphs, panel stacks, HTML containers, selectors, and widgets. Also, pre-formatted customized templates can be created to place the design experience in the hands of end-users. These pre-formatted templates can include the corporate logo and color layout. Users can simply drag, drop, and select dashboard components to quickly design a dashboard in the matter of minutes.

37) Are there any special requirements needed to move objects across projects? 

Yes. In order to perform cross-project operations, the projects involved must originate from the same source project. In other words, the projects can only be related by the duplication of a single project. This ensures that the projects have a similar set of schema and application objects, and that the object ID’s in the two projects are the same. MicroStrategy Object Manager uses the object and version ID’s across the projects to perform comparisons.MicroStrategy Object Manager prevents the user from attempting operations across unrelated projects.

38) How does MicroStrategy Object Manager determine if two projects are related? 
MicroStrategy Object Manager compares the Schema ID’s of the two projects. Duplicated projects have different Project ID’s, but their Schema ID’s are the same.
39) What is the Conflict Resolution Window? 

The Conflict Resolution window provides the user with a means to decide how to handle object conflicts between the source project and the destination project. In addition, the Conflict Resolution window displays the object name in the original project, the object name in the destination project and the type of conflict. Users may also specify a new name for the object depending on the action chosen
40) How does MicroStrategy Object Manager determine if two objects in different projects are the same? 
To determine if two objects are the same, MicroStrategy Object Manager compares their Object ID’s. If these ID’s are the same, MicroStrategy Object Manager then compares the Version ID’s. If the Version ID’s are the same, the Conflict Resolution grid lists the conflict as ‘Exists Identically.’ If the Version ID’s are different, the Conflict Resolution grid lists the conflict as ‘Exists Differently.’
41) How can the user determine the Object ID of an object? 

To view the Object ID of an object, right-mouse click on the object and select ‘Properties.’ The Object ID and Version ID are listed on the ‘General’ tab.
42) Why does MicroStrategy Object Manager search for object dependencies? 
MicroStrategy Object Manager makes a list of all object dependencies before copying an object to prevent metadata inconsistency. The time required for dependency checking varies based on a customer’s metadata size and schema complexity. For large metadata and complex schemas, gathering all the dependencies may take a long time.
43) Can schema objects be copied across projects with MicroStrategy Object Manager? 

Yes, schema objects can be copied across projects using MicroStrategy Object Manager. MicroStrategy Object Manager moves objects seamlessly between similar projects such as from a development project version to a production project version where the warehouses are the same in terms of views, prefixes, and warehouse structure. However, subtle changes in the warehouse that relate to prefixes, views, or table structure cannot be tracked by MicroStrategy Object Manager. For situations where the projects’ warehouse structures or setups are dissimilar, users may be required to make further edits of the objects to ensure full integration into the destination project. These edits may include hierarchical relationship changes or modifications to the prefixes.
44) How does MicroStrategy Object Manager integrate with the MicroStrategy Product Suite security model? 

Security in MicroStrategy Object Manager is based on the MicroStrategy 7.x Product Suite security model. All activities that can be performed in MicroStrategy Object Manager are governed by privileges and access control lists. For example, if a user is not allowed to access a certain folder in MicroStrategy Agent, they will not be able to access the folder in MicroStrategy Object Manager.
45) Is it possible to use MicroStrategy Object Manager while other users are making changes in MicroStrategy Agent? 
Using MicroStrategy Object Manager to copy/move objects around is not recommended while other user sessions are making changes using MicroStrategy Agent, as it could lead to metadata inconsistency. Project and schema locking prevent multiple users sessions from manipulating the schema at the same time. This prevents metadata inconsistency from occurring.
46) What are the tracing options available in MicroStrategy Object Manager? 

Tracing is available under the Tools/Diagnostics menu. These tracing options apply to every MicroStrategy product installed on the machine.To see the SQL that has been executed against the metadata, go to the Advanced tab and turn on ‘SQL Tracing’ under the DSS MDServer key.Function level tracing can be accomplished by going to the Advanced tab and turning on ‘Function Level Tracing’ under the DSS ObjectManager key.
47) Where are dependent objects copied if they do not already exist in the destination project? 

If the location exists in the destination project, the dependent object is copied to that location. If the location does not exist in the destination project, a new folder entitled ‘Dependencies’ is created and the object is copied to that folder.
48) What happens if the owner of an object does not exist in the destination project? 

If the owner of the source object does not exist in the destination project, the user login for the destination project takes ownership of the object when it is copied or replaced.
49) Where can users find more information on MicroStrategy Object Manager? 
Further information can be found in the release notes, as well as in MicroStrategy Object Manager’s online help.
50) Does MicroStrategy Web support clustering via Cisco Local Router or any other third-party clustering software?
MicroStrategy Web relies on third-party web-clustering software to provide clustering functionality. MicroStrategy Web is designed to be stateless so that each individual MicroStrategy Web node can function without the knowledge of the existence of other nodes. Therefore, any third-party software used to cluster web servers can be used.
51) What information is shared by the application across MicroStrategy Web nodes?
MicroStrategy Web is designed to be as stateless as possible. Therefore, no information is shared by the MicroStrategy Web application across cluster nodes. All state information for running jobs is pushed to the client browser.When a report is submitted by a MicroStrategy Web user, the user will receive a wait page in the client browser. This wait page will poll the MicroStrategy Web Server periodically for the status of the report. This polling is performed as new http requests. This http request will contain all state information, including encrypted login information and MicroStrategy Intelligence Server connection information.

52) Is MicroStrategy Web “cluster-aware”?
The MicroStrategy Web application is designed so that each MicroStrategy Web cluster node does not need to know that it is a member of a cluster. MicroStrategy Web is designed to be stateless, so that each client http request can be processed individually without having to persist information within the MicroStrategy Web application. Therefore, third-party Web-server clustering software can be used to cluster together multiple web servers running MicroStrategy Web.

53) Should MicroStrategy Web be specifically configured to access a MicroStrategy Intelligence Server cluster?
No. When the administrator configures MicroStrategy Web to access a particular MicroStrategy Intelligence Server, the MicroStrategy Web application will automatically detect that the MicroStrategy Intelligence Server is a member of a cluster. Once this detection is made, MicroStrategy Web will automatically add all the other members of the same cluster into the pool of available MicroStrategy Intelligence Servers.

54) What information is shared by the application across MicroStrategy Intelligence Server nodes?
Report caches are shared in a cluster and Object caches on each MicroStrategy Intelligence Server node are synchronized.

55) Is a copy of each report cache retained in each MicroStrategy Intelligence Server node?

No. Each MicroStrategy Intelligence Server retains a lookup table with information about the existence and location of report caches. When a cluster node creates a report cache, information about the location of the new cache is shared with the other cluster nodes. Each cluster node then updates its own lookup table with the location of the new cache.

56) If a MicroStrategy Intelligence Server node crashes, will report caches be lost?
Although report caches will not be lost, access to report caches may be affected, depending on the way in which the report cache is configured.If a separate file server is used as a common report cache repository for all MicroStrategy Intelligence Server cluster nodes, then the loss of a cluster node will not affect access to the report cache by other nodes.If the cluster is configured such that each node locally hosts the report cache created by that node, then those report caches residing in the lost node will naturally be inaccessible. If any report cached in that lost node is requested, then another node within the cluster will re-run and re-cache the report. When the cluster node is recovered and rejoined into the cluster, all report caches in that cluster node will be made available again to the rest of the cluster.
57) If a MicroStrategy Intelligence Server node is removed from a cluster manually, will report caches be lost?
If an administrator removes a cluster node from a cluster, then all report caches that had been created by that cluster node will be inaccessible by the rest of the cluster, whether or not a separate file server is used as a common report cache. This behavior is by design.

58) How does MicroStrategy Intelligence Server 7.0 clustering enable cache sharing?
Each node in a MicroStrategy Intelligence Server 7.0 cluster maintains indices of the caches available on the different nodes. When a report is submitted, these indices will be searched and once an existing cache is found (in any nodes), the cached results will be retrieved directly from cache locations in either the local or remote machine.

59) What methods can be used to guarantee availability of the MicroStrategy Intelligence Server report cache?
To prevent the loss of a MicroStrategy Intelligence Server cluster node from affecting report cache availability, the cluster can be configured such that a separate file server is used as a common report cache repository. In order to maintain cache availability, this separate file server can be configured for failover with third-party clustering software.

60) If a report cache is created by a MicroStrategy Intelligence Server cluster node, will that report cache be seen in the Cache Monitor of another cluster node?
No. Although the new report cache will be available for use by other cluster nodes, the cache will not appear in the Cache Monitor of other cluster nodes. In order to see all report caches within a cluster, the administrator will need to create a separate data source within Desktop for each cluster node. Then, the report caches within each node can be administered separately, using the same instance of the MicroStrategy Desktop application.

61) If objects are created, modified or deleted, will the change be reflected across all MicroStrategy Intelligence Server cluster nodes?
Yes. Object caches are synchronized across all cluster nodes. If any change affecting the Metadata is made by one cluster node, then the cluster node broadcasts the change to the other cluster nodes. The other cluster nodes will then update their local object caches.
NOTE: Client-side object caches will not be automatically be refreshed. In MicroStrategy Desktop, for example, a user may have to explicitly click on ‘Refresh’ to see an object change be reflected in the client application.

62) Should all MicroStrategy Intelligence Server cluster nodes be configured identically?
Technically, MicroStrategy Intelligence Servers in a cluster do not have to be configured identically. The only technical requirements are that all MicroStrategy Intelligence Servers point to the same metadata and that all MicroStrategy Intelligence Servers have the same projects registered and in the same state (i.e., if Node A has Project A in a ‘Loaded’ state, then Node B must also have Project A in a ‘Loaded’ state.).However, in order to ease administration and to reduce the risk of unbalanced load across cluster nodes, it is recommended that all nodes use the same MicroStrategy Intelligence Server definition and that each machine shows identical characteristics (i.e., equal RAM, hard disk space, CPU).

63) Is it possible for different nodes of a MicroStrategy Intelligence Server cluster to run against different metadata repositories?
No, all the nodes in the same cluster must run against the same metadata.
64) Is it possible for different nodes of a MicroStrategy Intelligence Server cluster to run with different configuration settings under the same metadata repository?

Yes this is possible, using caution because users can configure different nodes at different settings. For example, differences in memory allocation for the cache, time out settings, etc can result in uneven performance across cluster nodes.
65) What communication protocol does MicroStrategy Intelligence Server use for intracluster communication?
MicroStrategy Intelligence Server 7.0 and MicroStrategy Intelligence Server 7.0 SP1 use TCP/IP when communicating between clusters. MicroStrategy Intelligence Server 7.1 will provide the option of using TCP/IP or UDP/IP (Universal Datagram Protocol). In 7.2, UDP support was removed as packet loss affects cluster synchronization.

66) If a MicroStrategy Intelligence Server cluster node is rebooted, will the node rejoin the cluster automatically?
Whenever a MicroStrategy Intelligence Server cluster node is stopped in any way besides explicitly shutting down the MicroStrategy Intelligence Server service, the node will automatically rejoin the cluster when the MicroStrategy Intelligence Server service is restarted. So, if the node crashes, then the node will rejoin the cluster automatically upon startup.

67) Does MicroStrategy Intelligence Server support clustering via Microsoft Cluster Server or any other third-party clustering software?
Microsoft Cluster Server (MSCS) can be used for failover of MicroStrategy Intelligence Server. However, MSCS and other third-party clustering software will not provide the load-balancing and some of the failover capabilities of MicroStrategy Intelligence Server’s native clustering solution.

68) Is it possible to run multiple instances of MicroStrategy Intelligence Server on the same Microsoft Windows NT machine?
MicroStrategy 7.0 does not support running multiple instances of MicroStrategy Intelligence Server on the same Microsoft Windows NT machine. This is because MicroStrategy Intelligence Server can support running multiple projects with different prioritization and configuration settings on one server. This functionality was not available in MicroStrategy DSS Server 5.x and thus, required running multiple instances of MicroStrategy Intelligence Server to accomplish the same functionality.

69) What is the maximum number of nodes that can be supported in a MicroStrategy Intelligence Server 7.0 cluster?
There is no technical hard limit on the maximum number of cluster nodes that can be supported by MicroStrategy Intelligence Server 7.0. However, when the number of nodes increases, there is increasing overhead put on the system by the clustering software. So, there will be practical limits related to the hardware configuration of the users’ system.

70) What is metadata?
Metadata is repository which stores microstrategy objects definitions and information about the data warehouse structure and content.Metadata is used by the intelligence server to evaluate the most efficient data retrieval  scenario to guarantee optimal query performance.Explain 2-tier and 3-tier Architecture in MSTR.

71) What is ScanMD and MD Doctor?
Scan MD is the tool to recover from logical inconsistencies where MD Doctor fixes physical errors, When working with Microstrategy there is a chance for the metadata to become corrupt.  There are 2 types of errors; Physical or Logical.When working with Microstrategy there is a chance for the metadata to become corrupt.  There are 2 types of errors; Physical or Logical.ScanMD is used to recover from Logical discrepancies, where as MD Doctor is used for Physical discrepancies. Why these discrepancies creep????? Like any software application, there are bugs in MicroStrategy, dandling code (a situation never programmed), etc. such a thing is the main reason behind discrepancies.
Logical discrepancies: Using desktop you can create/delete several objects. Sometime deletion of the objects doesn’t get reflected in metadata and they get deleted in metadata but still available in front-end. Now such a mis-match in case of schema objects is simply killing, because it won’t let you update schema at all. . Using ScanMD is not an JImagine not having ability to update schema easy task. ScanMD shows 95% for false errors as discrepancies. Engine team of MicroStrategy, Inc. needs to update the tool. For one error, you may have to go through 100+ errors. You can verify the error easily by having project opened side by side. NEVER EVER FIX A FALSE ERROR.
Physical discrepancies: There are not just 10 DSS tables that make metadata, there are several more database objects that are created for metadata; e.g., triggers, views. If any of these objects are messed up, you have a physical discrepancy in your project. 

What is clustering and types of clustering?
cluster is a group of two or more I-servers connected to each other in such a way that they behave like a single I-server.Each machine in the cluster is called a node.Each machine in the cluster runs the same service as other machines in the cluster hence any machine can stand the failure of other machine and take over the tasks of the failed machine.

 

 

Cognos TM1 interview questions

Top most important Cognos TM1 interview questions and answers by Experts:

Here is a list of Top most important Cognos TM1 interview questions and answers by Experts.If you want to download Cognos TM1 interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Cognos TM1 interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Cognos TM1,Register for Cognos TM1 online training here.

 

1. What are the basic features of Cognos TM1?

  • Real-time, interactive multidimensional database also with write-back functionality
    • 64-bit, in memory OLAP server capable of concentrate huge volumes of data
    • Enterprise level planning and analysis capabilities
    • Guided modeling environment to facilitate planning, analysis and forecasting
    • Rapid development of flexible models, including profitability models, without batch processing
    • Integrates with IBM Cognos Business Intelligence, to establish a single view of performance

2.How do you use TM1 cubes in Framework Manager?

TM1 v10.1 has the interoperability function configured to allow packages to be published from TM1.

3.How many types of viewers available in TM1?

There are 3 types of viewers in TM1.
1) Cube Viewer
2) Web Sheet Viewer
3) Navigation Viewer.

4. How to run Turbo Integrator Process from Command Line?

To initiate the TI process TM1RunTI is a command line interface tool.

5. Name the function which is used to serialize the TI process?

Synchronized () function can is used to serialize TI process so they can be processed sequentially.

6. What are the functions required to be called for enabling or disabling the bulk load of mode?

EnableBulkLoadMode() = Enable
DisableBulkLoadMode() = Disable.

7. Define Data Tab?

Series of statements that manipulates values for each record in data source.

8. Define TM1 package connector?

TM1 package connector helps to import data from packages/dimensions and custom queries.

9. What is Epilog Tab?

Series of statements that is to be executed after the data source is processed.

10. What is the limit of string length in Turbo Integrator?

The limit of string length in Turbo Integrator is 8000 single byte character. If the length will become more 8000 bytes then it gets truncated.

11. What is the meaning of Ending Bulk Load mode?

All the system and User threads will be resumed and user logins will also be allowed.

12. How we can generate cubes in Cognos?

Power Play Transformer contains dimension, measure, model and cube. There are different ways to generate a cube.
Steps to be followed:
• Just right click on cube name and build.
• We can write script in UNIX. Using that we can easily generate the cube.

13. How can you access TM1 web form portlet?

There is a option to integrate TM1 viewers in cognos Portlets.

14. What are different windows in portlet?

There are 3 type of viewers available in TM1.

-Cube Viewer

-Web Sheet Viewer

-Navigation Viewer.

15. What are the basic features of Cognos TM1?

-Real-time, interactive multidimensional database also with write-back functionality.

-64-bit, in memory OLAP server capable of concentrate huge volumes of data.

-Enterprise level planning and analysis capabilities.

-Guided modeling environment to facilitate planning, analysis and forecasting.

-Rapid development of flexible models, including profitability models, without batch processing.

-Integrates with IBM Cognos Business Intelligence, to establish a single view of performance.

16. Name the data sources which are available with TI?

Data Sources available with TI are:
• SAP via RFC
• IBM Cognos Packages
• Microsoft Analysis Services
• Relational databases using ODBC Connectivity
• Comma delimited text files including ACII files
• Other Cubes and Views

17. Define Sparse Cube?

It’s a cube in which number of populated cells as a percentage of total cells is too low.

18. Define Query Subject?

A Query Subject is a collection of Query Items. Generally query items have a defined relationship. It is primarily a database table upon which query action takes place.

19. What is Sparse Cube?

A cube in which number of populated cells as a percentage of total cells is too low

20. What is Chore Commit property?

  • It allows you to specify if the processes in chores will be committed as a single transaction or either as multiple transaction
    • In a single commit mode all processes are committed as a single transaction. It is default by nature.
    • In multiple commit modes any processes that need to be committed do so as they are processed.
    • It is possible to change chore property only when chore is INACTIVE.

21. Define snapshot?

Snapshot is nothing it’s only a copy of data, whenever we create any snapshot it only copy the exact data that is related to the particular report. It is used to compare reports.
For ex: we want to compare the report of this month with previous month.

22. Differentiate between view and materialized view?

Views
It contains query whenever execute views it has read from base tableMaterialized View
M views loading or replicated takes place only once which gives you better query performance.

23. Define Bulk Load mode?

  • It enables TM1 to run in a special optimized Single-user mode. This mode has a capacity to maximize the performance for dedicated tasks during the time when there is no load on server or at night time.
    • It doesn’t display a message to end-user to alert them. No new connections can be created.

24. What actually happens when Bulk Load mode starts?

  • All the scheduled chores will be deactivated
    • All the processing by other threads will be paused
    • Running chores and any of the existing user threads will be suspended
    • TM1 Top connections and all the system-specific threads will be suspended

25. Define Drill Through?

By using Drill-through reporting you can link from one report to another. There are number of ways to Drill Through:
Drill Through id developed with two reports• Parent Report
• Child Report

25.What is the difference between list and crosstab?

List
• List report show the data in row and column(detail information of report).
• List can be converted to Cross tab
Crosstab
• Crosstab report show the data in grids, dimensions in row and column and measures in cells or in intersection point.
• Crosstab can’t be converted to list. There is no direct option available in Report Studio to convert the cross tab to list.

26. Give the options which are available after importing data using TI?

  • Create Cube and populate data
    • Create and Update dimensions
    • Re-create Cube. It destroys the existing cube definitions and overwrites it

27. Define Prolog tab?

Procedure is executed before the data source for the TI is opened.  TI directly goes to Epilog if the data source for the process is none and if there is no data source then Metadata and data tabs are ignored.

28. Define Metadata?

Series of statements that update or create cubes, dimensions and other metadata structures during the processing

29. What is the cube size?

2.0 GB. It depends as per your project requirements.

30. What kind of system requirement is needed to run TM1 for a small installation?

The minimum requirement to run the sample TM1 servers provided will be:
• Disk Space: Minimum 500 MB
• Memory: Minimum 2 GB RAM
• Processor: Pentium 4 or higher
• OS: Windows 2003(x86, x64), Windows 2008(x86, x64), Windows 7(x86, x64), Linux

31.How many users can open or connect to one of the TM1 web application concurrently?

Using the distributed approach of TM1 10 they have done testing in thousands of users.

32.Define FEEDERS?

It creates a placeholder on the cells so that the cells will not be skipped during the consolidation.

33.What is the way of writing a FEEDER Statement when feeding one cube from another?

  • Calculation statement always resides in the target cube, but the FEEDER statement should reside in the source cube.
    • The Feeder is basically the inverse of the calculation statement in the Target Cube that requires the feeder.

34. What are the steps to troubleshoot the FEEDERS?

  • Use Rules Tracer will be used to assist in the development and debugging of rules.
  • The functionality of Rule Tracer is available in Cube Viewer

35. How does Rules Tracer help us?

  • It traces FEEDERS; it ensures that selected leaf cells are feeding rules-calculated cells properly or not.
    • It checks FEEDERS, ensures that the children of selected consolidated cells are fed properly or not. Check Feeders options is available from consolidated cells and it’s not available from leaf node.

36. Define the logic behind the Sparsity in cubes?

The more dimensions a cube has, the greater will be the degree of sparsity.

37. Define Over Feeding?

Over Feeding means defining feeders for consolidated cells. (Feeding a consolidated cell automatically feeds all children of the consolidation.)

38. Define Under Feeding?

Under Feeding means failing to the feed cells that contain rules-derived values. This always results in incorrect values and must be avoided at all costs.

39. What is the role of SKIPCHECK in TM1?

SKIPCHECK forces TM1 to use the Sparse Consolidation algorithm in all the cases.

40. What is the role of transformer?

It is used for building the Cubes (Multidimensional Structure used for OLAP processing).

41. Name the components of report studio?

  • Insertable Objects pane
  • Properties pane
  • Explorer bar
  •  Report Viewer

42. Name the components of Report Net?

  • Framework manager
  • Cognos connection
  • Query Studio
  • Report Studio

43. Name the prompt which are available in Cognos?

  • Value prompt
  • Text Prompt
  • Date prompt
  • Time prompt
  • Date and time prompt

44. Define Cognos Connection?

It is a Web portal for Cognos 8 and a component which interacts with the Content Store. It is a frontend to publish, find, manage, organize, and view organization’s business intelligence data.

45. Define Cognos Reporting tool?

It’s a reporting tool from IBM which helps in reporting and analysis of various data from a data warehouse.

46. Define Junk Dimension?

It is also called as garbage dimension. A junk dimension is a convenient grouping of typically low-cardinality flags and indicators.

47. Define is catalog and types of catalogs in Cognos?

Catalog is a file containing the information (Database tables) that Impromptu users need to create reports. Types of catalogs are:
• Personal
• Distributed
• Shared
• Secured

48. What are the flaws of Chore Start Time?

TM1 executes chores in GMT standards. It doesn’t have any auto mechanism to accommodate DayLight Saving. Its Chores Scheduled time should be edited when DayLight time begins and ends.

49. Name the different procedures within TI?

  • Defining Data Source
  • Setting Variables
  • Mapping Data
  • Editing Advanced Scripting
  • Scheduling the completed Process.

50. Name the window by which you can create your own custom scripts?

You can create your own custom scripts using Advanced Window

51. How you can enable Bulk Load Mode in TI?

  • In Prolog or Epilog section of TI, Bulk Load mode can be enabled.
    • It always recommended that Bulk Load Mode should be enabled in Prolog section.

52. Name the sub-tabs which are in advance tab of TI

The sub-tabs which are in advance tab of TI are:
• Prolog
• Metadata
• Data
• Epilog

53. Differentiate between Power play transformer and power play reports?

Power play transformer
It is an ‘MOLAP’ tool using which one can create multi dimensional structure called “CUBE”.Power play reports
Power play for reports is used to generate report from the cube. Only one report can be generated from one cube. If you want ‘n’ reports you must create ‘n’ cubes.

54. What are the different windows in Turbo Integrator?

There are different tabs available in Turbo Integrator

-Data source

-Variable

-Maps

-Advanced

-Schedule

55. What is the use of advanced window in Turbo Integrator?

Using the Advanced window you can create your own custom scripts.

56. What is TM1 Workflow. How do you create a process in that?

Workflow is helping for Wide deployment, Collaborative decision making, Continuous and real-time review and refinement and Monitoring of Key Performance Indicators

To create the work.

-Need to define the Views that you need to deploy to the  end users for Decision making.(Its basically Input View)

-You need to define Approval Hierarchy

-Need to deploy

-Security Assignments

57. What are the data sources available with TI?

-Comma delimited text files including ASCII files

-Relational databases using ODBC Connectivity

-Other Cubes and Views

-Microsoft Analysis Services

-SAP via RFC

-IBM Cognos Packages

58. Where is Portlet used?

Portlets are used in Cognos.

59. How do you use TM1 cubes in Framework Manager?

TM1 v10.1 has the interoperability function configured to allow packages to be published from TM1.

How to run Turbo Integrator Process from Command Line?

To initiate the TI process TM1RunTI is a command line interface tool.

60. Define Data Tab?

Series of statements that manipulates values for each record in data source.

61. What are sub-tabs in advance tab of TI?

-Prolog

-Metadata

-Data

-Epilog

62. What is the logic of Sparsity in cubes?

On average, the more dimensions a cube has, the greater the degree of sparsity.

63. What is Overfeeding

Defining feeders for for consolidated cells. (Feeding a consolidated cell automatically feeds all children of the consolidation.)

64. What is Under Feeding?

Failing to feed cells that contain rules-derived values. This always results in incorrect values and must be avoided at all costs.

65. How does SKIPCHECK helps TM1?

SKIPCHECK forces TM1 to use the Sparse Consolidation algorithm in all cases.

66. What are FEEDERS?

It creates a placeholder on cells so that cells will not be skipped during the consolidation

67. What is FEEDSTRING?

-If rule defines string values for any cells, then FEEDSTRINGS must be inserted as the first line of rule.

-FEEDSTRINGS declaration ensures that cells containing rules-derived strings are fed.

-Every calculation statement in a rule should have a corresponding feeder statement.

68. What are simple FEEDERS?

FEEDERS that are applied to calculation within one dimension of one cube.

69. Define TM1 package connector?

TM1 package connector helps to import data from packages/dimensions and custom queries.

70. What is Epilog Tab?

Series of statements that is to be executed after the data source is processed.

71. Name the function which is used to serialize the TI process?

Synchronized () function can is used to serialize TI process so they can be processed sequentially.

72. What are the functions required to be called for enabling or disabling the bulk load of mode?

-EnableBulkLoad Mode() = Enable

-Disable Bulk Load Mode() = Disable.

73. What is the limit of string length in Turbo Integrator?

The limit of string length in Turbo Integrator is 8000 single byte character. If the length will become more 8000 bytes then it gets truncated.

74. What are the flaws of Chore Start Time?

TM1 executes chores in GMT standards. It doesn’t have any auto mechanism to accommodate DayLight Saving. Its Chores Scheduled time should be edited when DayLight time begins and ends.

75. Name the different procedures within TI?

-Defining Data Source

-Setting Variables

-Mapping Data

-Editing Advanced Scripting

-Scheduling the completed Process.

76. How you can enable Bulk Load Mode in TI?

In Prolog or Epilog section of TI, Bulk Load mode can be enabled.

It always recommended that Bulk Load Mode should be enabled in Prolog section.

How we can generate cubes in Cognos?Powerplay Transformer contains dimension, measure, model and cube.

There are different ways to generate a cube.

Steps to be followed:

-Just right click on cube name and build.

-We can write script in UNIX. Using that we can easily generate the cube.

77. Name the data sources which are available with TI?

Data Sources available with TI are:

-SAP via RFC

-IBM Cognos Packages

-Microsoft Analysis Services

-Relational databases using ODBC Connectivity

-Comma delimited text files including ASCII files

-Other Cubes and Views

78. What kind of system requirement is needed to run TM1 for a small installation?

The minimum requirement to run the sample TM1 servers provided will be:

-Disk Space: Minimum 500 MB

-Memory: Minimum 2 GB RAM

-Processor: Pentium 4 or higher

-OS: Windows 2003(x86, x64), Windows 2008(x86, x64), Windows 7(x86, x64), Linux

79. Define snapshot?

Snapshot is nothing it’s only a copy of data, whenever we create any snapshot it only copy the exact data that is related to the particular report. It is used to compare reports.
For ex: we want to compare the report of this month with previous month.

80. Define Bulk Load mode?

-It enables TM1 to run in a special optimized Single-user mode. This mode has a capacity to maximize the performance for dedicated tasks during the time when there is no load on server or at night time.

-It doesn’t display a message to end-user to alert them. No new connections can be created.

81. How is the FEEDER Statement written when feeding one cube from another?

-Calculation statement resides in the target cube, but the FEEDER statement should reside in the source cube.

-The Feeder is basically the inverse of the calculation statement in the Target Cube that requires the feeder.

82. How to Troubleshoot the FEEDERS?

-Use Rules Tracer to assist in the development and debugging of rules.

-Rules Tracer functionality is available in Cube Viewer

83. How does Rules Tracer help?

-It traces FEEDERS, it ensures that selected leaf cells are feeding rules-calculated cells properly

-It checks FEEDERS, ensures that the children of selected consolidated cells are fed properly. Check Feeders options is available from consolidated cells and its not available from leaf node

84. What actually happens when Bulk Load mode starts?

-All the scheduled chores will be deactivated

-All the processing by other threads will be paused

-Running chores and any of the existing user threads will be suspended

-TM1 Top connections and all the system-specific threads will be suspended

85. Define Drill Through?

By using Drill-through reporting you can link from one report to another.

There are number of ways to Drill Through:

Drill Through is developed with two reports

-Parent Report

-Child Report

86. What is the difference between list and crosstab?

List

-List report show the data in row and column(detail information of report).

-List can be converted to Crosstab

Crosstab

-Crosstab report show the data in grids, dimensions in row and column and measures in cells or in intersection point.

-Crosstab can’t be converted to list. There is no direct option available in Report Studio to convert the cross tab to list.

87. Give the options which are available after importing data using TI?

Create Cube and populate data.

Create and Update dimensions.

Re-create Cube. It destroys the existing cube definitions and overwrites it.

88. Define Prolog tab?

Procedure is executed before the data source for the TI is opened. TI directly goes to Epilog if the data source for the process is none and if there is no data source then Metadata and data tabs are ignored.

89. What is the cube size?

2.0 GB. It depends as per your project requirements.

90. What are the steps to troubleshoot the FEEDERS?

-Use Rules Tracer will be used to assist in the development and debugging of rules.

-The functionality of Rule Tracer is available in Cube Viewer

91. Define the logic behind the Sparsity in cubes?

The more dimensions a cube has, the greater will be the degree of sparsity.

92. Name the components of report studio?

-Insertable Objects pane

-Properties pane

-Explorer bar

-Report Viewer

93. Name the components of Reportnet?• Framework manager?

-Cognos connection

-Query Studio

-Report Studio

94. Define Cognos Connection?

It is a Web portal for Cognos 8 and a component which interacts with the Content Store. It is a frontend to publish, find, manage, organize, and view organization’s business intelligence data.

95. Define Cognos Reporting tool?

It’s a reporting tool from IBM which helps in reporting and analysis of various data from a data warehouse.

96. Define is catalog and types of catalogs in Cognos?

Catalog is a file containing the information (Database tables) that Impromptu users need to create reports. Types of catalogs are:

-Personal

-Distributed

-Shared

-Secured

97. Define Query Subject?

A Query Subject is a collection of Query Items. Generally query items have a defined relationship. It is primarily a database table upon which query action takes place.

 

 

Business Objects Interview Questions

Top most important Business Objects interview questions and answers by Experts:

Here is a list of Top most important Business Objects interview questions and answers by Experts.If you want to download Business Objects interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Business Objects interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Business Objects ,Register for Business Objects online training here.

 

1) What is business objects? 
Business objects provides a set of tools for building data warehouses very efficiently. Business objects is an integrated query, reporting and analysis solution for business professionals that allow them to access the data in their corporate databases directly from their desktop and present and analyze this information in a business objects document. 

2) What is Designer? 
Designer is the Business objects product that is intended to develop the universes. These universe is the semantic layer of the database structure that isolates from technical issues of the database structure. 

3) How many modes are there in BO & Designer?  
There are two modes are there in BO and Designer are : 
Enterprise Mode
Workgroup Mode
4) What are the different types of data providers supported by BO? 
Queries on Universe 

Stored procedure
Free hand SQL
Text files or Spread sheets
VBA Procedures
OLAP servers
SAP 

5) What are the different multidimensional analysis methods available in Business Objects? 
  There are two multidimensional analysis methods available in BO, they are : 

Slice and Dice
Drill Down

6) What are the different @ Functions? 
@Aggregate_Aware 

@Prompt 
@Script 
@Select 
@Variable 
@Where 

7) What is the difference between @Prompt and @Script? 
The @Prompt function lets you create an interactive object. In the Query Panel, this type of object causes a message to appear. This message prompts the end user to enter a specific value.

This function recovers the results of Visual Basic for Applications macro (VBA macro). 

8) What is a context? 
A context is a rule by which determines which of two paths can be chosen when more than one path is possible in the database. 

9) What are the disadvantage of using contexts? 
The disadvantage of using context is that you expose the business objects end user to the database structure. They are forced to decide which context they want to use to run their query. The role of the universe is to shield end users from the database structure, so they do not have to make such decisions. 

10) What is BO? 
BO is an excellent tool for generation of reports, carrying out detailed analysis, graphical presentation of the results and generation of alerts. 

11) What are data integration tools? 
Data integration tools are used to extract data from existing databases, carry out the necessary transformations and load the data into the target database using a GUI. 

12) What are the components of data integrator? 
Data Integrator has four components are : 

Graphical designer : GUI to build and test ETL jobs for data cleansing, validation and auditing. 
Data integration server : to integrate data from different source databases.
Metadata repository : to keep source and target metadata as well as transformation rules.
Administrator : A web based tool can be used to start, stop, schedule and monitor ETL jobs. The ETL jobs can be in batch mode or real time mode.

13) What is a Business Objects Repository? 
The Business Objects repository is a centralized set of relational data structures stored on a database. This relational device enables Business objects and Web intelligence users to share resources in a controlled and secured environment.  
  
14) What are the types of business objects repository domain? 
The repository is made up of three domains are: 
The security domain
The universe domain
The document domain

15) What is BI platform? 
This platform provides a set of common services to deploy, use and manage the tools and applications. These services are security, broadcasting, collaboration, metadata and developer services.

16) What is BCA? 
BCA is a software product for the users of WEBI and BO to process and distribute documents automatically at the scheduled dates and time. 

17) What are the features in BCA? 
Automated document scheduling and publishing

On the server side, CORBA–enabled features such as load balancing and server optimization using caches
Tracing BROADCAST AGENT activity
Access and security

18) What is a chasm trap? 
Many to one joins from two fact tables converge on a single lookup table. This type of join convergence is called a Chasm trap.  

19) What is a fan trap? 
A one to many join links a table which is in turn linked by a one to many join. This type of fanning out of one to many joins is called a Fan trap. 

20) What is a Loop?  
In a relational database, a loop occurs when joins form multiple paths between lookup tables. if a query includes more than one path, the information returned can be incorrect. The rows that are returned are an intersection of the results for each path, so fewer rows are returned than expected. 

21) What is universe? 
A universe is a set of classes and objects intended for a specific application or group of users. 
22) What are the types of OLAP? 
ROLAP (Relational OLAP) 
MOLAP (Multidimensional OLAP) 
HOLAP (Hybrid OLAP)

23) What are the different schemas supported by BO Designer? 
Star Schema 

Snowflake Schema 
Multistar Schema 
Normalized production Schema 
Data warehouse with aggregates

24) What is the repository?  
The repository is the database in which the information about the users and data is present. It is created and maintained by the general supervisor. It is used to share resources of business objects. 

25) What are the types of repository domains?  
There are three types of repository domains 

Universe domain : It holds all the exported universes and there may be more than one universe domain in the repository.
Document domain : It holds all exported documents, templates, scripts, and lists of values.
Security domain : secured connections are stored in this. 

26) How is a fan trap solved? 
A Fan trap is solved in the following way : 

Creating an Alias and applying Aggregate Aware function.
Using Multiple SQL statements for each Measure.

27) What is user objects?  
User objects are end–user personal objects that are not shared with other end-users. This is because the user objects are stored locally in a specific user object definition file. If an end-user tries to refresh or edit query that contains another user’s user object, the user object is automatically removed from the query and report. A user object is stored inside the Universe folder. 

28) What are the various Business Objects products? 
User Module

Designer
Supervisor
Auditor
Set Analyzer
Info View (Web Intelligence)
Business Objects Software Development Kit (SDK)
Broadcast Agent

29) What are strategies?  
A strategy is a script that automatically extracts structural information from a database or flat file.  
30) What are the different types of Strategies? 
In Designer we can specify two types of strategies :
Built in strategies
External strategies

31) What are the types of users in Business Objects? 
General Supervisor

Supervisor
Designer 
Supervisor Designer
End User
Versatile User

32) What is Web Intelligence?  
Web Intelligence is a decision support solution designed to meet critical query, reporting and analysis needs over the World Wide Web and has features very similar to Business Objects.  

33) What are master/detail reports? 
Master/detail reports enable you to split large blocks of data into sections. This type of presentation allows you to avoid repeating values and also to display subtotals.  

34) What are calculation contexts? 
By default, BUSINESS OBJECTS determines the result of a measure based on the dimension or dimensions in the part of the report in which the measure is inserted. These sets of dimensions are called calculation contexts. Calculation contexts are dynamic in BUSINESS OBJECTS.  

35) What is Dimension? 
It is a structural attribute of a cube, which is used for multidimensional analysis.  

36) What is supervisor? 
Supervisor is the product is used to set up and maintain a secure environment for Business Objects products. 

37) What are the types of dimensions?  
There are four types of dimensions are : 

Informational Dimension 
Structural Dimension
Categorical Dimension
Partitioning Dimension 

38) What is a Data Provider? 
The data source (Query) is called as the data provider.  

39) What are the tasks of the universe designer? 
The responsibilities that universe designer has

Designing, Creating and maintaining the Universe 
Distributing the Created Universes

40) What are different types of parsing? 
There are two types of parsing are : 
Quick parsing
Thorough parsing

41) What is the use of Check integrity? 
Check Integrity can detect :

Invalid syntax in the SQL definition of an object, condition, or join.
Loops
Isolated tables
Isolated joins
Loops within contexts
Missing or incorrect cardinalities

42) What are the user profiles in Supervisor? 
SUPERVISOR offers several standard profiles for the various types of users of Business Objects products. 
General Supervisor 
Supervisor 
Designer
Supervisor-Designer 
User
Versatile 

43) What is a LOV? 
A list of values is a file that contains the data values associated with an object. Lists of values can reveal information about the contents of a database. 

44) What is a category in supervisor? 
A category is a keyword or phrase end-users can assign to documents when they send them to users, groups, or BROADCAST AGENT.  

45) What are the disadvantages of using a Alias? 
When we create Alias, it creates a new/extra table and this increases the number of tables in the universe, and this can be a problem, if performance is a issue 

46) What are the types of connection available in Designer? 
The database connection is categorized into three types : 

Personal 
Shared 
Ssecured 

47) Explain personal, shared and secured? 
Personal : The user and the universes, which are created using the personal connection belongs to that computer and can be accessed from that computer only, create it.

Shared : In shared type of connection we can use the common resources such as universes and documents. This can be used by several users. The parameters are stored locally in locdata folder with name sdac.lsi or sdac.ssi. This shared type of connection is used to setup in testing environment before publishing them.
Secured : In secured type of connection we can centralize and control access to secure the data. The secured connections are stored in security domain and are distributed to all the designers and supervisors who have the appropriate privileges. 

48) When do u use a Context? 
When you have dimension objects in one or both fact tables, you should always use a context. 

49) What is symbolic objects? 
Objects whose values are text are referred to as Symbolic objects.  

50) What is data mining? 
Data Mining is the analysis of data and use of software techniques for extracting hidden, comprehensible, and useful information from databases. 

51) When do u use Multiple Universes? 
You can use multiple universes to solve a Chasm trap in a WEBINTELLIGENCE universe when Multiple SQL for Measures cannot be used as there are dimension objects defined for one or both of the fact tables. 

52) What are the different methods by which we can link the universes? 
There are three approaches available for linking universes : 

The kernel approach 
The master approach 
The component approach 

53) Explain kernel approach, master approach and component approach? 
Kernel Approach : With the kernel approach, one universe contains the core components. These are the components common in all universes. The derived universes that you create from this kernel universe contain these core components as well as their own specific components.

Master Approach : In this approach, one master universe holds all possible components. In the universes derived from this master, certain components are hidden; in other words, the components visible in the derived universes are a subset of the master universe.
Component Approach : It involves merging two or more universes into one universe.

54) What is a report bursting? 
Report bursting allows you to maintain the version documents based on the user profiles. 
Post Your Answers Your Name Your Email-ID 
  
55) What is aggregate awareness? 
Aggregate awareness is a feature of DESIGNER that makes use of aggregate tables in a database. These are tables that contain pre-calculated data. The purpose of these tables is to enhance the performance of SQL transactions; they are thus used to speed up the execution of queries. 

56) What is metadata? 
Metadata is defined as the data about data. Metadata describes the entity and attributes description. 

57) What is a Batch? 
Batches provide a way to group sessions for either serial or parallel execution by the Informatica Server.  

58) Define Cubes? 
A cube consists of a set of ordered dimensions and measures. A cube may have one or several dimensions. 

59) What is surrogate key? 
A system generated artificial primary key that is not derived from any data in the database. It is similar to the object identifier in an object oriented system. 

60) What is slice and dice? 
An operation that allows one to select a portion of the data of a fact on the basis of specified values in one or several dimensions. 

61) What are the benefits of linked universe? 
A dynamic link may considerably reduce development and maintenance time. When you modify a component in the kernel universe, DESIGNER propagates the change to the same component in all the derived universes.
Instead of re–creating common components each time you create a new universe, you can centralize such components in a kernel universe, and then include them in all new universes.
Linked universes facilitate specialization. Development can be split between database administrators who set up a basic kernel universe, and the more specialized designers who create more functional universes based on their specific field.

Can Dimension and Measure objects both be used for Drill down analysis? 
NO, Measure object cannot be used in Drill down analysis. 

62) What is drill down? 
The drill down operation performs the operation opposite to the roll–up operation, i.e., it moves from a more general level to a detailed level in a hierarchy, thus presenting more detailed measures.  

63) What is drill across? 
The drill across operation executes queries involving more than one cube. 

64) What is drill through? 
The drill through operation allows one to move from data at the bottom level in a cube to data in the operational systems from which the cube was derived. 

65) What is the default qualification of a object? 
By default, DESIGNER automatically assigns a dimension qualification to an object you create. 

66) What are Lookup and Fact Tables? 
A lookup (Dimension) table contains information about the entities. In general the Dimension and details objects are derived from lookup tables. A fact table contains the statistical information about transactions. 

67) What is slice? 
The slice operation performs a selection on one dimension of a cube,resulting in a subcube. 

68) What is roll up? 
The roll up operation transforms detailed measures into summarized ones. This is done when on moves up in a hierarchy or reduces a dimension. 

69) What is materialized view? 
A view which is physically stored in a database. Materialized views allow query performance to be enhanced by precalculating costly operations. 

70) What are the main components of the Designer Interface? 
The main components that interact in the designing of universe are 

Table browser : The table browser is used to choose the tables and columns.
The Structure pane : The Structure pane refers the database structure of the universe.
The Universe pane : The Universe pane refers the components in which it is going to use in BO or WEBI documents. 

71) Does Cardinality have any effect on the generation of the query? 
Cardinality has no effect in the generation of SQL. Its sole purpose is to provide enough information to DESIGNER so that it can detect and resolve loops. 

72) What are new in BO 5.1? 
Save business objects documents in Adobe Portable Document Format (PDF).

Add hyperlinks to reports such as mail addresses and web site addresses. These hyperlinks are maintained when you save the report in HTML or PDF.
Zero Administration : Only the business objects client software installed on the windows pc; all middleware is stored and administered on the web intelligence web server and the server side processing is handled by the web intelligence system.

73) What is a class? 
A class is a collection of objects with in a universe. A class can further divided into subclasses. Using this classes and subclasses we can define the hierarchy. 

74) What is Drill up/down/by/through? 
Drill mode allows you to analyze data from different angles and on different levels of detail. 

Drill down displays next level of detail in hierarchy.
Drill up goes back up through the hierarchy to display data on less detailed levels. 
By using Drill by option you can move to another hierarchy to analyze other data that belongs to a different hierarchy.
Drill Through – If the lowest level of detail you need is not currently available in the report, you can drill through to the database directly from drill mode and get the data you need. You do not have to edit the query in the Query Panel.

75) What are the ways by which you can distribute the Universe? 
There are two ways to distribute a universe: through the repository or through the file system . 

76) What are enterprise and workgroup modes? 
Enterprise mode means that you are working in an environment with a repository. Workgroup mode means that you are working without a repository. The mode in which you save your universe determines whether other designers are able to access them. By default, a universe is saved in the mode in which you are already working. For example, if you launched a session in enterprise mode, any universe you save is automatically in that mode.  

77) What is a macro? 
A macro is a series of commands and functions that are stored in a Visual Basic for Applications module and can be run whenever you need to perform the task. If you perform a task repeatedly, you can automate the task with a macro. You create macros using the Visual Basic Editor. 

78) Is a default lov assigned to a Measure object? 
No default list of values is assigned to measure objects. 

79) What is aggregate aware? 
Aggregate awareness is a feature of DESIGNER that makes use of aggregate tables in a database. These are tables that contain precalculated data. The purpose of these tables is to enhance the performance of SQL transactions; they are thus used to speed up the execution of queries. 

80) What is the syntax of a Aggregate Aware function? 
The syntax of the @Aggregate_Aware function is as follows : @Aggregate_Aware(sum(agg_table_1), … sum(agg_table_n)) where agg_table_1 is the table with the highest level of aggregation, and agg_table_n the table with the lowest level. 

81) Is Universe MOLAP or OLAP? 
MOLAP – Multidimensional Online Analytical Processing.  

82) What is intelligent binning? 
A powerful BUSINESSMINER feature which automatically and intelligently divides numeric values into “bins” based on the range, values and distribution of the data. 

83) What is an add-in? 
Add-ins are programs that add optional commands and features to BUSINESS OBJECTS. Add-ins are usually created by those responsible in your company for adding customized features to BUSINESS OBJECTS. All you probably need to do is install and uninstall add-ins that are sent to you. 

84) Can a Universe have more than one fact Table? 
Yes. Typically a universe can have more than one fact table and numerous aggregated tables. 

85) What is the difference between built in strategies and external strategies? 
Built in Strategies : Designer provides a number of default strategies which we can use. These are strategies for extracting joins, detecting cardinalities, and creating default classes and objects. Options for indicating default strategies are located in the Database tab of the Options dialog box.

External Strategies : We can also create our own strategies. Such strategies are referred to as external strategies. With an external strategy, we can specify the exact way that objects and joins are to be extracted from the database structure. The strategy we use, for example, can be a script generated from a CASE Access tool. An external strategy is specific to one RDBMS.

86) Is Universe MOLAP or OLAP? 
MOLAP – Multidimensional Online Analytical Processing.  

87) What is intelligent binning? 
A powerful BUSINESSMINER feature which automatically and intelligently divides numeric values into “bins” based on the range, values and distribution of the data.
88) What is an add-in? 
Add-ins are programs that add optional commands and features to BUSINESS OBJECTS. Add-ins are usually created by those responsible in your company for adding customized features to BUSINESS OBJECTS. All you probably need to do is install and uninstall add-ins that are sent to you. 

89) Can a Universe have more than one fact Table? 
Yes. Typically a universe can have more than one fact table and numerous aggregated tables. 

90) What are the different schemas supported by BO Designer? 
Star Schema 

Snowflake Schema 
Multistar Schema 
Normalized production Schema 
Data warehouse with aggregates 

91) What is a join? 
Join combines columns and data from two or more tables. Join is the relation that occurs between the two tables that helps to combine into one table. The purpose of join is that to restrict the Cartesian product.  

92) How many types of join are available? 
Designer supports the following types of joins : 

equi joins
outer joins
theta joins
shortcut joins

93) What is theta join? 
A join is a relational operation that causes two or more tables with a common domain to be combined into a single table. The purpose of joins is to restrict the result set of a query run against multiple tables. 
How do you determine that you are working on the latest edition of the Universe? 
Each time you export a universe to a universe domain, DESIGNER increments the revision number of the universe. In this way, you can determine which is the latest version of the universe. 

94) What are the resources that are managed by supervisor? 
Business objects products 

Universes
Documents
Repository domains
Stored Procedure

95) What are linked universe? 
Linked universes are universes that share common components such as parameters, classes, objects, or joins. Among linked universes, one universe is said to be the kernel or master universe while the others are the derived universes. 

96) What are two modes for importing users & groups from supervisor? 
Batch Mode : The purpose of batch mode is to automate user and group import operations with no intervention from the interface.
Interactive Mode : Dialog boxes will appear as the file is imported to allow you to confirm the information. This is the default mode.

97) What is the difference between rolap and molap? 
ROLAP (Relational OLAP) : Users see their data organized in cubes and dimensions but the data is really stored in RDBMS. The performance is slow. A storage mode that uses tables in a relational database to store multidimensional structures.

MOLAP (Multidimensional OLAP) : Users see their data organized in cubes and dimensions but the data is really stored in MDBMS. Query performance is fast.  
What types of tables? 
BUSINESSOBJECTS has three types of table. It has two basic table types that display data in a list and crosstabs, which are a special kind of table that allows you to summarize data.
Tables
Crosstab tables
Free Standing Cells

 

Teradata Interview Questions

Top most important Teradata interview questions and answers by Experts:

Here is a list of Top most important Teradata interview questions and answers by Experts.If you want to download Teradata interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Teradata interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Teradata ,Register for Teradata online training here.

 

1) What is meant by a Parsing Engine?

PE happens to be a kind Vproc. Its primary function is to take SQL requests and deliver responses in SQL. It consists of a wide array of software components that are used to break SQL into various steps and then send those steps to AMPs.

2) What do you mean by parsing?

Parsing is a process concerned with analysis of symbols of string that are either in computer language or in natural language.

3) What are the functions of a Parser?

A Parser: –

  • Checks semantics errors
  • Checks syntactical errors
  • Checks object existence

4) What is meant by a dispatcher?

Dispatcher takes a whole collection of requests and then keeps them stored in a queue. The same queue is being kept throughout the process in order to deliver multiple sets of responses.

5) How many sessions of MAX is PE capable of handling at a particular time?

PE can handle a total of 120 sessions at a particular point of time.

6) Explain BYNET.

BYNET basically serves as a medium of communication between the components. It is primarily responsible for sending messages and also responsible for performing merging, as well as sorting operations.

7) What is meant by a Clique?

A Clique is basically known to be an assortment of nodes that is being shared amongst common disk drives. Presence of Clique is immensely important since it helps in avoiding node failures.

8) What happens when a node suffers a downfall?

Whenever there is a downfall in the performance level of a node, all the corresponding Vprocs immediately migrate to a new node from the fail node in order to get all the data back from common drives.

9) List out all forms of LOCKS that are available in Teradata.

There are basically four types of LOCKS that fall under Teradata. These are: –

  • Read Lock
  • Access Lock
  • Exclusive Lock
  • Write Lock

10) What is the particular designated level at which a LOCK is liable to be applied in Teradata?

  • Table Level – All the rows that are present inside a table will certainly be locked.
  • Database Level Lock – All the objects that are present inside the database will be locked.
  • Row Hash Level Lock – Only those rows will be locked which are corresponding to the particular row.

11) In the Primary Index, what is the score of AMPs that are actively involved?

Only one AMP is actively involved in a Primary Index.

12) In Teradata, what is the significance of UPSERT command?

UPSERT basically stands for Update Else Insert. This option is available only in Teradata.

13) Highlight the advantages of PPI(Partition Primary Index).

PPI is basically used for Range-based or Category-based data storage purposes. When it comes to Range queries, there is no need of Full table scan utilization as it straightaway moves to the consequent partition thus skipping all the other partitions.

14) Give the sizes of SMALLINT, BYTEINT and INTEGER.

SMALLINT – 2 Bytes – 16 Bites -> -32768 to 32767

BYTEINT – 1 Bytes – 8 Bits -> -128 to 127

INTEGER – 4 Bytes – 32 Bits -> -2,147,483,648 to 2,147,483,647

15) What is meant by a Least Cost Plan?

A Least Cost Plan basically executes in less time across the shortest path.

16) Highlight the points of differences between the database and user in Teradata.

  • A database is basically passive, whereas a user is active.
  • A database primarily stores all the objects of database, whereas a user can store any object whether that is a macro, table, view, etc.
  • Database does not has password while the user has to enter password.

17) Highlight the differences between Primary Key and Primary Index.

  • Primary index is quite mandatory, whereas Primary Key is optional.
  • Primary Index has a limit of 64 tables/columns, whereas Primary Key does not have any limit.
  • Primary Index allows duplicates and nulls, whereas Primary Key doesn’t.
  • Primary Index is a physical mechanism, whereas Primary Key is purely logical mechanism.

18) Explain how spool space is used.

Spool space in Teradata is basically used for running queries. Out of the total space that is available in Teradata, 20% of the space is basically allocated to spool space.

19) Highlight the need for Performance Tuning.

Performance tuning in Teradata is basically done to identify all the bottlenecks and then resolve them.

20) Comment whether bottleneck is an error or not.

Technically, bottleneck is not a form of error, but it certainly causes a certain amount of delay in the system.

21) How can bottlenecks be identified?

There are basically four ways of identifying a bottleneck. These are: –

  • Teradata Visual Explain
  • Explain Request Modifier
  • Teradata Manager
  • Performance Monitor

22) What is meant by a Highest Cost Plan?

As per Highest Cost Plan, the time taken to execute the process is more, and it takes the longest path available.

23) Highlight all the modes that are present under Confidence Level.

Low, No, High and Join are the four modes that are present under Confidence Level.

24) Name the five phases that come under MultiLoad Utility.

Preliminary Phase, DML Phase, Data Acquisition Phase, Application Phase and End Phase.

25) Highlight the limitations of TPUMP Utility.

Following are the limitations of TPUMP utility: –

  • We cannot use SELECT statement.
  • Data Files cannot be concatenated.
  • Aggregate and Exponential operators are not supported.
  • Arithmetic functions cannot be supported.

26) In BTEQ, how are the session-mode parameters being set?

.set session transaction BTET -> Teradata transaction mode

.set session transaction ANSI -> ANSI mode

These commands will work only when they are entered before logging into the session.

27) How do you define Teradata? Give some of the primary characteristics of the same.

Teradata is basically an RDMS which is used to drive the Datamart, Datawarehouse, OLAP, OLTP, as well as DSS Appliances of the company. Some of the primary characteristics of Teradata are given below.

  • Is capable of running on Single-nodes, as well as multi-nodes.
  • Parallelism is built into the system.
  • Very much compatible with the standards of ANSI.
  • Tends to act in the same way as a server.
  • It is an Open System that basically executes for UNIX MR-RAS, Suse Linux ETC, WIN2K, etc.

28) What are the newly developed features of Teradata?

Some of the newly developed features of Teradata are: –

  • Automated temporal analytics
  • Extension in the compression capabilities which allows flexible compression of data about 20 times more data than the previous version.
  • Customer associated innovation like tetradata viewpoint.

29) Highlight a few of the important components of Teradata.

Some of the important components of Teradata are: –

  • Bynet
  • Access Module Processor (AMP)
  • Parsing Engine (PE)
  • Virtual Disk (vDisk)
  • Virtual Storage System (VSS)

30) Mention the procedure via which, we can run Teradata jobs in a UNIX environment.

All you have to do is perform execution in UNIX in the way as mentioned below.

$Sh > BTEQ < [Script Path] > [Logfile Path]
or
$Sh > BTEQ < [Script Path] TEE [Logfile Path]

31) In Teradata, how do we Generate Sequence?

In Teradata, we Generate Sequence by making use of Identity Column

32) During the Display time, how is the sequence generated by Teradata?

All you have to do is use CSUM.

33) A certain load is being imposed on the table and that too, every hour. The traffic in the morning is relatively low, and that of the night is very high. As per this situation, which is the most advisable utility and how is that utility supposed to be loaded?

The most suggestible utility here has to be Tpump. By making use of packet size decreasing or increasing, the traffic can be easily handled.

34) If Fast Load Script fails and only the error tables are made available to you, then how will you restart?

There are basically two ways of restarting in this case.

  • Making the old file to run– Make sure that you do not completely drop the error tables. Instead, try to rectify the errors that are present in the script or the file and then execute again.
  • Running a new file– In this process, the script is executed simply using end loading and beginning statements. This will help in removing the lock that has been put up on the target table and might also remove the given record from the fast-log table. Once this is done, you are free to run the whole script once again.

35) Mention a few of the ETL tools that come under Teradata.

Some of the ETL tools which are commonly used in Teradata are DataStage, Informatica, SSIS, etc.

36) Highlight a few of the advantages that ETL tools have over TD.

Some of the advantages that ETL tools have over TD are: –

  • Multiple heterogeneous destinations, as well as sources can be operated.
  • Debugging process is much easier with the help of ETL tools owing to full-fledged GUI support.
  • Components of ETL tools can be easily reused, and as a result, if there is an update to the main server, then all the corresponding applications connected to the server are updated automatically.
  • De-pivoting and pivoting can be easily done using ETL tools.

37) What is the meaning of Caching in Teradata?

Caching is considered as an added advantage of using Teradata as it primarily works with the source which stays in the same order i.e. does not change on a frequent basis. At times, Cache is usually shared amongst applications.

38) How can we check the version of Teradata that we are using currently?

Just give the command .SHOW VERSION.

39) Give a justifiable reason why Multi-load supports NUSI instead of USI.

The index sub-table row happens to be on the same Amp in the same way as the data row in NUSI. Thus, each Amp is operated separately and in a parallel manner.

40) How is MLOAD Client System restarted after execution?

The script has to be submitted manually so that it can easily load the data from the checkpoint that comes last.

41) How is MLOAD Teradata Server restarted after execution?

The process is basically carried out from the last known checkpoint, and once the data has been carried out after execution of MLOAD script, the server is restarted.

42) What is meant by a node?

A node basically is termed as an assortment of components of hardware and software. Usually a server is referred to as a node.

43) Let us say there is a file that consists of 100 records out of which we need to skip the first and the last 20 records. What will the code snippet?

We need to use BTEQ Utility in order to do this task. Skip 20, as well as Repeat 60 will be used in the script.

44) Explain PDE.

PDE basically stands for Parallel Data Extension. PDE basically happens to be an interface layer of software present above the operation system and gives the database a chance to operate in a parallel milieu.

45) What is TPD?

TPD basically stands for Trusted Parallel Database, and it basically works under PDE. Teradata happens to be a database that primarily works under PDE. This is the reason why Teradata is usually referred to as Trusted Parallel or Pure Parallel database.

46) What is meant by a Channel Driver?

A channel driver is software that acts as a medium of communication between PEs and all the applications that are running on channels which are attached to the clients.

47) What is meant by Teradata Gateway?

Just like channel driver, Teradata Gateway acts as a medium of communication between the Parse Engine and applications that are attached to network clients. Only one Gateway is assigned per node.

48) What is meant by a Virtual Disk?

Virtual Disk is basically a compilation of a whole array of cylinders which are physical disks. It is sometimes referred to as disk Array.

49) Explain the meaning of Amp?

Amp basically stands for Access Module Processor and happens to be a processor working virtually and is basically used for managing a single portion of the database. This particular portion of database cannot be shared by any other Amp. Thus, this form of architecture is commonly referred to as shared-nothing architecture.

50) What does Amp contain and what are all the operations that it performs?

Amp basically consists of a Database Manager Subsystem and is capable of performing the operations mentioned below.

  • Performing DML
  • Performing DDL
  • Implementing Aggregations and Joins.
  • Releasing and applying locks, etc.

51) What is meant by a Parsing Engine?

PE happens to be a kind Vproc. Its primary function is to take SQL requests and deliver responses in SQL. It consists of a wide array of software components that are used to break SQL into various steps and then send those steps to AMPs.

 

 

Qlikview Interview Questions

Top most important Qlikview interview questions and answers by Experts:

Here is a list of Top most important Qlikview interview questions and answers by Experts.If you want to download Qlikview interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Qlikview interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Qlikview ,Register for Qlikview online training here.

 

1)Optimized and unoptimized QVD Load Situations?

Loading the data without performing any transformations is unoptimized load. Loading the data with some transformations is Optimized load. Ex : Load * from tab1 where Year = 2014;

2) Explain 3 tier architecture implementation while deploying QlikView application?

In First Tier: Data is loaded from the Database and stored in QVDs.

In Second Tier: Data is loaded from QVDs and tranform the data based on business needs.

In Third Tier: Using the Binary load the Qlikview file created in Tier II is loaded in to the Qlikview file and all chart objects are created in this Tier.

3) Briefly explain how does QlikView storage the data internally?

data is binary format

4)What are the restrictions of Binary Load?

Binary load should be used only once in qvw application and it should be the first line in script editor page.

5) How are NULLS implemented in QlikView?

The way in which QlikView’s associative engine works means Nulls don’t really exist in the data it holds, a Null is simply an absence of a data item.For example if a field value is missing we can’t make selection on empty list box. We can replace these null values with our required values(NA,0, etc) .

6) How do you optimize QlikView Application? (What tools are used and where do you start?

For Optimization we can optimized the QVD which is used in the QVW files in the backend. For QVW optimization i believe not use any tools we can manually remove unwanted fields form data model or comment it, remove most complex logics from the load script or create it with the simple way because it will take long time for loading. If using large data base file try to create the incremental load for the QVD or load latest data only.

7) What is the difference between Subset ratio & Information Density?

Information Density: is the number of records that have values (i.e. not NULL) in this field as compared to the total number of records in the table.

SubSet Ratio:Subset ratio, which shows the percentage of all distinct values for a field in the table compared to all the distinct values for that field in the entire data model.

8) What is the difference between ODBC, OLE DB & JDBC?

JDBC is Java Data Base Connectivity .
ODBC is Open Data Base Connectivity .
Differences :
ODBC is from Microsoft .
JDBC is standard java interface to communicate with a database.
Both are used to connect with the database.
ODBC can’t be directly used with Java because it uses a C interface.
ODBC makes use of pointers which have been removed totally from java.
ODBC mixes simple and advanced features together and has complex options for simple queries.But JDBC is simple which allows advanced capabilities when required.

JDBC drivers are written in java and JDBC code is automatically installable, secure, and portable on all platforms.

JDBC API is a Java Interface and is built on ODBC.
JDBC retains some of the basic feature of ODBC.

9) What is the use of Crosstable prefix in QlikView Load Script?

Incremental load is a very common task in relation to data bases. It is defined as loading nothing but new or changed records from the database. All other data should already be available, in one way or another. With QVD Files it is possible to perform incremental load in most cases. The basic process is described below:

Load the new data from Database table (a slow process, but loading a limited number of records).

Load the old data from QVD file (loading many records, but a much faster process).

Create a new QVD file.

Repeat the procedure for every table loaded.

The following basic cases can be identified:

1) Case 1: Append Only (typically log files)

2) Case 2: Insert Only (No Update or Delete)

3) Case 3: Insert and Update (No Delete)

4) Case 4: Insert, Update and Delete

10) What is Mapping Load & ApplyMap() ?

Mapping Load: The mapping prefix is used on a load or select statement to create a mapping table, these tables are different from other tables. They will be stored in separate area of memory and used as mapping tables during script execution. After execution they will automatically drop.

Ex. Mapping (Load Statement/Select Statement)

Apply Map: used for mapping data from one source to another at load time.

Ex. Applymap(‘Mapname’, expr,[default expr])

11) Synthetic Keys in QlikView and how & when to avoid them?

In two or more tables more than one common field is there synthetics keys will be formed: we can remove in the following ways 1.commenting 2.rename 3.join 4.Qulify 5.linktable 6.concatenation

12) Explain QlikView Resident Load?

Resident Load:- It’s a part of loading data into qlikview application.
It means, loading data (one or more fields) from a table (which is already loaded into qlikview application).

Syntax:- Load
Field_1
Field_2

Resident <Table_Name>;

13) What is initial load and incremental load?

Initial load means, when ever the target table is empty on that time we load from starting year data to up to this day data we are loading into the target like facts or dataware house or datamarts.This is initial loading.

Incremental loading means just coming data that means today data that data load into target table this is called as incremental loading(already previous data is loading into targets then just now coming data is loading into target table that means updated data)it takes loading time is 3 to 4 hours.

Another difference that the initial load jobs were set to first truncate the tables and then do a complete load and the incremental load was set to insert new rows and update existing rows for DIMENSION Tables. Facts jobs were same (truncate and complete load).

14) What is Incremental Load in qlikview?

Incremental load is a very common task in relation to data bases. It is defined as loading nothing but new or changed records from the database. All other data should already be available, in one way or another. With QVD Files it is possible to perform incremental load in most cases. The basic process is described below:

Load the new data from Database table (a slow process, but loading a limited number of records).

Load the old data from QVD file (loading many records, but a much faster process).

Create a new QVD file.

Repeat the procedure for every table loaded.

The following basic cases can be identified:

1) Case 1: Append Only (typically log files)

2) Case 2: Insert Only (No Update or Delete)

3) Case 3: Insert and Update (No Delete)

4) Case 4: Insert, Update and Delete

15) Explain Aggr Function?

Think of the aggr() function as making a temporary table. For instance, let’s say we have this real table of data:

Customer, Value
A, 20
A, 30
B, 10
B, 15
C, 40

Now, as a nonsense requirement, let’s say that we’re looking for maximum value by customer, and then the minimum of THAT value across customers. Our max value by customer is A=30, B=15, C=40. Our min of those is 15. Note how we built a temporary table with three rows while calculating it. Aggr() is one way to build this temporary table. Our dimension is Customer, and our expression is max(Value). We could build this as a real chart, but instead we want to use it in an expression without doing so. The general format for aggr() is this:

aggr(expression, dimensions)

So in our case:

aggr(max(Value),Customer)

We’re trying to extract the minimum from that temporary table, that list of values, so we just enclose it in min():

min(aggr(max(Value),Customer))

16) What is the use of FirstSortValue in QlikView?

SET or a LET statement is often used To define the variable. The SET statement is used when you want a variable to hold the string or numeric value that is to the right of the Equal (=) sign.

The LET statement is used when you need to evaluate what is to the right of the Equal sign

e.g

set myVar=52 the result is “52″

Let myVar=5*2 the result is “10″

17) Explain Qlikview architecture?

QlikView deployments have three main infrastructure components:

1.QlikView Developer : Is a Windows-based desktop tool that is used by designers and developers to create

1) a data extract and transformation model and

2) to create the graphical user interface (or presentation layer).

2.QlikView Server (QVS) : Handles the communication between clients and the QlikView applications. It loads QlikView applications into memory and calculates and presents user selections in real time.

3.QlikView Publisher: Loads data from different data sources (oledb/odbc, xml, xls), reduces the QlikView application and distributes to a QVS.

Because QlikView Server and Publisher have different roles and handle CPU and memory differently it’s considered a best practice to separate these two components on different servers.

18) Set analysis in qlikview ?

Used for set of groups .. mostly used in arrgeted function like sum (year) used sales of current year VS last year.

19) What is Synthetic key and how to avoid it in QlikView?

It is undesirable to have multiple common keys across multiple tables in a QlikView data structure. This may cause QlikView to use complex keys (a.k.a. synthetic keys) to generate the connections in the data structure. Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload anapplication. They also make a document harder to understand and maintain. Thereare some cases where synthetic keys cannot be avoided (e.g. Interval Match tables),but, in general, synthetic keys should always be eliminated, if possible.

comment the fileds in load script
rename the fileds in load script
rename the fileds using UNQUALIFY operator;
20) Difference between keep and joins ?

Left Keep and Left join gives the same output. The only difference is that Left Keep keeps the Table separate in Data Model, whereas Left join merges the Tables that are joined.

21) Difference between Straight table and pivot table ?

Pivot Table –

1) A pivot table is better at grouping: you can easily see which group a specific row belongs to, and a group can have a subtotal.

2) You can also display a pivot table like a cross table (one or several horizontal dimensions).

3) But when you sort a pivot table, you have to sort it first according to the first dimension, then according to the next, etc.

You cannot sort it any way you want.

Straight Table-

A straight table is better at sorting than a pivot table: you can sort it according to any column.

But it is not so good at grouping. Subtotals are not possible, for instance.

22) Which graph will you used for two years difference sale ?

BAR Graph

23) What is Incremental Load in qlikview?

As BI apps are expected to deal with larger and larger amounts of data the amount of time that it takes to retrieve that data becomes a serious issue. This could be due to shear volume of data or the need for frequent refreshes. Either way, you do not want to be pulling all of the data all of the time. What you want to be able to do is just pull the data that has changed, append to that the data that you stored away previously and then get back to the business of analyzing. This will reduce load on the source database, the network infrastructure and your QlikView server.

24) Whta is Inline memory in QlikView ?

Create table or add field to table .

25) what is Set and let in QlikView and difference between it?

SET or a LET statement is often usedTo define the variable. The SET statement is used when you want a variableto hold the string or numeric value that is to the right of the Equal (=) sign.

The LET statement is used when you need to evaluate what is to the right of the Equal sign

e.g

set myVar=5*2 the result is “5*2″

Let myVar=5*2 the result is “10″

26) Explain QlikView Resident Load?

Create a new logical table in QlikView, based on a previously loaded (resident) table.

27) What is Apply Map (Mapping Tables)?

Sometimes you need to add an extra field to a table to use a combination of fields

from different tables, or you want to add a field to clean up the data structure. Qlik- View has an effective way to add single fields to a table called mapping tables. syntax — mapping ( load statement | select statement )

applymap( ‘mapname’, expr, [ , defaultexpr ] )

28) What is Dimensions ( What is difference between Expression and Dimension)?

Each data warehouse consists of dimensions and measures. Dimensions allow data analysis from various perspectives. For example, time dimension could show you the breakdown of sales by year, quarter, month, day and hour. Product dimension could help you see which products bring in the most revenue. Supplier dimension could help you choose those business partners who always deliver their goods on time. Customer dimension could help you pick the strategic set of consumers to whom you’d like to extend your very special offers.

29) Explain about Normalized Data?

Well Structured Form of Data, which doesnt have any repetition or redundancy of data. Its a kind of Relational data. Its mainly used in OLTP kind of stuffs Denormalized Data – Its a whole bunch of data without any relationship among themselves, with redundancy of data. Its mainly used in OLAP kind of stuffs.

30) What Is Star Sechma ?

A star schema is the simplest form of dimensional model, in which data is organized into facts and dimensions. A fact is an event that is counted or measured, such as a sale or login. A dimension contains reference information about the fact, such as date, product, or customer. A star schema is diagramed by surrounding each fact table with its associated dimensions table. The output diagram resembles a star.

Star Schema Definition : A means of aggregating data based on a set of known dimensions. It stores data multidimensionality in a two dimensional Relational Database Management System (RDBMS), such as Oracle.

31) What is Snowflaking Schema ?

Snowflake Schema: An extension of the star schema by means of applying additional dimensions to the Dimensions of a star schema in a relational environment. Snowflaking is a form of dimensional modeling; dimensions are stored in multiple relational dimension tables. A snowflake schema is a variation of the star schema. Snowflaking is used to improve the performance of specific queries. The schema is diagramed with each fact surrounded by its associated dimensions as in a star schema, and those dimensions are further related to other dimensions, branching out into a snowflake pattern.

32) What is Central Link Table?

In the event of multiple fact tables QlikView In-Memory Technology allows us to create a central link table that only contains the existing data combinations. Instead of Joining the tables the event dimensions can be merged (CONCATENATED) in to one central Link table. This link table can then be linked back to the event measures one side and the dimension tables on the other.

33) What is binary load ?

Binary load is loading data from another QV file. For example, you have application A.qvw. You can create another application B.qvw with script binary A.qvw. binary file where:

file ::= [ path ] filename

Examples:

Binary customer.qvw;

Binary c:\qv\customer.qvw;

The path is the path to the file, either absolute, or relative to the .qvw file containing this script line.

34) What is Container ?

A container object can be used to hold multiple charts. You can use a container object to put multiple charts in the same box. All charts will appear in the same window but only one chart will appear active at a given time. You can click the chart title to switch or toggle between charts. A word of advice: Use containers with caution. They form linked objects and might affect the properties of all linked objects.

 

 

Cognos Interview Questions

Top most important Cognos interview questions and answers by Experts:

Here is a list of Top most important Cognos interview questions and answers by Experts.If you want to download Cognos interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Cognos interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Cognos ,Register for Cognos online training here.

 

 

1)  What is a Data Warehouse?

  A Data Warehouse is a collection of data marts representing historical data from different operation data sources (OLTP). The data from these OLTP are structured and optimized for querying and analysis in a Data Warehouse.

2) Define Cognos Report Net?

Cognos Report Net is the web-based, dynamic, business intelligence reporting solution from Cognos.

3) What are the tiers of the Report Net Architecture?

The Report Net architecture can be separated into three tiers:

* Web server

* Applications

* Data 

4) Define Business Intelligence

 Business Intelligence is a broad category of application programs and technology used for query, Reporting and analyzing the business multi dimensionally.

5) What is a Data Mart?

 A Data Mart is a subset of a data warehouse that can provide data for reporting and analysis.

6) What is HPQS ?

 Data Marts are sometimes also called as HPQS (Higher Performance Query Structure).

7) What is multi dimension analysis?

It is a technique to modify the data so that the data can be view at the different levels of details.

8) What are the responsibilities of Cognos Administrator?

 A Cognos Administrator is assigned with following responsibilities.

  1. Installations and configurations in distributed network environment.
  2. Creating the Repository (Content Store). 3. Perform back up and recovery of Meta Data.4. Developing the user Administration.5. Tuning the servers.6. Deployment.

9) Responsibility of Cognos Architect?

An Architect is responsible for designing the interface by fulfilling the user requirements. Once the interface has been designed it should be rigorously tested before distributing to the end user population.

10) Roles of the Application Developer?

 Design the Reports according to the Report requirement templates.

 Testing the each report with the following types of tests.

  1. Unit Testing b. System Testing c. Performance Testing

11) What is OLAP?

 OLAP stands for Online Analytical Processing. It uses database tables (Fact and Dimension tables) to enable multidimensional viewing, analysis and querying of large amount of data.

12) What are the types of OLAPs?

  1. DOLAP:-The OLAP tool which works with desktop databases are called as DOLAP

Ex:- FoxPro, Clipper, Dbase, Paradox.

  1. ROLAP:-The OLAP tool, which works with Relational databases, are called as ROLAP.

Ex:-Oracle, SQL Server, Tera Data, DB2.

  1. MOLAP: – The OLAP  tool, which works with Multidimensional databases, are called as MOLAP.

Ex:- ESSBASE, Power Cube

  1. HOLAP:- The OLAP tool which works with Relational databases and Multidimensional database, are called as MOLAP.

Ex:- Cognos, Business Objects.

13) What is OLTP?

OLTP stands for Online Transaction Processing. Except data warehouse databases the other databases are OLTPs. These OLTP databases are designed for recording the daily operations and transactions of a business.

14) What is an Interface?

An Interface is a gateway between the user and database.

An Interface contains a logical pointers which pointes to data in the Data Warehouse.

An Interface is isolates the complexity and diversity if data bases.

A good OLAP Interface writes an efficient SQL read on accurate data from database.

An Interface needs to be designed by fulfilling the report requirement.

15) What is the role of a Data Warehouse in creating the Interface?

Data Warehouse acts as Source to the Interface.

16) What are the types of components installed while loading the Cognos Report Net Software?

When we install  the Cognos Report Net Software there are two types of components get installed :-

  1. Windows based components. 2. Web based components.

17) Define Windows based components?

 A Framework manager is windows based modelling tool used for creating a project (interface).

18) Define Web based Components?

 The following are the web based components.

  1. Cognos connection  B. Query Studio C. Report Studio D. Analysis Studio

19) Components of Report Net:

  1. Framework  manager.2. Cognos connection 3. Query Studio 4. Report Studio 5.Transformer 6. Analysis Studio

20) What are the phases of the Cognos Report Net Workflow?

Plan, Manage, Model, Author, Consume.

21) What are the advanced features of Report Net?

The Cognos Report Net has multilingual database accessibility.

The parameter maps are key value phase, which are used for working with local Language that the default value is ‘EN’.

22) What is Framework manager?

 Frame Work Manager is windows based metadata development or metadata modelling tool for Cognos Report Net.

23) Why we need Framework manager?

 To create the interface known as Project.

 To create model the metadata derived from one or more data sources

24) How a project appears in a Framework manager?

A frame work manager project appears as a folder that contains a project file (.cpf) and the specific XML files that define the project.

25) What are the Building Blocks in Frame Work Manager?

The Query subjects are called Building Blocks in Framework Manager.

26) What are phases the workflow process consists in Framework Manager?

The workflow process consists of the following phases:

* Design and create project > * Prepare Metadata >* Prepare the Business View

* Create and Manger Packages >* Set Security > * Publish >* Manage the Project

27) How you set the security Framework manager?

 To set the security you have to set as:* Define access to packages. >* Create security filters > * Define access to objects >* Define package administration access

28) Define Name Space?

In security, a collection of user accounts and user groups from an authentication provider. A  Name Space is a container, which holds the Query Subjects.A Name Space uniquely represents the Query Subjects.

29) Can you delete Cognos namespace?

No, we cannot delete Cognos namespace

30) What is the Cognos Namespace? And what it contains?

The Cognos namespace is the ReportNet built-in namespace

It contains Cognos objects:

Groups , Roles, Sources, Distribution lists, Contacts

31) What are the Groups and Roles?

 Groups and roles represent collections of users that perform similar tasks.

32) What is Folder?

A Folder is used to organize the Query Subjects.

33) Define Relationship?

 A Relationship is a connection that explains how the data in one Query Subject relates to data in other Query Subjects.

34) What is a Package?

A container for models, reports, and so on. Modellers create packages in Framework Manager to publish models to the ReportNet server.

35) What is Physical layer?

The Physical layer provides the physical query layer and is made up primarily of data source and stored procedure query subjects. It acts as the foundation for the presentation layer.

36) What is Presentation layer?

 The Presentation layer makes it easier for report authors to find and understand their data. The Presentation layer is made up primarily of model query subjects that you create.

37) Define function set:

 A function set is a collection of vendor specific database.

 The Expression editor lists the function sets for all available vendors. However, you can restrict the function sets, so that it lists only the vendors that you want to use in your project.

38) What is a Metadata?

 Data about data is called as Metadata. The Metadata contains the definition of a data.

39) What is a Project?

 When we work with a framework manager you work in a project.

 Project is a set of metadata organized for Report Authors according to the Business rules and Model.

 A Project created appears as folder, which contains following files.

  1. Project Name .CPF (Cognos Project File) > b. Model .XML > c. Preferences .XML
  2. Custom data .XML >The files in the folder are unique to that Project.

40) Define Publish?

To transfer all or part of a Framework Manager model to the ReportNet server, so that report authors can use it.

41) What a Project contains?

  1. A Model > B. Namespaces > C. Data sources > D. Parameter maps > E. Packages
  2. Folders > G. Query Subject > H. Query Item >I. Relation ship

42) What is a Model?

 A model in Frame work manager is a business presentation of the structure of the data from one or more databases. Or A model is a set of related query subjects and other objects.

43) What are Dimensions?

Dimensions are categories by which summarized data can be viewed.

44) What are Confirmed Dimensions?

The Dimensions which are reusable and fixed in nature.

45) What are Fact Tables?

A Fact Table is a table that contains summarized numerical (facts)  and historical data. This Fact Table has a foreign key – primary key relation with a dimension table.

46) What are the types of Facts?

 The types of Facts are:

  1. Additive Facts:- A Fact which can be summed up for any of the dimension available in the Fact Table.
  2. Semi-Additive Facts:- A Fact which can be summed up to a few dimensions and not for all dimensions and not for all dimensions available in the Fact Table.
  3.   Non-Additive Fact:- a fact which cannot be summed up for any of the dimensions available in the Fact Table.

47) Define Query?

 A description of the data to be retrieved from a database.

48) Explain about Data Source Query Subject?

Data Source query subjects contain SQL statements the directly reference data in a single data source. Frame work manager automatically creates a data source query subject for each table and view that you import into model.

49) When the default data source query subject is created?

 During the initial metadata import operation, a default data source query subject is created for each object you select (for example table) and creates the default relationships between query subjects for you automatically.

50) How can you edit the data source query subject?

i. Apply or embed a filter > ii. Apply or embed a calculation > iii. Insert a MacroInsert a data source reference. > v. Change the SQL type. > vi. Change how SQL is generated. >These all are we can find it in “Edit definition window”.

51) What are the types of SQL?

 SQL is the industry language for creating, updating and querying relational data base management system. Types of SQL.

  1. Cognos SQL 2. Native SQL 3. Pass-through SQL.

52) Define Cognos SQL?

 By default Cognos FM uses Cognos SQL to create and edit Query subjects.

 Advantages:

  1. Can contain metadata from multiple data sources. 2. Have fewer database restrictions 3. Interact more effectively with Cognos applications.

Disadvantages:

 You can not enter nonstandard SQL.

53) Define Native SQL?

 Native SQL is the SQL, the data source uses, such as Oracle SQL, but you cannot uses Native SQL in a query subject that references more than one data source in the project.

 Advantages:

  1. Performance is optimized across all related query subjects.
  2. You can use SQL that is specific to your database.

Disadvantages:

  1. You cannot use SQL that the data source does not support for sub queries.
  2. The query subject may not work on different database type.

54) Define Pass-Through SQL?

 Pass-Through SQL lets you use native SQL without any of the restrictions the data source imposes on sub queries.

 Advantages:

  1. You can enter any SQL supported by the database.

Disadvantages:

  1. There is no opportunity for Frame work manager to automatically optimize performance. The SQL may not work on a different data source.

55) What are Query Processing Types?

There are two types of query processing.

  1. Limited Local: The database server does as much of the SQL processing and Execution as possible. However, some reports or report sections use local SQL processing.
  2. Database only: The database server does all the SQL processing and execution with the exception of tasks not supported by the database. An error appears if any reports of report sections require local SQL processing.

56) What is Query Subject?

 A Query Subject maps to the table in the database.

 A Query Subject uses an SQL to retrieve the data from the data source.

 A Query Subject is also known as Business View.

57) What are the types of Query subjects?

  1. Default data source query subjects 2. Model Query Subjects
  2. Stored Query subjects

58) What is a Query Item?

 A query Item maps to columns to the database table. A Query Subject contains Query Items.

59) How can you restrict the tables retrieved by a query?

 By setting governors we can restrict the tables.

60) What is meant by Governors?

To apply privileges and restrictions for a user class. Governor settings are used to set restrictions on queries by user class, such as

  1. Sorting on non-indexed columns 2. Outer joins 3. Suppress duplicates 4. Cross-product queries 5. Retrieval of large text items 6. Number of records 7. Number of tables 8. Query execution time

61) What is a User Class?

A defined group of users with the same data access needs, privileges and restrictions.

62) What is Scrubbing at Project level?

According to the report requirements derive the new Items with the help of existing Query Items. This process is known as Scrubbing at Project level.

63) How can you add the calculated data to a report?

  1. Summarize existing detail values predefined summary functions (such as sum, avg)
  2. Create new report items using data from existing report items.
  3. Use model calculations provided in the package.

64) What are the sources to create new Query subjects?

 A new Query Subject can be created from the following sources.

  1. Model (Query subjects & Query Items) 2. Data Sources (Tables and Columns)
  2. Stored Procedure.

65) What is Multi Database Access in Cognos Report Net?

 In Cognos Report Net a Project can be created from multiple databases. The databases can be either homogenous or hydrogenous.

66) What are Parameter Maps?

 A Parameter Map is a key value pair used in creation of conditional Query Subjects.

 A Parameter map sub situates the values at the query at runtime.

67) What is the default formats of reports?

HTML, PDF, CSV, and XML.

68) What are the different ways to modify multi lingual metadata?

  1. Macro :- To modify dynamically
  2. Directly

69) What is a Macro?

A Macro is a runtime object, which uses the parameter maps and session parameters in developing the conditional query subjects.

70) What is usage property? And what are different setting to set usage property?

It identifies the intended use for the data represented by each query item.

It determines the aggregation rules of query items and calculations.

The different usage property settings are:

* Identifies * Fact * Attribute * Unknown

71) What are the types of filters in Framework manager?

In framework manager we can create two types of filters.

  1. Model filters 2. Query filters.

72) Define Model Filter?

 A filter is a condition used to restrict the data displayed in the Report.

 Model filter are reusable.

73) Define Data restriction?

A filter is a condition, which restricts the amount of data displayed in the report.

74) What is condition?

An expression that yields a Boolean value. Conditions are used in query expressions, query filters, and Boolean report variables that can be used for conditional formatting, styles, data sources, layouts and blocks.

75) Types of Conditions?

Conditions are of 2 types:

  1. Static Condition: – Condition doesn’t change whenever you run the report.
  2. Dynamic Condition: -A condition can be kept on change whenever you run the report.

76) What are the types of filters in Report Studio?

 In the report studio we can find two types of filters.

  1. Tabular Filters:- These filters are two types
  2. Summary Filters b. Detailed Filters
  3. Grouped Filter

77) What is a Loop?

A Loop is a closed path which is resulted due to joints.

A Loop causes performance degradation of query execution and wrong data will be displayed in the report.

A Loop can be resolved by creating the short cuts (Alias).

78) What is Alias Table?

An alternative name for a table generally used in self joins

79) What are the uses of Alias Table?

The uses of Alias Table are :1. To resolve the Loops. 2. To create self-joins with in a table. 3. To Provide alternate join structure.

80) What is Associated Data Item?

A Data Item linked to the group data item. Associated data item suppress duplicate data values but do not generate a control break. The associated column displays only one data values for the group with which it is associated.

81) What is Automatic Association?

The group association of a newly created summary. The location of the group where you create the summary determines its automatic association.

82) What is an Ambiguous Relationship?

An ambiguous relationship is where there are multiple relationship paths between one or more query subjects leaving multiple options for how to write a query.

83) What is a Join and types of Joins?

 A Join identifies the columns from one table that are used to link to another table.

 A Join is commonly formed by a foreign key definition within the database.

Types of Joins :- 1. Equi-joins 2. Non equi-joins 3. Outer joins

84) What is a dimension?

A broad grouping of descriptive data about a major aspect of a business, such as products, dates, or markets. Each dimension includes different levels of members in one or more hierarchies, and an optional set of calculated members.

85) What is a Confirmed Dimension?

If a Dimension is connected to multiple fact tables then it is called as Confirmed Dimension.

86) What is a Junk Dimension?

A randomly used dimension is Junk Dimension.

87) Define Surrogate Key?

It has system-generated artificial primary key values, which allows to maintain historical records in the Data Warehouse more effectively.

88) What are the User Interface Components?

* Cognos Connection * Report Studio * Query Studio * Frame work manager

* Cognos configuration

 89) What is significance of Framework Manager in Cognos Reporting?

 Framework Manager is a metadata modelling tool. A model is a business presentation of the information in one or more data sources. When you add security and multilingual capabilities to this business presentation, one model can serve the reporting, ad hoc querying, and analysis needs of many groups of users

90) What is loop in framework manager?

Loop is closed path in IN FRAME WORK MANAGER DUE TO JOINS report net it called as ambiguous relationship. That means a query subject contains multiple paths to retrieve the data. It is an exception to resolve to create a shortcut on that query subject otherwise it displays wrong results in reports and performance is degrades. An undefined join between two tables is known as loop. To resolve loop delete the joins, if these joins are necessary then we have to create shortcuts nothing but alias tables. Place the joins in alias tables.

91) What are the filters in Framework Manager and Report Studio?

Filters in framework manager are

  1. Standalone filters 2. Embedded filters

Report studio Filters are

  1. Detail filters 2. Summary filters.

92) How to import two data sources into Framework Manager?

We can import two data sources to FM  in the Run Metadata  Wizard.

Go to Run Metadata Wizard–>Select another database ( e.g. suppose SQL Server is choosed in prior attempt, then u can choose Oracle as new datasource).Then import it

93) How to perform single sign on in Cognos by using URL?

In Cognos configuration under authentication allow anonymous access should be false. In cgi-bin properties (under iis) the enable anonymous access should be false.

94) What is usage property? Where do we set usage property?

1.The Usage Property identifies the use for the data represented by query item.

It determines the aggregation rules of query items and calculations.
The different usage property settings are Identifier, Fact, Attribute, Unknown

  1. To set the usage property: Click the query item u want to set the usage property for Then click the properties icon on the tool bar or you can see the properties pane on the right side of the browser. Go to “USAGE” tab and click “CLICK TO EDIT” option. And select the appropriate.

95) What is the use of prompts in Framework Manager?

Use of Prompt in Framework Manager:

Filter on query subject – to restrict the rows that are returned by filtering out the unwanted data by creating one or more embedded filters for query subject.

Prompt in filter with usage set to always – user need to supply a value to filter.

96) Where exactly determinants are used in cognos framework manager?

A query subject that behaves as a dimension has multiple levels of granularity and will be joined on different sets of keys to fact data.

97) How would you structure your Framework Manager Model?

A model is normally structured into two layers/views. Database/ Physical view and Business/ Presentation view. These can be implemented using Namespaces.

You import the metadata into Physical layer from DB using data source connection. You will use Import Metadata Wizard. You will change the Query Subjects & Query Items, create relationships (joins) and other activities here.

In the Presentation layer, you create Query Subjects taking input from the existing Physical layer. You change the names of the Query Items and other cosmetic modifications. You can choose only the Presentation layer while creating the Packages and apply appropriate security settings.

98) How to provide security in frame work manager for a query subject?

 Procedure for providing security for query subject in frame work manager is:

Select querysubject -> in properties pane select ->security filters (click on edit)a specify data security wizard appears->click on add groups -> cognosnamesspace(select users and groups wizard opens)

99) How can we generate the cubes in framework manager?

You can create the IQD file from framework manager.

This IQD file will be used by Transformer to create the cube.

100) How to generate IQD file from framework manager?

Create a Query Subject, from the properties pane select externalise, there we have 4 options in that select IQD.

101) What are the components of Report Net?

Framework manager ,Cognos connection, Query Studio, Report Studio

102) What is difference between content store and content manager?

A content store is a data base which stores the meta data of the reports.
But where as a content manager is a service which is available in dispatcher. which it manages the security.

103) What is OLAP?

OLAP stands for On Line Analytical Processing, a series of protocols used mainly for business reporting.

104) What are components of report studio?

Insert table Objects pane > Properties pane > Explorer bar >Report Viewer

105) What is Snap Shot?

snapshot is a static data source it is a picture of report once u created a snapshot report u can’t add a data item.

106) How do you define a cube?

Its a multidimensional view of dimension and measures. It is used to analyze the data in various aspects, which is called slicing and dicing.

107) What is the Difference between Power Play transformer and power play reports?

Power play transformer’ is an ‘MOLAP’ tool using which one can create multi dimensional structure called “CUBE”.
Power play for reports is used to generate report from the cube. Only one report can be generated from one cube. If u want ‘n’ reports u must create ‘n’ cubes.

108) What is cube size?

2.0 GBit depends on our project requirements.

109) What is meant by Junk Dimension?

The Junk Dimension also called as garbage dimension. 
A junk dimension is a convenient grouping of typically low-cardinality flags and indicators.

110) What is transformer?

Transformer is used for building the Cubes (Multidimensional Structure used for OLAP

111) What is catalogue and types of catalogues in cagonos?

A catalogue is a file containing the information (Database tables) that Impromptu users need to create reports. > personal > distributed > hared > secured

112) What is Cardinality?

Relationships exist between two query subjects. The cardinality of a relationship is the number of related rows for each of the two query subjects. The rows are related by the expression of the relationship; this expression usually refers to the primary and foreign keys of the underlying tables.

113) How to join multiple db in catalogue?

Multiple database could not be connected in a single catalogue. So that we use hot file for this purpose.

114) How to generate cubes in cognos?

Power Play Transformer contain dimension,measure,model and cube. we can generate cube different way. > Just right click on cube name and build.
we can write script in Unix. using that we can generate cube.

115) What is snapshot?

A Snapshot is the copy of data, when we create a snapshot it copies the exact data that’s related to the at particular report, we use snapshot when ever we want to compare reports.(Example :we want to compare this months report with previous months).

116) Define data stores in Cognos?

Data sources, also known as query databases, are relational databases, dimensional cubes, files, or other physical data stores that can be accessed through IBM Cognos8. Application Tier Components use data source connections to access data sources.

117) What is metric store database?

A metric store database is a special case of a query database. A metric store database is used to store the information associated with metrics that appear in scorecards, including targets performance metrics , thresholds , membership in scorecards
links to related reports

118) What is difference between view and materialized view?

Views contains query whenever execute views it has read from base table Where as M views loading or replicated takes place only once which gives you better query performance.

119) What is difference between data mart and data warehouse?

A data mart designed for a particular line of business, such as sales, marketing, or finance.

Where as data warehouse is enterprise-wide/organizational

The data flow of data warehouse depending on the approach.

120) What is DTM?

DTM transform data received from reader buffer and its moves transformation to transformation on row by row basis and it uses transformation caches when necessary.

121) What are the different uses of a repository manager?

Repository manager used to create repository which contains metadata the informatica uses to transform data from source to target. And also it use to create informatica user’s and folders and copy, backup and restore the repository.

122) What is a folder?

Folder contains repository objects such as sources, targets, mappings, transformation which are helps logically organize our data warehouse.

123) Explain Informatica Architecture?

Informatica consist of client and server. Client tools such as Repository manager, Designer, Server manager. Repository data base contains metadata it read by informatica server used read data from source, transforming and loading into target.

124) How do you call a store procedure within a transformation?

In the expression transformation create new out port in the expression write :sp.stored procedure name(arguments).

125) What is difference between Informatica power mart and power centre?

Using power centre we can create global repository

Power mart used to create local repository

Global repository configure multiple server to balance session load

Local repository configure only single server

126) What are the batches and it’s details?

Sequential(Run the sessions one by one

Concurrent (Run the sessions simultaneously)

127) What is main difference mapplets and mapping?

Reuse the transformation in several mappings, where as mapping not like that.
If any changes made in mapplets it automatically inherited in all other instance mapplets.

128) What is bitmap index?

A bitmap for each key value replaces a list of row ids. Bitmap index more efficient for data warehousing because low cardinality, low updates, very efficient for where class.

129) Why need staging area database for DWH?

Staging area needs to clean operational data before loading into data warehouse.
Cleaning in the sense your merging data which comes from different source.

130) What is slowly changing dimension?

Dimension attribute values may change constantly over the time. (Say for example customer dimension has customer_id,name, and address) customer address may change over time.

131) What is difference between primary key and unique key constraints?

Primary key maintains uniqueness and not null values Where as unique constrains maintain unique values and null values.

132) What are the types of index?

Bitmap index ,B-tree index, Function based index ,reverse key and composite index. We used Bitmap index in our project for better performance.

133) What are the different uses of a repository manager?

Repository manager used to create repository which contains metadata the informatica uses to transform data from source to target. And also it use to create informatica user’s and folders and copy, backup and restore the repository.

134) What are shortcuts? Where it can be used? What are the advantages?

There are 2 shortcuts(Local and global) Local used in local repository and global used in global repository. The advantage is reuse an object without creating multiple objects. Say for example a source definition want to use in 10 mappings in 10 different folder without creating 10 multiple source you create 10 shortcuts.

 

 

Datastage Interview Questions

Top most important Datastage interview questions and answers by Experts:

Here is a list of Top most important Datastage interview questions and answers by Experts.If you want to download Datastage interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Datastage interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Datastage ,Register for Datastage online training here.

 

1) Define Data Stage?

A data stage is basically a tool that is used to design, develop and execute various applications to fill multiple tables in data warehouse or data marts. It is a program for Windows servers that extracts data from databases and change them into data warehouses. It has become an essential part of IBM WebSphere Data Integration suite.

2) Explain how a source file is populated?

We can populate a source file in many ways such as by creating a SQL query in Oracle, or  by using row generator extract tool etc.

3) Name the command line functions to import and export the DS jobs?

To import the DS jobs, dsimport.exe is used and to export the DS jobs, dsexport.exe is used.

4) What is the difference between Datastage 7.5 and 7.0?

In Datastage 7.5 many new stages are added for more robustness and smooth performance, such as Procedure Stage, Command Stage, Generate Report etc.

5) In Datastage, how you can fix the truncated data error?

The truncated data error can be fixed by using ENVIRONMENT VARIABLE ‘ IMPORT_REJECT_STRING_FIELD_OVERRUN’.

6) Define Merge?

Merge means to join two or more tables. The two tables are joined on the basis of Primary key columns in both the tables.

7) Differentiate between data file and descriptor file?

As the name implies, data files contains the data and the descriptor file contains the description/information about the data in the data files.

8) Differentiate between datastage and informatica?

In datastage, there is a concept of partition, parallelism for node configuration. While, there is no concept of partition and parallelism in informatica for node configuration. Also, Informatica is more scalable than Datastage. Datastage is more user-friendly as compared to Informatica.

9) Define Routines and their types?

Routines are basically collection of functions that is defined by DS manager. It can be called via transformer stage. There are three types of routines such as, parallel routines, main frame routines and server routines.

10) How can you write parallel routines in datastage PX?

We can write parallel routines in C or C++ compiler. Such routines are also created in DS manager and can be called from transformer stage.

11) What is the method of removing duplicates, without the remove duplicate stage?

Duplicates can be removed by using Sort stage. We can use the option, as allow duplicate = false.

12) What steps should be taken to improve Datastage jobs?

In order to improve performance of Datastage jobs, we have to first establish the baselines. Secondly, we should not use only one flow for performance testing. Thirdly, we should work in increment. Then, we should evaluate data skews. Then we should isolate and solve the problems, one by one. After that, we should distribute the file systems to remove bottlenecks, if any. Also, we should not include RDBMS in start of testing phase. Last but not the least, we should understand and assess the available tuning knobs.

13) Differentiate between Join, Merge and Lookup stage?

All the three concepts are different from each other in the way they use the memory storage, compare input requirements and how they treat various records. Join and Merge needs less memory as compared to the Lookup stage.

14) Explain Quality stage?

Quality stage is also known as Integrity stage. It assists in integrating different types of data from various sources.

15) Define Job control?

Job control can be best performed by using Job Control Language (JCL). This tool is used to execute multiple jobs simultaneously, without using any kind of loop.

16) Differentiate between Symmetric Multiprocessing and Massive Parallel Processing?

In Symmetric Multiprocessing, the hardware resources are shared by processor. The processor has one operating system and it communicates through shared memory. While in Massive Parallel processing, the processor access the hardware resources exclusively. This type of processing is also known as Shared Nothing, since nothing is shared in this. It is faster than the Symmetric Multiprocessing.

17) What are the steps required to kill the job in Datastage?

To kill the job in Datasatge, we have to kill the respective processing ID.

18) Differentiate between validated and Compiled in the Datastage?

In Datastage, validating a job means, executing a job. While validating, the Datastage engine verifies whether all the required properties are provided or not. In other case, while compiling a job, the Datastage engine verifies that whether all the given properties are valid or not.

19) How to manage date conversion in Datastage?

We can use date conversion function for this purpose i.e. Oconv(Iconv(Filedname,”Existing Date Format”),”Another Date Format”).

20) Why do we use exception activity in Datastage?

All the stages after the exception activity in Datastage are executed in case of any unknown error occurs while executing the job sequencer.

21) How a routine is called in Datastage job?

In Datastage, routines are of two types i.e. Before Sub Routines and After Sub Routines. We can call a routine from the transformer stage in Datastage.

22) Differentiate between Operational Datastage (ODS) and Data warehouse?

We can say, ODS is a mini data warehouse. An ODS doesn’t contain information for more than 1 year while a data warehouse contains detailed information regarding the entire business.

23) NLS stands for what in Datastage?

NLS means National Language Support. It can be used to incorporate other languages such as French, German, and Spanish etc. in the data, required for processing by data warehouse. These languages have same scripts as English language.

24) Can you explain how could anyone drop the index before loading the data in target in Datastage?

In Datastage, we can drop the index before loading the data in target by using the Direct Load functionality of SQL Loaded Utility.

25) Does Datastage support  slowly changing dimensions ?

Yes. Version 8.5 + supports this feature

26) How can one find bugs in job sequence?

We can find bugs in job sequence by using DataStage Director.

27) How complex jobs are implemented in Datstage to improve performance?

In order to improve performance in Datastage, it is recommended, not to use more than 20 stages in every job. If you need to use more than 20 stages then it is better to use another job for those stages.

28) Name the third party tools that can be used in Datastage?

The third party tools that can be used in Datastage, are Autosys, TNG and Event Co-ordinator. I have worked with these tools and possess hands on experience of working with these third party tools.

29) Define Project in Datastage?

Whenever we launch the Datastage client, we are asked to connect to a Datastage project. A Datastage project contains Datastage jobs, built-in components and Datastage Designer or User-Defined components.

30) How many types of hash files are there?

There are two types of hash files in DataStage i.e. Static Hash File and Dynamic Hash File. The static hash file is used when limited amount of data is to be loaded in the target database. The dynamic hash file is used when we don’t know the amount of data from the source file.

31) Define Meta Stage?

In Datastage, MetaStage is used to save metadata that is helpful for data lineage and data analysis.

32) Have you have ever worked in UNIX environment and why it is useful in Datastage?

Yes, I have worked in UNIX environment. This knowledge is useful in Datastage because sometimes one has to write UNIX programs such as batch programs to invoke batch processing etc.

33) Differentiate between Datastage and Datastage TX?

Datastage is a tool from ETL (Extract, Transform and Load) and Datastage TX is a tool from EAI (Enterprise Application Integration).

34) What is size of a transaction and an array means in a Datastage?

Transaction size means the number of row written before committing the records in a table. An array size means the number of rows written/read to or from the table respectively.

35) How many types of views are there in a Datastage Director?

There are three types of views in a Datastage Director i.e. Job View, Log View and Status View.

36) Why we use surrogate key?

In Datastage, we use Surrogate Key instead of unique key. Surrogate key is mostly used for retrieving data faster. It uses Index to perform the retrieval operation.

37) How rejected rows are managed in Datastage?

In the Datastage, the rejected rows are managed through constraints in transformer. We can either place the rejected rows in the properties of a transformer or we can create a temporary storage for rejected rows with the help of REJECTED command.

38) Differentiate between ODBC and DRS stage?

DRS stage is faster than the ODBC stage because it uses native databases for connectivity.

39) Define Orabulk and BCP stages?

Orabulk stage is used to load large amount of data in one target table of Oracle database. The BCP stage is used to load large amount of data in one target table of Microsoft SQL Server.

40) Define DS Designer?

The DS Designer is used to design work area and add various links to it.

41) Why do we use Link Partitioner and Link Collector in Datastage?

In Datastage, Link Partitioner is used to divide data into different parts through certain partitioning methods. Link Collector is used to gather data from various partitions/segments to a single data and save it in the target table.

42) Define APT_CONFIG in Datastage?

It is the environment variable that is used to identify the *.apt file in Datastage. It is also used to store the node information, disk storage information and scratch information.

43) Name the different types of Lookups in Datastage?

There are two types of Lookups in Datastage i.e. Normal lkp and Sparse lkp. In Normal lkp, the data is saved in the memory first and then the lookup is performed. In Sparse lkp, the data is directly saved in the database. Therefore, the Sparse lkp is faster than the Normal lkp.

44) How a server job can be converted to a parallel job?

We can convert a server job in to a parallel job by using IPC stage and Link Collector.

45) Define Repository tables in Datastage?

In Datastage, the Repository is another name for a data warehouse. It can be centralized as well as distributed.

46) Define OConv () and IConv () functions in Datastage?

In Datastage, OConv () and IConv() functions are used to convert formats from one format to another i.e. conversions of roman numbers, time, date, radix, numeral ASCII etc. IConv () is basically used to convert formats for system to understand. While, OConv () is used to convert formats for users to understand.

47) Explain Usage Analysis in Datastage?

In Datastage, Usage Analysis is performed within few clicks. Launch Datastage Manager and right click the job. Then, select Usage Analysis and that’s it.

48) How do you find the number of rows in a sequential file?

To find rows in sequential file, we can use the System variable @INROWNUM.

49) Differentiate between Hash file and Sequential file?

The only difference between the Hash file and Sequential file is that the Hash file saves data on hash algorithm and on a hash key value, while sequential file doesn’t have any key value to save the data. Basis on this hash key feature, searching in Hash file is faster than in sequential file.

50) How to clean the Datastage repository?

We can clean the Datastage repository by using the Clean Up Resources functionality in the Datastage Manager.

OBIEE Interview Questions

Top most important OBIEE interview questions and answers by Experts:

Here is a list of Top most important OBIEE interview questions and answers by Experts.If you want to download OBIEE interview questions pdf free ,you can register with RVH techguru. Our experts prepared these OBIEE interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in OBIEE ,Register for OBIEE online training here.

 

 

1.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?
Find the SQL query of the report in Admin->manage Session-> view log then run the SQL query on toad if it takes the same time even in TOAD then modify the query to reduce the query time. If the query runs normally in TOAD then we need to make changes in the repository.

2.What is aggregate navigation? How do you configure the Aggregate tables in OBIEE?
Aggregate tables store pre-computed 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.
Ex: If you observe that most of the queries are fired on aggregated values like yearly or monthly data rather that day level data then to speed up the report we can create an aggregate table containing month level and year level data and map it to the respective logical table and set the aggregation levels in the content tab of LTS.

3.We have 4 dimension tables, in that 2 tables need to have hierarchies then in such a case is it mandatory to create hierarchies for all the dimension tables?
No, it is not mandatory to define hierarchies to other Dimension tables.

4.Can you have multiple data sources in OBIEE?
Yes, we can have multiple data sources of different types.

5.Do you know about Initialization Blocks? Can you give me an example where you used them?
Initialization blocks are used for instantiating the value of a variable.
To create dynamic variable you have to create IB to write SQL statement. The result of the query would be assigned as value to the variable.

6.What is query repository tool?
It is utility of OBIEE Admin tool
It allows you to examine the repository metadata
Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer
Ex: we can search for objects based on name, type.

7.Can you migrate the presentation layer to a different server.
No, we cannot migrate only one particular layer to another server, we have to migrate the complete repository or the web catalog.

8.How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?
Dimension tables contain descriptions that data analysts use as they query the database. Ex: the Product table contains product packaging information; and the Period table contains month, quarter, and year values. The tables which contain numeric values used for calculations are used as Fact tables.

9.Why do we have multiple LTS in BMM layer? What is the purpose?
To improve the performance and query response time.

10.How do i disable cache for only 2 particular tables?
In the physical layer, double click on a table on the general tab we have an option Cachable we can either enable it or disable it for that particular table.

11. Explain the Architecture of OBIEE and what each components do?

OBIEE Consists of Presentation Services(Web),Oracle BI (Analytics Engine) Server
The client (Answers) constructs sql and passes it to the Analytic Engine and then the Oracle BI (Analytic Engine) parses the physical sql to the Datasources and retrieve the data back to the Engine and presents to the presentation Services .
12. How to get sql from obiee for reports?
 There are many ways to get the sql
a. Modify the request and click Advanced in that you get xml code and also the actual sql.
b. In the catalog Manager click Tools –>Create Report .In the Create Report Window –> Click Request SQL and save the sql to the physical path in your PC.
c. Enable Loglevel to 2 in the OBIEE Admin Tool from Mange-> Security and enable the log level to 2 by clicking properties for the user, then go to the NQQuery.log in BI_HOME/OracleBI/Server/Logs.You will find the SQL for that User.
d. By clcking Administration->Manage sessions-> view sql.
13. How will you do sort in Reports in OBIEE Answers.
Click modify and then click sort (order by icon) on the relevant column in the criteria pane.
14. How will you do different types of narrative Reports in OBIEE?
By clicking modify request and Narrative View and by giving @1 for the first column result and @2 for the 2nd column and son on and we can also give a heading for No Results by clicking the Narrative view.
15. How will you create Interactive Dashboards ?
By clicking Administration and Manage dashboards and by adding column selector also by using view selector etc.and also by using prompts.
16. What is write-back in obiee ?
In Reports of Answer you can give a column as updatebale and then view the reports,this option is called write back.
17. How will you execute Direct SQL in OBIEE?
By clicking Direct Database Request below the subject area in Answers.
18. How will you create report from two subject areas
From the Criteria Pane of the Report Created from First Subject Area
come to the bottom of the page and click combine request. but the options are limited for combining like union etc..
19. How will you Port changes for dashboards,reports,rpd from development to production?
For the RPD use the Merge option in Admin Tool and for dashboards and reports use Content Accelerator Framework.
20.Define repository in terms of OBIEE?
Repository stores the Meta data information. The extension of the repository file is “.rpd”. With OBIEE 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. OBIEE Server can access multiple repositories
Repository is divided into three layer,
1. Physical – Represents the data Sources
2. Business – model the Data sources into Facts and Dimension and apply business logic
3. Presentation – Specifies the user’s view of the data rendered in OBIEE answers client

21.What is the end to end life cycle of OBIEE?
OBIEE life cycle:
1. Gather Business Requirements
2. Identify source systems
3. Design ETL to load data to the Data Warehouse
4. Build a repository
5. Build dashboards and reports
6. Define security (LDAP or External table)
7. Based on performance, decide on aggregations and/or caching mechanism.
8. Testing and QA.

22.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?
Instead of joining fact with fact we can create one logical table (fact) and add the 3 fact tables as logical table source in the BMM layer.

23.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.
Can use either shared user accounts or can use pass-through accounts
We can have multiple connection pools for each group to avoid waiting

24.What is the purpose of Alias Tables?
An Alias table 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. 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:
1. To reuse an existing table more than once in your physical layer (without having to import it several times)
2. To set up multiple alias tables, each with different keys, names, or joins
3. 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.

25.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.

26.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> select new logical column>give name for new logical column like Total cost.
27.Can you use physical join in BMM layer?
Yes we can use physical join in BMM layer.

28.Can you use outer join in BMM layer?
Yes we can. When we are doing complex join in BMM layer in the join properties we have an option to change the type of join to left outer, right outer, full outer or inner.

29.What is level based metrics?
Level based metrics means, having a measure pinned at a certain level of the dimension.
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.
Ex: if you have a measure called “Dollars”, you can create a “Level Based Measure” called “Yearly Dollars” which 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.

30.What is logging level? Where can you set logging levels?
You can enable logging level for individual users.
We can 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

31.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 persists from the time the repository is started.
Static: This value does not change until a Siebel Analytics Server administrator decides to change it.
Dynamic: The values of these variables change with the values returned by queries. These variables are linked to an initialization block. An initialization block contains a SQL query. When the repository is started the value returned by the query in the initialization block will be assigned to the dynamic variable.
Session Variables-
Session variables are created and assigned a value when each user logs on(when a new session is started). There are two types of session variables:
1. System: 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).
Ex: To filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.
2. Non-system: A common use of non-system variables is for setting user filters.
Ex: 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. For example, to filter a column on the value of the variable ‘SalesRegion’ set the filter to the Variable NQ_SESSION.SalesRegion.

32.What is Authentication? How many types of authentication do we have in OBIEE?
Authentication is the process by which a system verifies a user with the help of a user ID and password. It checks if user has the necessary permissions and authorizations to log in and access data. There are 4 main types of Authentication in OBIEE:
Operating system authentication
External table authentication
Database authentication
LDAP authentication

33.What is object level security?
There are two types of object level security: Repository level and 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.

34.What is data level security?
This controls the type or 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 data for that region.

35.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 a report. Object level security provides security for objects stored in the OBIEE web catalog like dashboards, dashboards pages, folder and reports.

36.How do you implement security using External Tables and LDAP?
Instead of storing user IDs and passwords in a OBIEE 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 Siebel Analytics Web users.
Instead of storing user IDs and passwords in a OBIEE Server repository, you can have the OBIEE 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.

37.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?
Using level base metrics.

38.If you want to create new logical column where will you create (in repository or dashboard) why?
It would be better if we create a new logical column in repository because if it is in repository you can use it for any report. If you create new logical column in dashboard then it is going to affect only those reports which are on that dashboard. We cannot use that new logical column for other dashboards.

39.What is complex join, and where it is used?
To join a dimension table and fact table in BMM layer we use a complex join.

40.If you want to limit the users by the certain region to access only certain data, what would you do?
Using data level security.
In the Admin tool: 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.

41.If there are 100 users accessing data, and you want to know the logging details of all the users, where can you find that?
Set the 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

42.How do implement event polling table?
In OBIEE Server event polling tables store information about updates in the underlying databases. These tables are used for cache management. 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 cache entries corresponding to the updated tables.

43.Define pipeline. Did you use it in your projects?
Yes, pipelines are the stages in a particular transaction, assessment, finance, etc.

44.How do you work in a multi user environment? What are the steps?
Create a shared directory on the network for Multi-user Development (MUD).
Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to point to the above directory.
Define projects within the rpd to allow multiple users to develop within their subject area or Facts.
Save and move the rpd to the shared directory setup in point 1.
When users work in the MUD mode, they open the admin tool and start with
MUD ->Checkout to check-out the project they need to work on (not use the File open as you would usually do).
After completing the development, users check-in the changes back to the network and merge the changes.

45.Can you bypass siebel analytics server security ?if so how?
Yes we can by-pass by setting authentication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml.

46.What are the things you can do in the BMM layer?
We apply the business login in the BMM layer like creating new logical columns, Aggregation navigation, level based metrics, time series wizard, complex join.

47.What is the difference between Single Logical Table Source and Multiple Logical Table Sources?
If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
Ex: Usually Fact table has Multiple LTSs, for which sources will be coming from different Physical tables.

48.How do you bring/relate the aggregate tables into the Siebel analytics Logical layer?
One way of bringing the Aggregate Tables into the BMM layer is by bringing them as Logical Table sources for the corresponding Fact table.
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 in the content tab of the LTS.

49.How do you know which report is hitting which table, either the fact table or the aggregate table?
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.

 

Informatica Interview Questions

Top most important Informatica interview questions and answers by Experts:

Here is a list of Top most important Informatica interview questions and answers by Experts.If you want to download Informatica interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Informatica interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Informatica ,Register for Informatica online training here.

 

1) What is Data warehouse?

In 1980, Bill Inmon known as father of data warehousing. “A Data warehouse is a subject

oriented, integrated ,time variant, non volatile collection of data in support of

management’s decision making process”.

  • Subject oriented : means that the data addresses a specific subject such as sales, inventory etc.
  •  Integrated : means that the data is obtained from a variety of sources.
  • Time variant : implies that the data is stored in such a way that when some data is changed.
  •  Non volatile : implies that data is never removed. i.e., historical data is also kept.

2) What is the difference between database and data warehouse?

A database is a collection of related data.

A data warehouse is also a collection of information as well as a supporting system.

3) What are the benefits of data warehousing?

Historical information for comparative and competitive analysis.

Enhanced data quality and completeness.

Supplementing disaster recovery plans with another data back up source.

4) What are the types of data warehouse?

There are mainly three type of Data Warehouse are :

  •  Enterprise Data Warehouse
  •  Operational data store
  •  Data Mart

5) What is the difference between data mining and data warehousing?

Data warehouse is the base for Data Mining. Data Mining basically extract the knowledge

from Data warehouse. It means by analyzing data in data warehouse, will predict trend in

business.

6)What are the applications of data warehouse?

  • Datawarehouse are used extensively in banking and financial services, consumer goods.
  •  Datawarehouse is mainly used for generating reports and answering predefined queries.
  • Datawarehouse is used for strategic purposes, performing multidimensional analysis.
  •  Datawarehouse is used for knowledge discovery and strategic decision making using datamining tools.

7) What is mapping?

A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation.

8) What is Datadriven?

The informatica server follows instructions coded into update strategy transformations with in the session maping determine how to flag records for insert,update,delete or reject. If u do not choose data driven option setting, the informatica server ignores all update strategy transformations in the mapping.

9) What are the three major types of metadata in a data warehouse?

Metadata in a data warehouse fall into three major categories :

  • Operational Metadata
  • Extraction and Transformation Metadata
  • End–User Metadata

10) What is OLAP?

  • Allow users to run complex dimensional queries.
  • Enable users to generate canned queries.
  • Two categories of online analytical processing are multidimensional online analytical processing (MOLAP) and relational online analytical processing (ROLAP).

11) What is meant by geographic information system(GIS)?

A software system that allows users to define, create, maintain, and control access to a geographic database.

12) What is dimension table?

A relational table that contains dimension data.

13) What is the difference between OLTP and OLAP?

The main differences between OLTP and OLAP are:

  • OLTP systems are for doing clerical/operational processing of data whereas OLAP systems are for carrying out analytical processing of the data.
  • OLTP systems look at data in one dimension; whereas in OLAP systems, data can be viewed in different dimensions and hence interesting business intelligence can be extracted from the data.
  • Operational personnel of an organization use the OLTP systems whereas management uses OLAP systems, though operational personnel may also use portions of OLAP system.
  • OLTP systems contain the current data as well as the details of the transactions. OLAP systems contain historical data, and also data in summarized form.
  • OLTP database size is smaller as compared to OLAP systems. If the OLTP database occupies Gigabytes (GB) of storage space, OLAP database occupies Terabytes (TB) of storage space.

14) What is DTM?

DTM transform data received from reader buffer and its moves transformation to transformation on row by row basis and it uses transformation caches when necessary.

15) What is meant by spatial data warehouse?

A data warehouse that manipulates spatial data, thus allowing spatial analysis. This is to be contrasted with conventional and temporal data warehouses.

16) What is a Batch?

Batches provide a way to group sessions for either serial or parallel execution by the Informatica Server.

17) What are the types of batch?

There are two types of batches are :

  • Sequential batch : Runs sessions one after the other.
  • Concurrent batch : Runs sessions at the same time.

18) What is Rolap?

Relational OLAP (ROLAP) servers store data in relational databases and support extensions to SQL and special access methods to efficiently implement the multidimensional data model and the related operations.

19) What is Molap?

Multidimensional OLAP (MOLAP) servers directly store multidimensional data in special data structures (for instance, arrays) and implement the OLAP operations over those data structures. While MOLAP systems offer less storage capacity than ROLAP systems, MOLAP systems provide better performance when multidimensional data is queried or aggregated.

20) What is Holap?

Hybrid OLAP (HOLAP) servers combine both technologies, benefiting from the storage capacity of ROLAP and the processing capabilities of MOLAP.

21) Describe Informatica Architecture?

Informatica consist of client and server. Client tools such as Repository manager, Designer, Server manager. Repository data base contains metadata it read by informatica server used read data from source, transforming and loading into target.

22) What is fact?

A central component of a multidimensional model that contains the measures to be analyzed. Facts are related to dimensions.

23) What are the types of dimensional table?

There are three types of dimensional table are :

  • Conformed dimensional table
  • Junk dimensional table
  • Degenerate dimensional model

24) What are the types of fact table?

There are three types of fact table are :

  • Additive fact
  • Semi additive fact
  • Non additive fact.

25) What are the types of data mart?

There are two types of data mart are :

  • Dependent data mart
  • Independent data mart

26) What is data source view?

Data source view (DSV) allows one to define the relational schema that will be used in the analysis services database. This schema is derived from the schemas of the various data sources. Cubes and dimensions are created from data source views rather than directly from data source objects.

27) What are the different threads in DTM process?

  • Master thread
  • Maping thread
  • Pre and post session threads
  • Reader thread
  • Writer thread
  • Transformation thread

28) What are the types of datawarehouse applications?

  • Info processing
  • Analytical processing
  • Data mining.

29) What is metadata?

Metadata is defined as the data about data. Metadata describes the entity and attributes description.

30) What are the benefits of Datawarehousing?

The implementation of a data warehouse can provide many benefits to an organization. A data warehouse can :

  • Facilitate integration in an environment characterized by un–integrated applications.
  • Integrate enterprise data across a variety of functions.
  • Integrate external as well as internal data.
  • Support strategic and long–term business planning.
  • Support day–to–day tactical decisions.
  • Enable insight into business trends and business opportunities.
  • Organize and store historical data needed for analysis.
  • Make available historical data, extending over many years, which enables trend analysis.
  • Provide more accurate and complete information.
  • Improve knowledge about the business.
  • Enable cost–effective decision making.
  • Enable organizations to understand their customers, and their needs, as well competitors.
  • Enhance customer service and satisfaction.
  • Provide competitive advantage.
  • Provide easy access for end–users.
  • Provide timely access to corporate information.

31) What is the difference between dimensional table and fact table?

A dimension table consists of tuples of attributes of the dimension. A fact table can be thought of as having tuples, one per a recorded fact. This fact contains some measured or observed variables and identifies them with pointers to dimension tables.

32) What is data mart?

A data mart can contain one fact table to address one subject. In such a case,when a number of data marts are integrated to create a data warehouse, it is important that the facts in each table mean the same thing. Such facts (i.e., measures or metrics) that have the same meaning in different data marts are called conformed facts.

33) What are the different types of datasmarts?

  • Stand–alone Data Marts :Data marts that do not interact with other data marts are called stand–alone data marts. On the other hand, data marts can be integrated to create a data warehouse.
  • Multi–source Data Mart : A data mart for which the input data is obtained from multiple sources is called a multi–source data mart.
  • Personal Data Mart :A data mart for use by individuals such as Chief Executive Officer (CEO), Chief Technology Officer (CTO) or Chief Financial Officer (CFO) is called Personal Data Mart.
  • Operational Data Store : ODS is a database system that obtains data from different sources, consolidates it and keeps it at a single location. However, it does not have the sophistication of a data mart or a data warehouse for analytical processing. ODS is generally developed as a pre-cursor for ERP systems.

34) What is fact table?

A fact table is the central table that contains the measures or facts of a business process.

35) What is the mapplet?

Mapplet is a set of transformations that you build in the mapplet designer and you can use in multiple mappings.

36) What is meant by grain?

Granularity of data is a very important factor in the design of fact table. In fact table, we can represent data at different atomic levels, called grains.

37) What is difference between view and materialized view?

Views contains query whenever execute views it has read from base table.
A view which is physically stored in a database. Materialized views allow query performance to be enhanced by precalculating costly operations.

38) What is surrogate key?

A system generated artificial primary key that is not derived from any data in the database. It is similar to the object identifier in an object oriented system.

39) What is a folder?

Folder contains repository objects such as sources, targets, mappings, transformation which are helps logically organize our data warehouse.

40) What is pivot?

The pivot (or rotate) operation rotates the axes of a cube to provide an alternative presentation of the data.

41) What is the difference between mapping and session?

Maping : It is a set of source and target definitions linked by transformation objects that define the rules for transformation.
Session : It is a set of instructions that describe how and when to move data from source to targets.

42) What is meant by ETL?

The overall data acquisition process, called ETL (extraction, transformation,and loading), is generally grouped into three main components :

  • Extraction : Involves obtaining the required data from the various sources.
  • Transformation :Source data undergoes a number of operations that prepare it for import into the data warehouse (target database). To perform this task, integration and transformation programs are used which can reformat, recalculate, modify structure and data elements, and add lime elements. They can also perform calculations, summarization, de-normal-ization, etc.
  • Loading : Involves physically placing extracted and transformed data in the target database. The initial loading involves a massive data import into the data warehouse. Subsequently, an extraction procedure periodically loads fresh data based on business rules and a pre–determined frequency.

43) What are the types of dimensional schema?

There are two types of dimensional schema :

  • Star schema
  • Snowflake schema

44) What is star schema?

Star schema, there is only one central fact table, and a set of dimension tables, one for each dimension. In star schema, each dimension is represented by only one table, and each table contains a set of attributes.

45) What is snowflake schema?

A snowflake schema avoids the redundancy of star schemas by normalizing the dimension tables. Therefore, a dimension is represented by several tables related by referential integrity constraints.

46) What is meant by starflake schema?

A starflake schema is a combination of the star and the snowflake schemas where some dimensions are normalized while others are not.

47) What is Operational Data Store?

Operational Data Store (ODS) is a hybrid data architecture to cover the requirements for both analytical and operational tasks.

48) What is the difference between star schema and snowflake schema?

The star schema consists of a fact table with a single table for each dimension. The snowflake schema is a variation on the star schema in which the dimensional tables from a star schema are organized into a hierarchy by normalizing them. A fact constellation is a set of fact tables that share some dimension tables.

49) What is data staging?

Data staging is the process of transferring the data from the data sources (operational systems) into the target database of the data warehouse.

50) What is a session?

A session is a set of instructions that describes how and when to move data from sources to targets.

51) What is a transformation?

A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions.

52) What are the types of transformations?

There are two types of transformations.

  • Active
  • Passive

53) What parameters can be tweaked to get better performance from a session?

DTM shared memory, Index cache memory, Data cache memory, by indexing, using persistent cache, increasing commit interval etc

54) What are the different types of transformations available in Informatica?

  • Aggregator
  • Application Source Qualifier
  • Custom
  • Expression
  • External Procedure
  • Filter
  • Input
  • Joiner
  • Lookup
  • Normalizer
  • Output
  • Rank
  • Router
  • Sequence Generator
  • Sorter
  • Source Qualifier
  • Stored Procedure
  • Transaction Control
  • Union
  • Update Strategy
  • XML Generator
  • XML Parser
  • XML Source Qualifier

55) What is a transformation language?

It is a language which is similar to SQL functions so, one can write expressions to modify the data or test the data.

56) Is Aggregator a passive/active transformation?

Active

57) What are the constants used in update strategy?

  • DD_INSERT
  • DD_UPDATE
  • DD_DELETE
  • DD_REJECT

58) What are the benefits of DWH?

  • Immediate information delivery
  • Data Integration from across, even outside the organization
  • Future vision of historical trends
  • Tools for looking at data in new ways
  • Enhanced customer service.

59) Explain informatica repository?

The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information such as mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets.

60) What is power centre repository?

The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information such as mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets.

61) What is power centre repository?

The Power center repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, you can create a single global repository to store metadata used across an enterprise, and a number of local repositories to share the global metadata as needed.

62) What are the types of Informaica client tools?

  • Designer
  • Server Manager
  • Repository Manager

63) What are the types of designer tools?

The Designer provides the following tools are :

  • Source Analyzer
  • Warehouse Designer
  • Transformation Developer
  • Mapplet Designer
  • Mapping Designer

64) What is Aggregator Transformation?

  • Aggregator transformation is an Active and Connected transformation.
  • The Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.
  • The Aggregator transformation is unlike the Expression transformation, in that you can use the Aggregator transformation to perform calculations on groups.
  • The Expression transformation permits you to perform calculations on a row-by-row basis only.

65) What is filter transformation?

  • Filter transformation is an Active and Connected transformation.
  • The Filter transformation provides the means for filtering rows in a mapping. You pass all the rows from a source transformation through the Filter transformation, and then enter a filter condition for the transformation.

66) What is Joiner transformation?

Joiner Transformation is an Active and Connected transformation. This can be used to join two sources coming from two different locations or from same location. The Joiner transformation to join two sources with at least one matching port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources.

67) What is clustering?

Clustering is the process of grouping the data into classes or clusters so that objects with in a cluster have high similarity in comparison to one another, but are very dissimilar to objects in other clusters.

68) What is difference between aggregator and expression?

Aggregator is active transformation and Expressionis passive transformation. Aggregator transformation used to perform aggregate calculation on group of records Where as expression used perform calculation with single record.

69) What are the types of cache?

  • Static cache
  • Dynamic cache
  • Persistent cache
  • Recache from lookup
  • Shared cache

70) What are the costly transformation in informatica?

  • L : Look up
  • A : Aggregator
  • R : Rank
  • J : Joiner

71) What are the transformation having cache concept?

  • Data Cache – 2GB
  • Index Cache – 1GB

72) How does the recovery mode work in informatica?

In case of load failure an entry is made in OPB_SERV_ENTRY(?) table from where the extent of loading can be determined.

73) What is a command that used to run a batch?

pmcmd is used to start a batch.

74) What are the main difference between Data Warehousing and Business Intelligence?

Data Warehousing : is a way of storing data and creating information through leveraging data marts. DM’s are segments or categories of information and/or data that are grouped together to provide ‘information’ into that segment or category. DW does not require BI to work. Reporting tools can generate reports from the DW.

Business Intelligence : is the leveraging of DW to help make business decisions and recommendations. Information and data rules engines are leveraged here to help make these decisions along with statistical analysis tools and data mining tools.

75) What are the advantages of Dimensional modelling?

  • Ease of use
  • High performance
  • Predictable,standard framework
  • Understandable
  • Extensible to accomodate unexpected new data elements and new design decisions

76) What transformations are used for variable port?

  • Expression Transformation
  • Aggregated transformation
  • Rank Transformation

77) What is the difference between OLTP and OLAP?

  • OLTP is nothing but OnLine Transaction Processingwhich contains a normalised tables. But OLAP Online Analtical Programming contains the history of OLTP data which is non-volatile acts as a Decisions Support System.
  • OLTP systems are for doing clerical / operational processing of data whereas OLAP systems are for carrying out analytical processing of data.
  • OLAP system contains the current data as well as the details of the transactions.
  • OLTP system contains historical data, and also data in summarized form.

78) What are the various aggregate calculations?

Aggregate functions are :

  • AVG
  • COUNT
  • FIRST
  • LAST
  • MAX
  • MEDIAN
  • MIN
  • PERCENTILE
  • STDDEV
  • SUM
  • VARIANCE

79) What are designer objects?

  • Source
  • Target
  • Transformation
  • Mapping
  • Mapplet

80) What are Expression transformation?

Expression transformation is a Passive and Connected transformation. This can be used to calculate values in a single row before writing to the target.

81) What are the types of joiner transformation?

Joiner transformation supports four types of joins at Informatica level are :

  • Normal join
  • Master Outer
  • Detail Outer
  • Full Outer

82) What is filter transformation?

Filter transformation is an Active and Connected transformation. This can be used to filter rows in a mapping that do not meet the condition.

83) What are the tools of workflow manager?

  • Task developer
  • Workflow designer
  • Worklet designer

84) What is router transformation?

Router transformation is an Active and Connected transformation. A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. The only difference is, filter transformation drops the data that do not meet the condition whereas router has an option to capture the data that do not meet the condition. It is useful to test multiple conditions.

85) What is lookup transformation?

Lookup transformation is Passive and it can be both Connected and UnConnected as well. It is used to look up data in a relational table, view, or synonym. Lookup definition can be imported either from source or from target tables.

86) What is source qualifier transformation?

Source Qualifier transformation is an Active and Connected transformation. When adding a relational or a flat file source definition to a mapping, it is must to connect it to a Source Qualifier transformation. The Source Qualifier performs the various tasks such as overriding default SQL query, filtering records; join data from two or more tables etc.

87) what is update strategy transformation ?

Update strategy transformation is an active and connected transformation. It is used to update data in target table, either to maintain history of data or recent changes.

88) What is the functionality of Repository manager?

Repository manager is used to navigate through multiple folders and perform the basic repository tasks.

89) What is normaliser transformation?

Normaliser Transformation is an Active and Connected transformation. It is used mainly with COBOL sources where most of the time data is stored in renormalized format. Also, Normaliser transformation can be used to create multiple rows from a single row of data.

90) What is rank transformation?

Rank transformation is an Active and Connected transformation. It is used to select the top or bottom rank of data.

91) What is sorter transformation?

Sorter transformation is a Connected and an Active transformation. It allows to sort data either in ascending or descending order according to a specified field.

92) what is stored procedure transformation?

Stored Procedure transformation is an Passive & Connected or Unconnected transformation. It is useful to automate time-consuming tasks and it is also used in error handling, to drop and recreate indexes and to determine the space in database, a specialized calculation.

93) What is the difference between lookup and fact tables?

A lookup table contains information about the entities. In general the Dimension and details objects are derived from lookup tables.
A fact table contains the statistical information about transactions.

94) What is the functionality of Repository server administration console?

Repository server administration console is used to create and administer the repository through the repository server.

95) What is the difference between OLTP and ODS?

  • OLTP is online transaction processing systems and ODS os operational database system. In OLTP we can save the current data, it depends on the day to day transactions and it stores the day to day data.
  • In ODS we can store data for a month also and it is not restricted to a specific day or transaction.

96) What is sequence generator transformation?

Sequence Generator transformation is a Connected and an Passive transformation. The Sequence Generator transformation generates numeric values. You can use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

97) what is SCD?

SCD means Slowly Changing Dimension. If the values of attributes in a dimension table change over a period of time, then these dimensions are called slowly changing dimension.

98) What are the types of SCD?

  • Type 1 : In the dimensional table, the new data replaces the old data. In other words, the historical data is not preserved.
  • Type 2 : New records are added to the dimension table. The old data is retained and the new records contain the new data.
  • Type 3 : New fields are added to the dimension table so that the table can hold both old values and new values in the same record.

 

MSBI Interview Questions

Top most important MSBI interview questions and answers by Experts:

Here is a list of Top most important MSBI interview questions and answers by Experts.If you want to download MSBI interview questions pdf free ,you can register with RVH techguru. Our experts prepared these MSBI interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in MSBI ,Register for MSBI online training here.
1.What do we mean by dataflow in SSIS?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.
2•What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the
object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.
3•Can you name 5 or more of the native SSIS connection managers?
1) OLEDB connection – Used to connect to any data source requiring an OLEDB connection (i.e.,
SQL Server 2000)
2) Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
3) ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other
connection exposed through managed code (like C#) in a custom task
4) Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
5) File connection – Used to reference a file or folder. The options are to either use or create a file or folder
6) Excel
4• What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.
5• What is Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions. For example, we can organize the transform for the students in a class who have marks greater than 40 to one path and the students who score less than 40 to another path.
6• How do you eliminate quotes from being uploaded from a flat file to SQL Server?
This can be done using TEXT QUALIFIER property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
7• Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName – Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckpointUsage – Determines if/how checkpoints are used. Choose from these options: Never(default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
SaveCheckpoints – Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.
8• What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution:
1) Never: The package will not use a checkpoint file and therefore will never restart.
2) If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
3) Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.
9• What is the ONLY Property you need to set on TASKS in order to configure CHECKPOINTS to RESTART package from failure?
The one property you have to set on the task is FailPackageOnFailure. This must be set for each task or container that you want to be the point for a checkpoint and restart. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.
10• Where can we set the CHECKPOINTS, in DataFlow or ControlFlow ?
Checkpoints only happen at the Control Flow; it is not possible to checkpoint transformations or restart inside a Data Flow. The Data Flow Task can be a checkpoint, but it is treated as any other task.
11• Can you explain different options for dynamic configurations in SSIS?
1) XML file
2) custom variables
3) Database per environment with the variables
4) Use a centralized database with all variables
12• What is the use of Percentage Sampling transformation in SSIS?
Percentage Sampling transformation is generally used for data mining. This transformation builds a random sample of set of output rows by choosing specified percentage of input rows. For example if the input has 1000 rows and if I specify 10 as percentage sample then the transformation returns 10% of the RANDOM records from the input data.
13• What is the use of Term Extraction transformation in SSIS?
Term Extraction transformation is used to extract nouns or noun phrases or both noun and noun phrases only from English text. It extracts terms from text in a transformation input column and then writes the terms to a transformation output column. It can be also used to find out the content of a dataset.
14• What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at runtime. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the runtime and how it looks like after the transformation occurred. The different types of data viewers are:
1. Grid
2. Histogram
3. Scatter Plot
4. Column Chart.
15• What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.
16• Which are the different types of Control Flow components in SSIS?
The different types of Control Flow components are: Data Flow Tasks, SQL Server Tasks, Data Preparation Tasks, Work flow Tasks, Scripting Tasks, Analysis Services Tasks, Maintenance Tasks, Containers.
17• What are containers? What are the different types of containers in SSIS?
Containers are objects that provide structures to packages and extra functionality to tasks. There are four types of containers in SSIS, they are: Foreach Loop Container, For Loop Container, Sequence Container and Task Host Container.
18• What are the different types of Data flow components in SSIS?
There are 3 data flow components in SSIS.
1. Sources
2. Transformations
3. Destinations
19• What are the different types of data sources available in SSIS?
There are 7 types of data sources provided by SSIS: a.) Data Reader source b.) Excel source c.) Flat file source d.) OLEDB source e.) Raw file source f.) XML source g.) Script component
20• What is SSIS Designer?
It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.
21• What is Control Flow tab?
It is the tab in SSIS designer where various Tasks can be arranged and configured. This is the tab where we provide and control the program flow of the project.
22• What is Data Flow tab?
This is the tab where we do all the work related to ETL job. It is the tab in SSIS Designer where we can extract data from sources, transform the data and then load them into destinations.
23• What is the function of control flow tab in SSIS?
On the control flow tab, the tasks including dataflow task, containers and precedence constraints that connect containers and tasks can be arranged and configured.
24• What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when ANY task Failes or Stops or Starts ..
25• What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.
26• What is Solution Explorer?
It is a place in SSIS Designer where all the projects, Data Sources, Data Source Views and other miscellaneous files can be viewed and accessed for modification.
27• How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.
28• How are variables useful in ssis package?
Variables can provide communication among objects in the package. Variables can provide communication between parent and child packages. Variables can also be used in expressions and scripts. This helps in providing dynamic values to tasks.
29• Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying TSQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in datagrid, and also operations for each Output Alias in Operation columns of the same datagrid. Some of operation functions listed below :
• Group By
• Average
• Count
• Count Distinct : count distinct and non null column value
• Min, Max, Sum
In Advanced tab, you can do some optimization here, such as setting up Key Scale option (low, medium, high), Count Distinct scale option (low, medium, high), Auto Extend factor and Warn On Division By Zero. If you check Warn On Division By Zero, the component will give warning instead of error. Key Scale option will optimize transformation cache to certain number of key threshold. If you set it low, optimization will target to 500,000 keys written to cache, medium can handle up to 5 million keys, and high can handle up to 25 million keys, or you can specify particular number of keys here. Default value is unspecified. Similar to number of keys for Count Distinct scale option. It is used to optimize number of distinct value written to memory, default value is unspecified. Auto Extend Factor is used when you want some portion of memory is used for this component. Default value is 25% of memory.
30• Explain Audit Transformation ?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are :
1. Execution of Instance GUID : ID of execution instance of the package
2. PackageID : ID of the package
3. PackageName
4. VersionID : GUID version of the package
5. Execution StartTime
6. MachineName
7. UserName
8. TaskName
9. TaskID : uniqueidentifier type of the data flow task that contains audit transformation.
31• Explain Character Map Transformation?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are:
1. Uppercase
2. Lowercase
3. Byte reversal : such as from 0×1234 to 0×4321
4. Full width
5. Half width
6. Hiragana/katakana/traditional Chinese/simplified Chinese
7. Linguistic casing
32• Explain Conditional split Transformation ?
It functions as if…then…else construct. It enables send input data to a satisfied conditional branch. For example you want to split product quantity between less than 500 and greater or equal to 500. You can give the conditional a name that easily identifies its purpose. Else section will be covered in Default Output Column name.
After you configure the component, it connect to subsequent transformation/destination, when connected, it pops up dialog box to let you choose which conditional options will apply to the destination transformation/destination.
33• Explain Copy column Transformation?
This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.
34• Explain Data conversion Transformation?
This component does conversion data type, similar to TSQL function CAST or CONVERT. If you wish to convery the data from one type to another then this is the best bet. But please make sure that you have COMPATABLE data in the column.
35• Explain Data Mining query Transformation?
This component does prediction on the data or fills gap on it. Some good scenarios uses this component is:
1. Take some input columns as number of children, domestic income, and marital income to predict whether someone owns a house or not.
2. Take prediction what a customer would buy based analysis buying pattern on their shopping cart.
3. Filling blank data or default values when customer doesn’t fill some items in the questionnaire.
36• Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.
37• Explain Merge Transformation?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions :
1. Data should be in sorted order
2. Data type , data length and other meta data attribute must be similar before merged.
38• Explain Merge Join Transformation?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
39• Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
40• Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.
41• Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.
42• Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.
43• What r the possible locations to save SSIS package?
You can save a package wherever you want.
SQL Server
Package Store
File System
44• What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.
45• What is a workflow in SSIS?
A workflow is a set of instructions on how to execute tasks.
(It is a set of instructions on how to execute tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.
46• What is the diff between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process activities of data flow and other processes within a package.
When we want to extract, transform and load data within a package. You add an SSIS dataflow task to the package control flow.
47• What are the main component of SSIS(project-architecture)?
SSIS archItecture has 4 main components
1.ssis service
2.ssis runtime engine & runtime executables
3.ssis dataflow engine & dataflow components
4.ssis clients
48• Different components in SSIS package?
Control flow
Data flow
Event handler
Package explorer
49• What are Connection Managers?
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.
50• What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
51• How to provide securIty to packages?
We can provide security in two ways
1. Package encryption
2. Password protection.
52• What are Precedence constraints?
Constraints that link executable, container, and tasks wIthin the package control flow and specify condItion that determine the sequence and condItions for determine whether executable run.
53• What is Design time Deployment in SSIS ?
When you run a package from with in BIDS,it is built and temporarily deployed to the folder. By default the package will be deployed to the BIN folder in the Package’s Project folder and you can configure for custom folder for deployment. When the Package’s execution is completed and stopped in BIDS,the deployed package will be deleted and this is called as Design Time Deployment.

54. Explain architecture of SSIS?
SSIS architecture consists of four key parts:
a) Integration Services service: monitors running Integration Services packages and manages the storage of packages.
b) Integration Services object model: includes managed API for accessing Integration Services tools, command-line utilities, and custom applications.
c) Integration Services runtime and run-time executables: it saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions. The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes, and custom tasks.
d) Data flow engine: provides the in-memory buffers that move data from source to destination.
55.How would you do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.

56. How would you do Error Handling?
A SSIS package could mainly have two types of errors
a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

57. How to pass property value at Run time? How do you implement Package Configuration?
A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

58. How would you deploy a SSIS Package on production?
A) Through Manifest
1. Create deployment utility by setting its propery as true .
2. It will be created in the bin folder of the solution as soon as package is build.
3. Copy all the files in the utility and use manifest file to deply it on the Prod.
B) Using DtsExec.exe utility
C)Import Package directly in MSDB from SSMS by logging in Integration Services.

59. Difference between DTS and SSIS?
Every thing except both are product of Microsoft.

60. What is Execution Tree?

Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.

61. Difference between Unionall and Merge Join?

a) Merge transformation can accept only two inputs whereas Union all can take more than two inputs

b) Data has to be sorted before Merge Transformation whereas Union all doesn’t have any condition like that.

62. May get question regarding what X transformation do?

Lookup, fuzzy lookup, fuzzy grouping transformation are my favorites.
For you.

63.How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.

64. Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

65.How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent

66. Difference between asynchronous and synchronous transformations?
Asynchronous transformation have different Input and Output buffers and it is up to the component designer in an Async component to provide a column structure to the output buffer and hook up the data from the input.

67. How to achieve parallelism in SSIS?
Parallelism is achieved using MaxConcurrentExecutable property of the package. Its default is -1 and is calculated as number of processors + 2.

-More questions added-Sept 2011
68. How do you do incremental load?
Fastest way to do incremental load is by using Timestamp column in source table and then storing last ETL timestamp, In ETL process pick all the rows having Timestamp greater than the stored Timestamp so as to pick only new and updated records

69. How to handle Late Arriving Dimension or Early Arriving Facts.

Late arriving dimensions sometime get unavoidable ‘coz delay or error in Dimension ETL or may be due to logic of ETL. To handle Late Arriving facts, we can create dummy Dimension with natural/business key and keep rest of the attributes as null or default. And as soon as Actual dimension arrives, the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions.

70. WHAT is SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection
71. What are the three stages of Enterprise Reporting Life Cycle ?
a. Authoring
b. Management
c. Access and Delivery

72. What are the components included in SSRS?
1. A Complete set of Tools that can be used to create, manage and view reports
2. A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.
3.An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

73. What is the benefit of using embedded code in a report?
1. Reuseability of Code: function created in embedded code to perform a logic can be then used in multiple expressions
2. Centralized code: helps in better manageability of code.
74. Which programming language can be used to code embedded functions in SSRS?
Visual Basic .NET Code.

75. Important terms used in the reporting services?

1. Report definition: The blueprint for a report before the report is processed or rendered. A report definition contains information about the query and layout for the report.

2. Report snapshot: A report that contains data captured at a specific point in time. A report snapshot is actually a report definition that contains a dataset instead of query instructions.

3. Rendered report: A fully processed report that contains both data and layout information, in a format suitable for viewing (such as HTML).

4. Parameterized report: A published report that accepts input values through parameters.

5. Shared data source: A predefined, standalone item that contains data source connection information.

6. Shared schedule: A predefined, standalone item that contains schedule information.

7. Report-specific data source: Data source information that is defined within a report definition.

8. Report model: A semantic description of business data, used for ac hoc reports created in Report Builder.

9. Linked report: A report that derives its definition through a link to another report.

10. Report server administrator: This term is used in the documentation to describe a user with elevated privileges who can access all settings and content of a report server. If you are using the default roles, a report server administrator is typically a user who is assigned to both the Content Manager role and the System Administrator role. Local administrators can have elevated permission even if role assignments are not defined for them.

11. Folder hierarchy: A bounded namespace that uniquely identifies all reports, folders, report models, shared data source items, and resources that are stored in and managed by a report server.
12. Report Server: Describes the Report Server component, which provides data and report processing, and report delivery. The Report Server component includes several subcomponents that perform specific functions.

13. Report Manager: Describes the Web application tool used to access and manage the contents of a report server database.

14. Report Builder: Report authoring tool used to create ad hoc reports.

15. Report Designer: Report creation tool included with Reporting Services.

16. Model Designer: Report model creation tool used to build models for ad hoc reporting.

17. Report Server Command Prompt Utilities: Command line utilities that you can use to administer a report server.
a) RsConfig.exe, b) RsKeymgmt.exe, c) Rs.exe

76. what are the Command Line Utilities available In Reporting Services?
• Rsconfig Utility (Rsconfig.exe): encrypts and stores connection and account values in the RSReportServer.config file. Encrypted values include report server database connection information and account values used for unattended report processing
• RsKeymgmt Utility: Extracts, restores, creates, and deletes the symmetric key used to protect sensitive report server data against unauthorized access
• RS Utility: this utility is mainly used to automate report server deployment and administration tasks.Processes script you provide in an input file.

77. What is difference between Tablular and Matrix report?

Tablular report: A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Matrix report: A matrix (cross-product) report is a cross-tabulation of four groups of data:
a. One group of data is displayed across the page.
b. One group of data is displayed down the page.
c. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
d. One group of data is displayed as the “filler” of the cells.
Martix reports can be considered more of a Pivot table.

78. How to create Drill-through reports?
Using Navigation property of a cell and setting child report and its parameters in it.

79. How to create Drill-Down reports?
To cut the story short:
– By grouping data on required fields
-Then toggle visibility based on the grouped filed

81. What is Query parameter in SSRS?
Query parameters is mentioned in the query of the datasources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and can not begin with numeral. For clarity, we use only letters.
82. What are the Reporting Service Components in SSRS?
Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server.
83. What is a matrix in SSRS?
 A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it.
84. What are sub reports and how to create them?
A sub report is like any other reports which can be called in main report and can be generate through main report. Parameters can be passed from main report to sub report and basis of that report can be generated.
85. What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder. Report model project can be created on bids or report server. This model can have simple view. And using
86. What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user. It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start creating reports.
87. What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report. Report builder creates reports on database objects available with report model project.
88.In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 22 is installed or not.

89. How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through solution explorer by providing the report server URL in project properties at Target Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to create a customize utility for your report deployment in bulk.
90. What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report server and can be customize accordingly.
91. What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config reside at ISS.
92.What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag columns values from source.
3. Preview: to check the preview after the report run.
93. Which language rdl files made of?
RDL files are written in XML.
94.What is the chart in report?
 Chart reports are for graphical representation. You can get pie charts columns harts and various other options. 3d charts are also available in reporting services.
95. What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are getting this data i.e. database server and database name connection string.
96. What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
97. What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.
98. How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will find the options for custom code.
99. What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.

 

 

Ab Initio Interview Questions

Top most important Ab Initio interview questions and answers by Experts:

Here is a list of Top most important Ab Initio interview questions and answers by Experts.If you want to download Ab Initio interview questions pdf free ,you can register with RVH techguru. Our experts prepared these Ab Initio interview questions to accommodate freshers level to most experienced level technical interviews.

If you want to become an expert in Ab Initio ,Register for Ab Initio online training here.

 

1. What does dependency analysis mean in Ab Initio?

Dependency analysis will answer the questions regarding datalinage.That is where does the data come from,what applications prodeuce and depend on this data etc.

We can retrieve the maximum (surrogate key) from the existing data,the by using scan or next_in_sequence/reformat we can generate further sequence for new records.

2. When using multiple DML statements to perform a single unit of work, is it preferable to use implicit or explicit transactions, and why?

Because implicit is using for internal processing and explicit is using for user open data requied.
3. Explain what is the architecture of Abinitio?
Architecture of Abinitio includes

GDE (Graphical Development Environment)
Co-operating System
Enterprise meta-environment (EME)
Conduct-IT

4. What is MAX CORE of a component?
MAX CORE is the space consumed by a component that is used for calculations
Each component has different MAX COREs
Component performances will be influenced by the MAX CORE’s contribution
The process may slow down / fasten if a wrong MAX CORE is set

5. Explain what is de-partition in Abinitio?
De-partition is done in order to read data from multiple flow or operations and are used to re-join data records from different flows. There are several de-partition components available which includes Gather, Merge, Interleave, and Concatenation.

6. How do you add default rules in transformer?
The following is the process to add default rules in transformer

Double click on the transform parameter in the parameter tab page in component properties
Click on Edit menu in Transform editor
Select Add Default Rules from the dropdown list box.
It shows Match Names and Wildcard options. Select either of them.

7. Mention what is the role of Co-operating system in Abinitio?
The Abinitio co-operating system provide features like Manage and run Abinitio graph and control the ETL processes
Provide Abinitio extensions to the operating system
ETL processes monitoring and debugging
Meta-data management and interaction with the EME

8. Describe the Grant/Revoke DDL facility and how it is implemented?

Basically,This is a part of D.B.A responsibilities GRANT means permissions for example GRANT CREATE TABLE ,CREATE VIEW AND MANY MORE .

REVOKE means cancel the grant (permissions).So,Grant or Revoke both commands depend upon D.B.A.

9. State the first_defined function with an example.
This function is similar to the function NVL() in Oracle database
It performs the first values which are not null among other values available in the function and assigns to the variable
Example: A set of variables, say v1,v2,v3,v4,v5,v6 are assigned with NULL.
Another variable num is assigned with value 340 (num=340)
num = first_defined(NULL, v1,v2,v3,v4,v5,v6,NUM)
The result of num is 340

10.Explain what is SANDBOX?
A SANDBOX is referred for the collection of graphs and related files that are saved in a single directory tree and behaves as a group for the purposes of navigation, version control, and migration.

11. How to run a graph infinitely?
To run a graph infinitely…The .ksh graph file should be called by the end script in the graph.
If the graph name is abc.mp then the graph should call the abc.ksh file.

12. Explain what does dependency analysis mean in Abinitio?
In Abinitio, dependency analysis is a process through which the EME examines a project entirely and traces how data is transferred and transformed- from component-to-component, field-by-field, within and between graphs.

13. Explain PDL with an example?
To make a graph behave dynamically, PDL is used
Suppose there is a need to have a dynamic field that is to be added to a predefined DML while executing the graph
Then a graph level parameter can be defined
Utilize this parameter while embedding the DML in output port.
For Example : define a parameter named myfield with a value “string(“ | “”) name;”
Use ${mystring} at the time of embedding the dml in out port.
Use $substitution as an interpretation option

14. Describe the elements you would review to ensure multiple scheduled batch jobs do not collide with each other?

Because every job depend upon another job for example if you first job result is successfull then another job will execute otherwise your job doesn’t work.
15. What is a local lookup?
• Local lookup file has records which can be placed in main memory
• They use transform function for retrieving records much faster than retrieving from the disk.
16. Mention how can you connect EME to Abinitio Server?
To connect with Abinitio Server, there are several ways like
• Set AB_AIR_ROOT
• Login to EME web interface- http://serverhost:[serverport]/abinitio
• Through GDE, you can connect to EME data-store
• Through air-command

17.Describe the Evaluation of Parameters order.
Following is the order of evaluation:
• Host setup script will be executed first
• All Common parameters, that is, included , are evaluated
• All Sandbox parameters are evaluated
• The project script – project-start.ksh is executed
• All form parameters are evaluated
• Graph parameters are evaluated
• The Start Script of graph is executed

18. Explain what is Sort Component in Abinitio?
The Sort Component in Abinitio re-orders the data. It comprises of two parameters “Key” and “Max-core”.
• Key: It is one of the parameters for sort component which determines the collation order
• Max-core: This parameter controls how often the sort component dumps data from memory to disk
19. Describe the process steps you would perform when defragmenting a data table. This table contains mission critical data?

There are several ways to do this:

1) We can move the table in the same or other tablespace and rebuild all the indexes on the table.

alter table <table_name> move <tablespace_name> this activity reclaims the defragmented space in the table

analyze table table_name compute statistics to capture the updated statistics.

2)Reorg could be done by taking a dump of the table, truncate the table and import the dump back into the table.
20. What is a ramp limit?
• A limit is an integer parameter which represents a number of reject events
• Ramp parameter contain a real number representing a rate of reject events of certain processed records
• The formula is – No. of bad records allowed = limit + no. of records x ramp
• A ramp is a percentage value from 0 to 1.
• These two provides the threshold value of bad records.

21. Mention what information does a .dbc file extension provides to connect to the database?
The .dbc extension provides the GDE with the information to connect with the database are
• Name and version number of the data-base to which you want to connect
• Name of the computer on which the data-base instance or server to which you want to connect runs, or on which the database remote access software is installed
• Name of the server, database instance or provider to which you want to link

22. Explain the methods to improve performance of a graph?
The following are the ways to improve the performance of a graph :
• Make sure that a limited number of components are used in a particular phase
• Implement the usage of optimum value of max core values for the purpose of sorting and joining components.
• Utilize the minimum number of sort components
• Utilize the minimum number of sorted join components and replace them by in-memory join / hash join, if needed and possible
• Restrict only the needed fields in sort, reformat, join components
• Utilize phasing or flow buffers when merged or sorted joins
• Use sorted join, when two inputs are huge, otherwise use hash join

23. How can you force the optimizer to use a particular index?

Use hints /*+ <hint> */, these acts as directives to the optimizer

24. Have you used rollup component? Describe how?

If the user wants to group the records on particular field values then rollup is best way to do that. Rollup is a multi-stage transform function and it contains the following mandatory functions.
1. initialise
2. rollup
3. finalise
Also need to declare one temporary variable if you want to get counts of a particular group.

For each of the group, first it does call the initialise function once, followed by rollup function calls for each of the records in the group and finally calls the finalise function once at the end of last rollup call.

25. We know rollup component in Abinitio is used to summarize group of data record then why do we use aggregation?
• Aggregation and Rollup, both are used to summarize the data.
• Rollup is much better and convenient to use.
• Rollup can perform some additional functionality, like input filtering and output filtering of records.
• Aggregate does not display the intermediate results in main memory, where as Rollup can.
• Analyzing a particular summarization is much simpler compared to Aggregations.

26. Mention what is Abinitio?
“Abinitio” is a latin word meaning “from the beginning.” Abinitio is a tool used to extract, transform and load data. It is also used for data analysis, data manipulation, batch processing, and graphical user interface based parallel processing.

27. What are the operations that support avoiding duplicate record?
Duplicate records can be avoided by using the following:
• Using Dedup sort
• Performing aggregation
• Utilizing the Rollup component

28. Mention what is Rollup Component?
Roll-up component enables the users to group the records on certain field values. It is a multiple stage function and consists initialize 2 and Rollup 3.

29. What kind of layouts does Abinitio support?
• Abinitio supports serial and parallel layouts.
• A graph layout supports both serial and parallel layouts at a time.
• The parallel layout depends on the degree of the data parallelism
• A multi-file system is a 4-way parallel system
• A component in a graph system can run 4-way parallel system.

30. Have you used rollup component? Describe how?
Post Your Answer
If the user wants to group the records on particular field values then rollup is best way to do that. Rollup is a multi-stage transform function and it contains the following mandatory functions.
1. initialise
2. rollup
3. finalise
Also need to declare one temporary variable if you want to get counts of a particular group.

For each of the group, first it does call the initialise function once, followed by rollup function calls for each of the records in the group and finally calls the finalise function once at the end of last rollup call.

31. What is $mpjret? Where it is used in ab-initio?

You can use $mpjret in endscript like

if 0 -eq($mpjret)

then

echo “success”

else

mailx -s “[graphname] failed” mailid

32. What is local and formal parameter?

Two are graph level parameters but in local you need to initialize the value at the time of declaration where as globle no need to initialize the data it will promt at the time of running the graph for that parameter.

33. What is m_dump?

m_dump command prints the data in a formatted way.

m_dump <dml> <file.dat>

34. What is AB_LOCAL expression where do you use it in ab-initio?

ablocal_expr is a parameter of itable component of Ab Initio.ABLOCAL() is replaced by the contents of ablocal_expr.Which we can make use in parallel unloads.There are two forms of AB_LOCAL() construct, one with no arguments and one with single argument as a table name(driving table).

The use of AB_LOCAL() construct is in Some complex SQL statements contain grammar that is not recognized by the Ab Initio parser when unloading in parallel. You can use the ABLOCAL() construct in this case to prevent the Input Table component from parsing the SQL (it will get passed through to the database). It also specifies which table to use for the parallel clause.
35. What is the latest version that is available in Ab-initio?

The latest version of GDE ism1.15 AND Co>operating system is 2.14

 

36. What are differences between different versions of Co-op?
1.10 is a non key version and rest are key versions.

There are lot of components added and revised at following versions.
37. What is the importance of EME in abinitio?

EME is a repository in Ab Inition and it used for checkin and checkout for graphs also maintains graph version.

38. How to get DML using Utilities in UNIX?

If your source is a cobol copybook, then we have a command in unix which generates the required in Ab Initio. here it is: