Business Intelligence Interview Questions

I have got these queries from someone and I would like to share my views. I dont claim my answers to be correct and hence wherever you dont agree, please let me know and share your views.
If you have any questions or comments, please post them.
Question:How you generally Approach to ur Analytics Project?
Answer: Any project should start from defining the scope of the project and the approach
should be not to deviate from the scope.
Then the project should be functionally divided into smaller modules generally done by
project managers alongwith technical and functional leads.
The functional leads then decide on majorly three things:
1. According to the defined scope of the project they start gathering requirements while
interacting with the clients.
2. They had a discussion with the technical leads and try to reach a solution.
3. Technical leads decides what schemas to create and what requirements are going to
fulfill by that schema.
Technical leads discuss all this with the developers and try to close requirements.
Simultaneously testing and deployment is planned in a phased manner.

Question: How we are going to decide which schema we are going to implement in the data warehouse?
Answer: One way is what is mentioned in Question above.
If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organisation
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organisation would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.

Question: What are the Challenges You Faced while making of Reports?
Answer: Making of an report has never been a difficult task. But problem comes when users
are reluctant to adopt a new system. I have experienced that if you are not able to create
the report in exactly the way they used to see, they will keep asking for the changes. Your
approach should be to first show them what they want to see and then add more information
in the report.

Question: What you will do when your Report is not Fetching Right Data?
Answer: this is the biggest problem in report creation and verification. There could be two
reasons for report not fetching the right data.
1. Mostly clients do not have correct data in their database and on top of that to correct
the results they make some changes at the report level to bring the desired result which
you may not e aware of while creating the reports. Clients try to match the data with their
existing reports and you never get the correct results. you try to discover the things and
at later stage come to know of all these problems and you are held responsible for this
delay. Hence always consult the SPOC(Single Point of Contact) and try to understand the
logic they have used to generate their reports.
2. If the database values are correct, there there could be a problem with the joins and
relations in the schema. You need to discover that analysing and digging deep into the
matter.

There are more questions which I will try to answer later.
The questions are very specific to OBIEE and I dont have much experience in that. Hence you may not agree to my answers, but wherever please post a comment and let me know too.


Question: How analytics Process Your Request When you Create your Requests.
Answer: If the Question means how does Oracle BI Analytics Server processes the user requests, the answer is- Oracle BI server converts the logical SQL submitted by the client into optimised physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc.



Question: From where u Get the Logical Query of your Request?
Answer: The logical SQL generated by the server can be viewed in BI Answers. If I have not understood the question, Please raise your voice.



Question: Major Challenges You Faced While Creating the RPD??????
Answer: Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. Th type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lot of problems with the date formats also.


Question: What are Global Filter and how thery differ From Column Filter?
Answer: Column filter- simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- Not sure. I understand this filter will have impact on across the application but I really dont understand where and how it can be user. I heard of global variables but not global filters.


How to make the Delivery Profilers Work?



When we are Use SA System how Does SA Server understand that It needs to use it For Getting

the User Profile information?



Where to Configure the Scheduler?
Answer: I am not sure if Iam correct but we configure the OBIEE schedular in database.



Question: How to hide Certain Columns From a User?
Answer: Application access level security- Do not add the column in the report, Do not add the column in the presentation layer.




Question:How can we Enable Drills in a Given Column Data?
Answer: To enable Drill down for a column, it should be included in the hirarchy in OBIEE. Hyperion IR has a drill anywhere feature where dont have to define and can drill to any available column.



Question: Is Drill Down Possible without the attribute being a Part of a Hierarchical Dimension?
Answer: No


Question: How do u Conditional Format.?
Answer: while creating a chat in BI Answers, you can define the conditions and can apply colour formatting.



Question: What is Guided Navigation?
Answer: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.

How is Webcat File Deployed Across Environment?



Question: How the users Created Differs From RPD/Answers/Dashboards Level?????
Answer: RPD users can do administrator tasks like adding new data source, create hirarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyse the dashboard or can edit dashboard by adding/removing charts objects.


Question: Online/Offline Mode how it Impact in Dev and Delpoyment????
Answer: Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment.
Offline mode- can be useful in test or development environment.


Questions: Explan me the Schema in Your Last Project???????



DB What happens if u Reconcile/Sync Both????

Magic Quadrant for Business Intelligence 2008

Gartner's Magic Quadrant is an easy way of knowing the various tools with their capabilities, shortcomings and market position.
Magic Quadrant for Business Intelligence 2008

Hyperion Licensing Information

