All Trainings are completely PRACTICAL, REAL-TIME and project oriented
 040-66638869
  09573168449
  info@vinaytechouse.com
MSBI Classroom Training / MSBI Online Training / MSBI Customized Training:

(Basic to Extreme Advanced Level with REAL TIME Projects) Brochure Download: PDF / WORD

MSBI Real Time Training Designed for Aspiring:
  • a) BI Developers
  • b) BI Leads
  • c) BI Testers
  • d) BI Consultants
  • e) BI Designer / Modeler / Architects
Batch Schedules:
Schedule Mode Duration Fee Link
30-Nov-17 10AM IST ClassRoom 50 Days 8500 Register
30-Nov-17 10AM IST OnLine 50 Days 12000 Register
1-Dec-17 8PM IST OnLine 45 Days 12000 Register
If Schedule doesn’t suit you, for:
  • SEPARATE ONLINE /
  • WEEK-END /
  • ONE ON ONE /
  • CUSTOMIZED TRAINING
TeraData Training : New Batch starts every 15 Days. MSBI Training : New Batch starts every 10 Days

MSBI FREQUENTLY ASKED QUESTIONS [DWH, DM, SQLSERVER, SSIS, SSAS, SSRS, PROJECT]

Datawarehouse Fundamental Questions
  • IT applications and differences between OLTP and OLAP?
  • 1) OLTP (Online Transaction Processing)
  • Purpose: Day-Day business storage
  • Process: Front-End, Back-End process
  • Points:
  • a) Data: Less data
  • b) Model: Noramlized model (more tables,smaller tables)
  • c) Joins: More joins (So retrieval it is slower)
  • d) Access Frequency: High [Milliseconds to seconds]
  • e) Volatality: Volatile [Read, Ins, update, Delete]

  • 2) OLAP(Online Analytical Processing)
  • Purpose: For analysis and mining
  • Process: Layered architecture
  • Points:
  • a) Data: More data
  • b) Model: DeNoramlized model (Less tables, complex tables)
  • c) Joins: Less joins (So retrieval it is faster)
  • d) Access Frequency: Low [Day/week/month...]
  • e) Volatality: NonVolatile [Read]

  • What is Datawarehouse [Single line] and warehouses available in the market?
  • Hige storage area and which is suitable for decision making.
  • Ex: Teradata,Oracle,Sqlserver,Sybase,Netezza, GreenPlum etc...

  • Difference between OLAP and BI?
  • BI is a process which uses OLAP approach to gather, convert and present the data.

  • Difference between Analysis and Mining?
  • Analysis talks about 'current understanding of data' and 'past analysis of data', where as mining talks about 'future prediction of data'.
  • Note: Datamining is a knowledge analysis and discovery method.

  • How many types of decision making available in IT?
  • a) Strategic-- Past analysis [OLAP data]
  • b)Tactical--Current and recent [OLTP]

  • Difference between OLTP and ODS?
  • OLTP holds realtime data where as ODS holds near real time data[daily 4 times from OLTP]
  • ODS helps in two ways >
  • a) Input to DWH
  • b) Incase enterprise database [OLTP] fails, it acts like enterprise db and continue operations.
  • Note: All top companies use ODS in the ETL flow.
  • OLTP similar terminologies?
  • a)Operational Systems
  • b)Enterprises databases
  • c)Business Processing Systems
  • d) Transactional systems

  • Can you describe an End-End typical ETL Flow?
  • OLTP->ODS->Stage Area[Files]->DWH[Stage]-->DWH[WorkArea]->DWH[Target]

  • How many life cycle does a DWH project need?
  • Three life cycles a)ETL b) Semantic c) Reporting
  • Famous DWH life cycles: Agile, iterative incremental [Block approach], V model

  • Explain Inmon Characterstics/ Principles?
  • Inmon Characterstics:
  • a) Subject oriented: Store data based on operation but not application.
  • Ex: Savings coount subject area
  • Application2 : a)Withdraw b)deposit
  • Subject Area: withdraw+deposit single area
  • b) Integrated: Integrate from diverse applications by eliminating inconsistencies and following standards.
  • c) Nonvolatile: No change of data. [Don't modify the data] Helps to maintain history.
  • d) Timevarient: Data store base on timeframes [granularity load / grain]
  • We can perform the below operations easily.
  • a) Current understanding
  • b)Past analysis
  • c)Future predictions

  • What is granularity and which grain your project has?
  • Granularity talks about the level of data maintaining in the project. Dwh has multiple tables with multiple grains.
  • Always the lowest grain has lot of burden but improves analytical performance.
  • Ex: Day/ week/month etc.. grains Day grain is recommended for better analysis

  • What is datamart and how many types available?
  • DataMart--Data sub store for specify business/ operation / functioality May or not not be a subset of DWH.
  • Three types of datamarts
  • a)Dependent-- DWH created first and then datamart [subset of DWH]
  • Ex: ICICI DWH, Savings account dependent datamart
  • b) Independent-- Directly created from source systems [we don't use DWH here]
  • Ex: ICICI employee payroll System [No history, not much analysis required]
  • c) Logical Datamart--It is replica of another data mart

  • How do we create datamarts practically?
  • a) By using Complex Views [Materialized views] --Dependent DM
  • b) By using a complex table --Depedent DM
  • c) By using a seperate physical storage area --Independent DM

  • How many types of DWH approaches available, which approach your company following?
  • Two approaches
  • a) KIMBal approach--Datamarts-> DWH [Bottom up approach]
  • b) Inmon approach-- DWH -->Datamarts [Top down approach]
  • My company using KImbal approach for DWH implementation because of dynamic decisions and adhod requests.

  • How many types OLAP available, which is effective?
  • OLAP--Online Analytical Processing
  • It creates aggregates(sum,avg, max,min stddev,covarience etc...) for decision making.
  • ROLAP: Relational OLAP--Data and aggrgates in the relation are [OLTP area]
  • Latency time less [fresh data anlysis]
  • Slower analysis
  • Minimal set up
  • Ex: Smal, Medium
  • MLOAP: Multidimensional OLAP-- Data and aggregates in mutidimensional area[ Cube]
  • Adv: More and detailed analysis this is helpful
  • Latency time is high [ fresh data analysis is not possible]
  • Complex set up
  • Ex: Corporates
  • HOLAP:Data in relational area and aggreagates in multidimensional area [cube]
  • Ex: Latency time avg
  • Avg set
  • Desktop OLAP: Here the data and aggregates on PC based desk top applications
  • Ex: Excel, LOtus, FoxPro,VISICALC etc...
  • Explain Time hierarchy?
  • Hour-->Day-->Week-->Fort Night-->Month-->Quarter-->half Year(Semister)-->Year

  • You have two databases with same size, then dow do you identify which is OLTP or OLAP?
  • Normalized model is OLTP, Denormalized model is OLAP

  • Need of Data warehouse?
  • 1)To Analysis of data and history maintenance.
  • 2)Companies require Strategic information to face the competition in market.
  • 3)The Operation system are not designed for strategic information.
  • 4)To maintain history of data for whole Organization and to have a single place where the entire data stored.

  • What is data warehousing and Explain Approaches?
  • Many companies follow either characteristic defined by W.H.Inmon or Sean kelly.
  • a)Inmon definition
  • Subjected Oriented, Integrated, Non Volatile,Time Variant
  • b)Sean Kelly definition
  • Seperate, Available, Integrated,TimeStamped,Suject Oriented, Non Volatile, Accessible
  • c)Dwh Approaches
  • There are two Approches
  • 1.Top Down by Inmon
  • 2.Bottom Up by Ralph kimbal
  • Inmon approach:Enterprise datawarehouse structured first and next Datamart created.(TopDown).
  • Ralph kimbal:Datamart designed first, later Datamarts to Datawarehouse designed.(BottomUp).

  • What are the responsibilities of a data warehouse consultant/professional?
  • The basic responsibility of a data warehouse consultant is to ‘publish the right data’. Some of the other responsibilities of a data warehouse consultant are:
  • 1. Understand the end users by their business area, job responsibilities, and computer tolerance.
  • 2. Find out the decisions the end users want to make with the help of the data warehouse.
  • 3. Identify the ‘best’ users who will make effective decisions using the data warehouse
  • 4. Find the potential new users and make them aware of the data warehouse.
  • 5. Determining the grain of the data.
  • 6. Make the end user screens and applications much simpler and more template driven.

  • What are fundamental stages of Data Warehousing?
  • a)Offline Operational Databases - Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance.
  • b)Offline Data Warehouse - Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.
  • c)Real Time Data Warehouse - Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g an order or a delivery or a booking etc.)
  • d)Integrated Data Warehouse - Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

  • What is Datamart Explain Types?
  • It is a specific Subject area or Functionality or Task. It is Designed to facilitate end user Analysis.
  • Wrong Answer-- It is a subset of warehouse
  • Types of Datamarts
  • Dependent, Independent, Logical.
  • Dependent: Created from Datawarehouse as a seperate physical store.
  • Independent :Created directly from operational systems to a seperate physical store.
  • Logical: Exists as a subset of existing Datawarehouse.