for hyperion essbase pricing policy, you can refer to the Oracle Price List
This document is for general purpose.
My main motto behind writing this post is to throw some light on types of licensing policies of softwares.
Before some time there was lot of confusion about the types of licenses. Let me explain them.
1. Named User License:
A named user license allows the software to be used by one specific user named when you purchase the software. Named user licenses may be installed on multiple computers but they may only be accessed by the named user, they may not be shared in any way. A named user license may not be used at the same time on different computers.
It can also be defined as an individual authorized by you to use the programs which are installed on a single server or multiple servers, regardless of whether the individual is actively using the programs at any given time. A non human operated device will be counted as a named user plus in addition to all individuals authorized to use the programs, if such devices can access the programs.
2. Concurrent user license or Floating user license:
A floating user license, sometimes known as a concurrent user license, allows the software to be used by more than one user at the same site but limits the number of simultaneous users to the number of licenses purchased.
For example, three floating user licenses permit three users to access the software at the same time. A fourth user must wait until one of the current users exits the software before they may use the software. A floating user license is sometimes called a concurrent or shared license. Floating user licenses should be purchased for any installation where multiple users may access the software on the same computer such as a build server.
3. Processor based license:
This is defined as the number or procesors where the software is installed and running. The number of cores and core processor licensing factor which generally ranges from 0.25 to 0.75 also decides the number of processors to be considered for licensing.
For example, an XYZ server installed and/or running the program on 6 cores and having core processor licensing factor of .25 would require 2 processor licenses (6 multiplied by a core processor licensing factor of .25 equals 1.50 which is then rounded up to the next whole number which is 2).
There are some other types of licensing terms but only these are used mostly.

Hyperion Reporting and Analysis Architecture

Lets understand the various components of Hyperion:
Workspace is a common window to view the contents of all Hyperion components.
Hyperion Reporting and Analysis:

One zero-footprint Web-based thin client provides users with access to content:

● Financial reporting for scheduled or on-demand highly formatted financial and operational reporting from most data sources including Hyperion Planning – System 9 and Hyperion Financial Management – System 9

● Interactive reporting for ad hoc relational queries, self-service reporting and dashboards against ODBC data sources

● SQR Production reporting for high volume enterprise-wide production reporting.

● Web analysis for interactive ad hoc analysis, presentation, and reporting of multidimensional data.

Hyperion Reporting and Analysis Architecture:(This diagram is taken from some Hyperion Document)

Client: The client tools consist of
Workspace: It is a DHTML Zero footprint web client and provide the user interface for viewing and interacting with the reports created using Authoring studios.
Authoring Studios: These are the client interfaces to create the reports and includes-
(a) Hyperion Interactive Reporting Studio: Windows client where you can connect to different data sources including the flat files and build very interactive presentation reports like reports in simple tabular format, pivot reports, graphs and charts with drill anywhere feature which means that you don’t have to define the hierarchy or drill path and slicing and dicing and Dashboards with many features like hyperlinks to the details reports and embedded browser which can be used to view any other web application to open within the Dashboards.
(b) Hyperion Financial Reporting Studio: Windows client where you can connect to the multidimentional data sources and create highly formatted financial reports by simply dragging and dropping rows and columns and defining page breaks.
(c) Hyperion SQR Reporting Studio: Windows client where you can connect to wide range of data sources and produce high volume pixel perfect operational reports and can be scheduled.
(d) Hyperion Web Analysis: Java applet where you can connect to different data sources using JDBC and build interactive reports and dashboards.
Smart view for office: This is a tight integration with Microsoft Office tools where ou can do analysis like drill downs, keep only and remove only options, POV manager, data refresh, copying data cells and pasting to MS Word and Powerpoint which automatically gets refreshed if the data changes in the source etc. There is one more component in smart wiew which is Hyperion Visual Explorer(HVE), where again you can view the data in presentable interactive graphs and charts.
Application Layer: It consists of two parts :
Web Tier: It consists of two parts (a) Web server- to send and receives content from the web clients. (b) Application server- it is a J2EE application server.
Web server and application server are connected using an HTTP connector.
The web Tier hosts the web applications like workspace, web analysis, interactive , SQR and financial reporting applications.
2. Services Tier: It contains services and servers that controls the functionality of the web applications and clients. Core services handles repository information, authorization, session information, documents publication.

Siebel Analytics (OBIEE) Vs Hyperion Essbase

In this post I will discuss some of the major differences between OBIEE and Hyperion Essbase.
I have got the opportunity to work on Oracle BIEE formerly known as Siebel Analytics and Hyperion Essbase. Sometimes people come up with a question that which one is the best and what are the differences. I just want to share some views which you may ar may not agree. I would appreciate any comments.

1. From a broader prospective, I felt that Hyperion Essbase is a very comprehensive BI tool with very advance features to cater specific areas of an organisation. Essbase provide options like Block storage and aggregate storage options. Block storage options can be very effective for planning and budgeting applications and it also enablles to write back the data like budget data to essbase and do what-if analysis and save their versions. Even OBIEE allows you to write back but not useful for what-if type analysis.

Organisations keeping most of their planning and analysis data in MS Excel, would find it more useful than Siebel analytics. To load data from text files, load rule files can be created which holds the information about any transformation or cleaning operation done and same load rule file can be used for many loads.

Lets discuss on some more differences-

1. Hyperion Essbase is MOLAP where as OBIEE is ROLAP- MOLAP as we store the cube data, analysis is much faster than ROLAP where data is not stored anywhere and queried directly from the source database. But when you are storing an Essbase cube you can analyse data only upto the time it was created( we call it near real time analysis) where as OBIEE gives you the 100% real time analysis. Now this all depends on the needs of an Organisation.Hyperion Essbase also provides XOLAP or hybrid OLAP where Summary level data resides in Essbase cube but detail data is retreived fom the source database. Also to load the data in the cubes or refresh the cube with fresh data, it takes some time and resource.

2. Hyperion Essbase requires a relational database to store metadata where as OBIEE does not. So you can save a lot on this.

3. Hyperion essbase use Microsoft Excel as analysis tool- This is one feature where Hyperion scores over OBIEE. Its not that OBIEE does not have integration with Excel but the way you do it in Hyperion is amazing. OBIEE only allows you to open the charts you have created in Excel or powerpoint and analyse but Hyperion places the entire cube in front of you in excel and you can drill down, add or remove dimensions, apply filters, pivot data, slice and dice and much more and also the write back capability which makes it the first choice for budgeting and forecasting applications. This is very important from implementation perspective as most of the users are already working on excel and they do not even think twice of acceping the system I mean users adaptibiliy is high and fast as compared to any other system which generally take atleast 15 days to learn and 1 month to understand and atleast 2 months to get fully used to the system and 3 months to accept the system. So a very big factor interms of a project being successful. Also, saves a lot of cost on training.

4. Pre built applications available for OBIEE- Oracle has created pre-built schemas, reports and dashboards for some major ERP's like SAP, Oracle Apps, JD Edwards, peoplesoft etc. Those warehouse pre-built schemas can be used by Hyperion but only to some extent and as the dashboards are created in OBIEE, Hyperion cannot use it. This is the scenario as of now but soon they will be available for Hyperion as well.

5. Very easy for end users to design dashboards in OBIEE. If you need a system only for your BI and reporting needs, OBIEE will be a good value proposition.

6. Advance analytics, what-if's, predictive modelling and very fast infact lightening fast system Hyperion Essbase would be effective.

Learn Qlikview

I have posted a tutorial for how to create quick dashboard in Qlikview at
Manohar Rana: Getting Started with Qlikview

You amy also like to read detailed comparison of Qlikview with others tools at

Qlikview Vs Others

You can also refer to the complete tutorial provided by Qliktech at

Qliktech Tutorial

Business Intelligence Will Decline

Gartner Says Strong Growth in Business Intelligence Will Decline as Market Flux Continues

Analysts Discuss Shifting Business Intelligence Landscape at Gartner Business Intelligence Summit, 5-7 February 2008

Egham, UK, January 10, 2008 — The days of strong double-digit growth in the business intelligence (BI) market are over as the industry enters a state of flux following vendor consolidation, increasing maturity and price erosion, said Gartner. However, BI remains mission critical for businesses as it turns information into an asset for deriving insight and making decisions. Gartner advised end users to make BI pervasive to business users by making it user-friendly, collaborative and process-driven.

Speaking ahead of the Gartner BI Summit, held from 5-7 February in Amsterdam, Netherlands, Gartner analysts said worldwide growth rates in 2007 are expected to be slightly lower than the previous year, at 12.5 per cent, and will move into single-digits beyond $7 billion by 2011, with a five-year compound annual growth rate (CAGR) of 8.6 per cent.