SSIS Interview Questions ( Simple, Medium and Complex)
  • Q: What is SSIS?
  • Ans: It is Integration of three services[ETL, Windows Admin,Sqlserver Admin]
  • Q: What is the previous component of SSIS?
  • Ans: DTS[Data Tranmsformation Services]
  • Q: SSIS version differences.
  • Ans:
  • Q: How many engines does SSIS use and explain the operations?
  • Ans: Two engines SSIS use a) SSIS Runtime Engine--Starts when pkg starts
  • 2) Data Pipeline engine--Starts when DataFlow Task Starts
  • Q: What is inmemory process?
  • Ans: DataFlow opertation is Inmemory process. It retrives source data into
  • SSIS internal memory (inmemory) and perform operations (options) and loads from the memory to Target.
  • Q: What is Solution, Project and Package?
  • Ans:
  • Solution--Is a container for one or more projects
  • Project--Collection of packages and related files
  • Package--Collection of tasks and containers.
  • Q: Whare the components a package has and explain in single line?
  • Ans:
  • It has five components.
  • a) Control flow-- controls various tasks and containers with precedence constraints
  • b)Data Flow--contains sources, targets and transformations
  • c) Event Handler--Perfoms action based on event
  • d) Parameters--Similar to variables but take user interaction, helpful in catalog deployment
  • e) Package Explorer--Single view of all components inside package
  • Q: Difference between control flow and data flow?
  • Ans: COntrol flow is the brain of apckage and controls various tasks and containers.
  • It has ETL,WindowsAdmin and SqlServer Admin tasks.
  • Data flow is a component inside control flow and it is heart of ETL process.
  • It transfors data from sources to destinations by doing operations.
  • Q: Difference between Variable and Parameter?
  • Ans:
  • Parameter:
  • a)Take value and support user interaction at runtime
  • b)Scope is across package
  • Variable:
  • a) Take value and does not support user interaction
  • b) Scope at Package/ Container/ Task Level
  • c) Variables of two types 1. System Defined 2. User Defined variables
  • Q: What is the difference between System defined and User defined variables?
  • Ans:
  • System defined variables crated by the system at the time of package creation
  • Store under System namespace.
  • Generally they hold Audit information
  • No Scope
  • Ex: Execution Time,Package Name, User Name etc
  • User defined variables crated by the user.
  • Store under User namespace.
  • Generally they hold user information
  • Three scopes a)Package b)Container c) Task
  • Ex: Numeric value 10
  • String Value MSBIClass etc...
  • Q: What are the extensions of Solution, Project,Package, Paramter and connection Strings?
  • Ans:
  • Solution: .sln
  • Project: .dtProj
  • Package: .dtsx
  • Parameter: .param
  • Connection Manager: .conmgr
  • Q: When do we Miscellaneous folder in SSIS?
  • Ans: Generally Business Docs, LLDs, and other credential docs are maintained here.
  • Q: Difference between COntainer and Group?
  • Ans:
  • Group is logical object which hold tasks and containers.
  • No properties associated to group.
  • Groups are ungrouped.
  • Container is physical object which hold tasks and containers.
  • Properties associated to container.
  • Containers support only deletion.
  • Q: How do yopu identify package and task functionality easily?
  • Ans: By using naming conventions and annotations[detailed descriptions]
  • Q: How many ways we can execute package ?
  • Ans: 8 ways [Materail]
  • Q: How many colors a package uses?
  • Ans: 5 colors
  • Green--Success
  • Red--Failure
  • Orange/Yellow--Running
  • Grey--Disable
  • White--Ready to execute
  • Q: How many types of files SSIS support
  • Ans: 4 types a) Flatfile b)Excel c) XML d) Raw file
  • Note: Refer to material for explanation
  • Q: What is CSV file and how do you open it?
  • Ans: Comma Seperated Value file, we open in two ways
  • 1. Flatfile 2. Excel
  • Q: How do you use csv file in SSIS?
  • Ans: Flatfile source
  • Q: Which extension of flatfile data you get mostly?
  • Ans:
  • a) txt--text file
  • b)dat--data file
  • c) out--output file
  • d)csv--comma seperated value file
  • Q: What is delimiter and explain some dlimiters?
  • Ans: Data seperator is delimiter.
  • Ex: , | : $ & @ LF CRLF etc...
  • Q: What is CRLF?
  • Ans: Carriage return[First char of a line] line feed/ forward [New line]
  • New line first character--Enter key
  • Q: How many types of flatfiles avaulable?
  • Ans: Two types a) Fixed length b) Delimiter
  • Q: What is TextQualifier, how is it helpful?
  • Ans: Qulifies the text with some indicators.
  • Ex: 'vinay'--single quote is text qualifier
  • Q: How many settings we need to know before processing flatfile?
  • Ans: 7 settings [ refer to material]
  • Q: What is codepage, which code page is useful in realtime to process all types of data?
  • Ans: Encrypted charset is codepage,in realtime Unicode or UCS [2/4/8] suitable.
  • Q: Data loading how many ways in real time?
  • Ans:
  • a) Truncate and load
  • b) Incremental load [it is of three types 1.Direct incremental 2. SCD load 3. CDC Load]
  • Q: What is Delta Load?
  • Ans: Differentiated records load is delta load.
  • Q: What is SCD?
  • Ans: Slowly changing dimension for implementung actions for new and modified records.
  • Q: What is CDC?
  • And: Change Data Capture for taking sources changes and doing some actions. Source changes are Inserts,updates and deletes.
  • Q: Can you give alternate terminologies for source, target, import and export?
  • Ans: Source--> Data producer/provider/feed/input
  • Target-->Desintation/ Data consumer/ Data output
  • Import-->Loading/Populating
  • Export-->Unloading/unpopulating
  • Q: Can yopu explain me Dataflow task features briefly?
  • Ans: Moves data from source to destination.
  • Uses internal buffer while processing [in memory process]
  • Each data flow task initiates one Execution Tree internally.
  • Q: Why DataFlow task runs faster than all other tasks in SSSI?
  • Ans: Seperate process [in memory process] and seperate engine.