Following consolidation among BI vendors, Gartner added that value to users can also increase as a result of mergers and acquisitions in the market. “Consolidation activities by SAP, Oracle, IBM and Microsoft should help accelerate the value derived from BI,” said Gartner senior research analyst and presenter at the Gartner BI Summit, Dan Sommer. “Large vendors will drive increased usage, while new BI vendors will emerge introducing innovative technology and products to demonstrate differentiation and fill the gaps in "mega-vendors'" product lines.”

Competitive edge
Mr Sommer added that increased BI innovation also means that query, reporting and online analytical processing (OLAP) capabilities have reached parity and no longer deliver competitive edge. Most vendors now include these basic BI capabilities in their product stacks, including Microsoft which added more BI functionalities in SQL Server 2005, Office 2007 and PerformancePoint Server. Remaining pure-play vendors can recruit application vendors (not SAP, Oracle or Microsoft) as original equipment manufacturers (OEM) of their BI platform to provide analytical applications, or leverage relationships with value-added resellers (VARs) for domain-specific solutions.

Successful pure-play BI vendors will incorporate emerging areas in BI such as dashboards, predictive modelling, enterprise search, interactive visualisation techniques and in-memory analytics. Hosted BI through software-as-a-service (SaaS) is one new approach being pioneered by a cluster of vendors including Seatab and LucidEra. They can also continue thriving by specialising by industry or geographic region. In addition, smaller and midsize organisations are becoming an important target market for BI vendors, with a large proportion being new opportunities.

Mega-vendor dominatio
The acquisitions by Oracle (Hyperion), SAP (Business Objects, still pending) and IBM (Cognos, still pending) in 2007 were disrupters for the market, which, if they are finalised, will eliminate all larger publicly traded BI companies. Overall, more than two-thirds of the current BI market is now attributed to the mega-vendors. The remaining BI powerhouse vendors SAS, Microstrategy, Information Builders, and more so, smaller BI vendors, such as Arcplan, Panorama, or Qliktech, will need to increase market push to stay visible above the increased noise from the “big four.”

North America, Western Europe and Japan are the most-significant regions in terms of BI spending and will still account for five-sixths of software revenue by 2011. However, "greenfield" opportunities, together with fast economic and structural developments, will fuel double-digit growth in Asia/Pacific, Eastern Europe, the Middle East and Africa, and Latin America.

End users
Gartner advises end users of BI solutions from vendors that have been recently acquired to hold strategic investments until a product roadmap has been clearly presented from the vendor. While there is no doubt that the acquired core products, such as Oracle’s Hyperion Essbase, Business Objects XI, or Cognos 8 will remain highly strategic and supported by extensive research and development funding, overlapping products in a vendor’s portfolio may see some defocus in the mid-term.

For more information on the Gartner BI Summit and to register for the event, please visit europe.gartner.com/bi.

Getting Started with Qlikview

You can download the free 15 days evaluation version from

http://www.qlikview.com/download.htm

Installation is simple is quick without any configration.
Open Qlikview from the start menu.
Go to file – New to create an application. Save it.

This is how the interface will look like.

There are two types of options for relational databases. OLEDB and ODBC.Create a DSN and reference it Here. However you can create the DSN from Here also.

I have already created a DSN ‘SH’ and after selecting provide the username and password. Click on OK. A connection string will automatically appear on the screen.

Now click on Select button.

Select the correct owner. The table and columns available will appear.

Select the table required and columns. To select more than one table use ADD button.

Select table and click ADD. Again select another table and click ADD and so on.

Also check the Preceding load button just above add button.

You can see the script generated on the left pane.

Click on OK once completed.

The scipt will appear like this.

Save the application and click on Debug to see the effects. The fields with the same name will be joined automatically. Lets see the results.

Check limited load and let the rows be 10 only.

Click on Run button.

The script is run and finished. Click close.The fields will appear like this.

Click on OK. And save the application.

Click Ctrl+T. The relations will appear like this.

This relation is perfect hence no need to change any joins. Click on OK.

Now we will reload the application with complete data. Click on Reload button which is next to the edit script button. After reload is complete click on OK and save the application.

Now we Will try to create a sample report of country wise sales.

Right click on the window . Select New sheet object- chart.

Give window Title as Country Sales. Select the type of chart. This time let it be bar chart only. Click on Next.

Select Country Name as the Dimension.

Click on Next

Write this expression as shown.

Click on OK

Click on Finish to see the Raw report.
Now there are lot of options available to make it look more relevant and beautiful. But your report is made. With some few more changes it will look like this.
Play with it. Its a wonderful tool and you can make quick reports out of it.

Good Luck.