SSRS Interview Questions ( Simple, Medium and Complex)
  • What is Report?
  • Ans:
  • Data display component with visuals is called report
  • Visual/ Visulizations: Chart,Matrix,List,Map etc...
  • What is SSRS, similar tools?
  • Ans:
  • is a report generation tool.
  • Similar tools are Cognos,BO,Tableau,Qlikview,Crystal reports,Panorama,Jasper, Microstrategy,OBIEE, Hyperion etc...
  • How many types reports generated in the market?
  • Ans:
  • ) OLTP report
  • Business Processing Report
  • Ex: Movie ticket
  • Payment Vochure
  • Tools: Light weight reporting tools
  • Ex: OracleD2k,Jasper,Crystal reports, SSRS etc...
  • b) OLAP Report
  • Decision support (Stategic and Tactic)
  • Ex: Last year last four months sales vs this year last four months sales comparison
  • Every even month sales groth comparison for the last 5 years
  • Tools: Heavy weight tools
  • Ex: SSRS,Cognos,BO,Hyperion,Microstrategy,Tableau,Qlikview etc...
  • What is the generic reporting life cycle steps availabe?
  • Ans:
  • Three steps
  • a) Authoring phase
  • Here we focus on design, layout, look and feel.
  • Even we focus on Data Source,Dataset and logics
  • b)Management Phase
  • Which servers can hold the reports and with what kind of security.
  • c) Access and delivery phase
  • Does does customers can access the reports(either Pull Model/ Push Model)
  • What is push model?
  • Delivering the report in the specified format to the specified people in the specified mode (Email/ Shared folder)
  • Scheduled approach/ subscription approach
  • What is Pull Model?
  • Ans:
  • End users go and get their required format from the report server. (Manual fetch of reports)
  • What is subscription?
  • Delivering the report in the specified format to the specified people in the specified mode (Email/ Shared folder) in a stipulated time.
  • Ex: Need a report in PDF format before 5AM in my
  • What is Report Rendering?
  • Ans: Report delivery format is report rendering.
  • Report render formats: PDF, Excel, Word, HTML,MHTM(Mime HTML) etc...
  • What is report item and report region?
  • Ans:
  • Report item is single cell holder (Ex: Textbox, Line, Image, Place holder etc...)
  • Report region is multi cell holder (Ex: Chart,Matrix, List etc...)
  • What are the report data sources available, what is report data source?
  • Ans:
  • Data feed/ Data input to the report is Data Source.
  • Ex:
  • Sqlserver
  • Teradata
  • Oracle
  • Analysis Services
  • Azure
  • SAP Netviewer
  • Hyperion
  • ODBC --complaint applications (Excel...)
  • XML
  • etc...
  • Data source takes connection to the source
  • [Ex: Servername, credentials]
  • What is Dataset?
  • Ans:
  • Subset of data source data is dataset.
  • Dataset takes a) query b)View c) Procedure to bring columns in the report.
  • What is drill through reporting?
  • Ans:
  • One report to another report navigation is drill through.
  • What is Drill Down report?
  • Ans:
  • With in the same report navigating based on expansion and collapsion.
  • Top down and bottom up hierachical analysis in the report.
  • Ex:
  • Coutry expand states display, states expand cities display etc...
  • What is BookMark reporting?
  • Ans:
  • Moving to the corresponding area in the same report based on action (click)
  • What is parametrised reporting?
  • Ans:
  • If user interaction provided to the report, then it is paramterized report.
  • Ex: Giving location specification to a report to go the correponding location.
  • SSRS History?
  • Ans:
  • 2005-SSRS introduced
  • 2008--Many changes to SSRS [ Architecture, IIS independent, new visualization, Internet protocol change]
  • 2008 R2--New visualizations added [ Map, Indicator, databar, Sparkline] Shared Dataset introducued.
  • 2012--Look and feel [Sleek], Sharepoint Mode many changes. Native Mode: Shared data source added
  • Sharepoint Mode: Power view, Power Pivot added. Dashboards added.Cards, Tiles etc...new visualizations added. The SSRS model is Project Cresend Model.
  • 2014
  • 2016...
  • Which mode of SSRS runs faster?
  • Ans: Shrepoint mode. In this mode all components installed in Sharepoint Server. Sharepoint server is one of the fatest running server service .
  • Hence it is recommended.
  • What do you mean by repory deployment / publishing, what is the use of it?
  • Ans:
  • Reports moving from development to server area for end user access. This process is called as deployment/ publishing.
  • What is report data?
  • Ans:
  • The data which you use in the report is called report data.
  • Ex:
  • Data source
  • Data Set
  • Parameters
  • etc...
  • SSRS important components:
  • Ans:
  • Three important components
  • a) Report Server:
  • Heart of SSRS.
  • Reports stored and retrived by using this component. This is for Developers and DBAs.
  • b) Report Manager:
  • This is brain of ssrs.
  • To manage, schedule, secure the reports this is helpful.
  • It is flexible to Developers, Support, DBAs and Clients [End uSers]
  • c) Report Server Databases.
  • These databases contain system defined tables, which store metedata of reports [Data about data]
  • Ex: For a report metadata is colors, items, fonts, data sources etc...
  • There are two types of databases
  • a) Permanent DB
  • Stores report definitions, data items ,credentials, history etc...
  • b) Temporary DB--Stores temporary calculations, caching informations, sessions info etc...
  • Q: How many reports you schedled as a developer in your 3 years of exp?
  • Ans:
  • As developer i never involved in scheduling.
  • Q: Have you created any procedure or view for your report?
  • Ans:
  • Small company: Yes, I created
  • Large: No, Sql Development Team used to provide that
  • Q: Which type of data retrieval into report is recommended and why?
  • Ans: Procedural retrieval is better in report performance.
  • a) Precompiled so during report operations it won't compile
  • b) Preplanned --Execution plan is generated.So at runtime it will not generate a plan
  • c) Changes at database level are independent to the report
  • [Ex: Column name change or length change or any metadata change]