1. What is MSBI?
Microsoft Business Intelligence (BI) is a suite of products and tools that you can use to monitor, analyze and plan your business by using scorecards, dashboards, management reporting and analytic.
2. What are the tools in MSBI?
It contains the following tools:
- SQL Server Analysis Services (SSAS)
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
Performance Point Services (PPS) which has added as a free service in Microsoft Office Share Point 2010.
3. What is SSIS? How it is related with SQL Server.
SQL Server Integration Services (
SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.
This is a platform for Integration and Work flow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading
ETL. The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.
4. What are the tools associated with SSIS?
We use Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) to work with Development of SSIS Projects. We use SSMS to manage the SSIS Packages and Projects.
5. What is the control flow?
lA control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements: Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.
6. What is the data flow?
Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.
7. In SSIS what is the difference between Control Flow and Data Flow?
Control Flow defines the work flow. It defines what all tasks need to be defined and in which order.
Data Flow defines the flow of Data with a single Control Flow task (called Data Flow Task).
8. What else we can do with the help of SSIS other than these ETL process?
In Control Flow we will find a very useful task called Data Flow Task. It let us define our ETL process. But this is not the end, we have many other useful tasks such as Execute SQL Task (let us execute Sql queries), Send Mail Task(let us send mail),MSMQ Task(let us work with MSMQ message. It let us send and receive messages from MSMQ), Backup Database Task etc.
9. What are partitions in SSAS?
SSAS let us create cube. Cube is a multi dimensional database. Data will be stored inside cube as dimensions and Fact Tables.
By default in order to store Fact tables (measure group tables) partitions will be created inside cube.
Be default for one fact table one partition will be created. Partition is simply a physical storage unit inside cube.
10. What are the Advantages of having partition?
Advantages of having partition:
- Each partition can be stored inside a separate physical drive bringing parallel data access into picture.
- Each partition can have its own aggregation logic.
- Different storage setting can be defined for each partition. Example – one partition supports MOLAP whereas one supports ROLAP
- Each partition can be processed independently
- By keeping historical data and a new data of a particular method in separate partitions processing speed can be improved.
Business Intelligence interview questions are more and more
often searched due to very high demand and relatively low amount of experts
(comparing to demand). At least that seems to be the trend in recent years when
it comes to SQL Server Business Intelligence. Microsoft become serious BI
player after release of SQL Server 2005 with proper BI tools.
In this post I will cover general business
intelligence concepts so you can get a good understanding of the candidate
grasp of the ideas before assesssing his technical skills. For technical
questions related to BI tools (SSIS, SSAS, SSRS) Please visit our intervie
questions and answers main page.
Question: Describe main areas (or tools) that
represent Business Intelligence.
Answer: This is open question and answers will vary but the candidate might
say ETL (SSIS), Data Warehouse and Cubes (SSAS) and Reporting (SSRS). This list
can be longer and can include data mining, master data management, data
profiling, data cleansing, analysis and data gathering also can be added here.
Question: What is the purpose of ETL?
Answer: ETL (Extract Transform Load) is used to extract data from various
sources. Transform the data which means applying business rules and performing
data cleaning (the latter being often overlooked that causes issues) and Load
the clean data into data warehouse. Sometimes in ETL there is also master data
management element so it can extract data, perform transformation and it may
use proper tools to perform data cleansing and check business rules. In recent
versions of SQL Server (including 2012) we can have Master Data Services (MDS)
and Data Qaulity Services (DQS).
Question: What is the purpose of cubes?
Answer: Cubes allow to store larges amounts (usually track history) of data
and get very good performance. Security is very detailed and it can be applied
on "member level" which is an individual value in a column. Cubes
usually track history so user get access to "point in time" data and
accurate data. My favourite example is top 1 customer that moves its location
from London to Bristol with cubes (or more data warehouses) historic data will
not move to Bristol and it will remain in London which is very often not the
case in providing reporting information from operational systems.
Question: What is the purpose of reporting?
Answer: That is more self-explanatory than previous questions. Ability to view
or create report by users is the short answer but usually reporting tools like
SSRS are much more powerful and they allow to set up subscriptions, provider
reports in different usable for users format, secure reports and so on.
1. Translators?
2. Write back Property?
3. Look up and Example clearly?
Session on 13-07-2013(F)
1. How many languages are used to retrieve data from cube?
2. Degeneration dimensions?
3. write back property
4. Named Calculations and example in real time
5. member,tuple,set definition explain and example
6. Named set
7. Aggregations
8. Creating cube , what challenges u faced
9. How to improve cube performance
10. Before cube creation process methods
11. Datawarehouse
12. Star & Showflake and difference between them
13. MOLAP , ROLAP, HOLAP
14. difference between parellelperiod and periods_to_date
15. YTD,MTD,QTD
16. what is current member
17. what is descendants
18. Drill Through Reports
19. Click Through Reports
20. Drill down Reports
21. sub report
22. one main report and two sub reports exports two in separate excels
23. ssis how to handle errors
24. what are transformations
25. character map transformations
26. blocking and non blocking
27. synchronous and asynchronous
28. hash index
29. Architectures of ssis,ssas,ssrs
30. Top 5 in MDX
31. snap shots
Session on 25-07-2013(F)
1. Types of constraints
2. Triggers
3. Constraints
4. How to initiate the triggers
5. 2nd highest salary of employees
6. ssis _ merge ,In merge any condition is used or not and explain brief example
7. Slowly changes dimension explain
8. Bulk insert task and give example
9. 100 to 1000 records are in database , u retrieve data from 100 to 200 records by using ssis transformations
10. design of cube
11. shared database ---> local database ---> destination tables. How to performs ssis transformations on it.
12. dimensions and fact
13. Check Points
14. How to place Check Points in package level or Task level and briefly explain
15. How about Loggings and types of Loggings
Session on 21-09-2013(F)
1. What Type of Connection Managers is used in ur project?
2. Transformations used in ur Project
3. What is use Merge Join
4. Sort Transformation is compulsory or not
5. How to eliminate duplicates
6. Audit information is useful or not
7. Event Handlers and used in ur project
8. Execute Sql Task, How to use in Real Time
9. Variables, Check Points in ur Project
10. How to check the data passing from source to destination correctly
11. Data viewers and Types
12. What is ur of User variables
13. Script Component and used in Project
14. Package Deployment
15. How do u do Package Configuration
16. How to u encryption data from one place to another place
17. What is CTE
18. What is Difference between Local and Global Temp Tables
19. How can u handle duplicate data using Temp tables or Another
20. How do u use Indexes and Types of Indexes
21. How long work with Mdx and SSAS
22. Aggregation and Partitions
23. Hierarchs and Types
24. Star and Snowflake Schemes
25. Report Builder and SSRS
26. Linked Reports and SubReports
27. Types of Reports
28. Types of Parameters
29. How to deleting options in report display at browser(only pdf)
30. Snapshots
Session on 24-09-2013(F)
1. Why is need of Data warehouse?
2. Package Optimization Methods
3. What type of source files are used in Project
4. How to Pull data in Data warehouse
5. How to Deploy the Report
6. What is Incremental Load
7. U create a variable in DataflowTask and used it Entire Package or not
8. U changes the scope of variable from Dataflowtask ->Package.
Session on 26-09-2013(T)
· Generally what type of issues u face getting data from various sources?
· What type of Package Configurations used?
· I have on ssis package one sequence container. In Dataflow task and execute sql task is there. That time what type transactions in database (Rollback transactions).How to delete data from database
· Difference between Data path and Precedent Constraints
· What type of Property used do u need to Order Feature in SSIS?
· How to schedule the package and types?
· Logging in SSIS?
· Difference between Sequence Container and For each loop Container
· Difference between Stored Procedure and Functions
· Indexes and Types of Indexes & Drawback of Indexes
Table1 - Product id, Product name
Table2 – Product group
Table3 – Sales amounts of products
Now i want sum of sales of product group.
Session on 28-09-2013(W)
· Difference between Merge and Union All?
· Difference between Merge and Merge Join?
· Using Merge Join Transformation Before not using Sort Transformation?
· How can you terminate a dataflow path without a Destination?
· Performance Considerations given while dealing with SCD’s?
· Command Line Tools to execute & Manage SSIS Package?
· Define Normalization, Boyee&Codd Normal Form?
· Difference between OLEDB Destination and SQL Server Destination?
· How to get 08-01-2013 Balance of Account No – 1
Account No
|
Account Balance
|
Date
|
1
|
1000
|
01-01-2013
|
1
|
2000
|
02-01-2013
|
1
|
1500
|
03-01-2013
|
1
|
1700
|
05-01-2013
|
1
|
1400
|
12-01-2013
|
1
|
3400
|
15-01-2013
|
· How to get Error Data of Below Table
S.no
|
Name
|
Age
|
1
|
28
|
Aaaaaaaa
|
Bbbbbb
|
2Bbbbbb
|
23
|
3
|
Ccccccc
|
Vvvvvvvvv
|
Dddddd
|
4ddddd
|
45
|
5
|
eeeeeeeee
|
56
|
6
|
Fffffffffff
|
67wwwww
|
Final Table data and Error Information go to Client by Mail.
· Date Validations and Explain Clearly with Example?
· Customer Information Table : -
Account No
|
Account Name
|
Date
|
1
|
A
|
01-01-2013
|
2
|
B
|
02-01-2013
|
3
|
C
|
03-01-2013
|
4
|
D
|
05-01-2013
|
5
|
E
|
12-01-2013
|
6
|
F
|
15-01-2013
|
Account Balance Table: -
Account No
|
Balance
|
1
|
10000
|
2
|
4500
|
3
|
5400
|
4
|
22000
|
5
|
4400
|
6
|
3000
|
Update Balance above 5000 + 10% added and below 5000 - 10%
By using single Query in Sql Command.
Session on 30-09-2013(TC)
· U Gather Requirement from Client or BI Team. How take Requirement Gathering from manager?
· One Excel file contains 10 Columns and Another Excel file contains 15 Columns. How to get data from two Excel Files.
· What is SDLC? Which state u worked in ur Project
· How many dimensions and fact u worked on in ur Project & How to work with Maximum Size of Dimensions and Facts like 20 Facts and 15 Dimensions.
· How many ways to Deployment SSIS Package
· Variables and Explain Variable Scope
· Fuzzy Grouping and Fuzzy Look up , Unpivot
· How to Handle Error Handlings in SSIS
· How Variables in Data Warehouse Concept
· Explain DTS and SSIS, Difference also
· What is UDM
· What is use of SSAS Components
· How Cubes are implemented in SSAS
· What is Ragged Hierarchy?
· What are Different Ways of Creating Aggregations?
· What is Write Back Property in Partition?
· What are SSRS Components?
· What is Report Builder?
· Running SSRS Reporting in Windows XP create no of Users?
· What are Drawbacks in SSRS Reports?
Session on 08-10-13(TL)
· Data ware Development Life Cycle
· What is the Stored Procedure(SP)
· What is the Precompiled Option in SP
· One table and One View, The structure was same but not related with each other.Table Contains 100 Rows &View Contains 200 Rows. Now Please Write Query for Total Rows in those Two.
· One Table u finds orphan records in a database.(orphan means their parent record_ in another table has been deleted) Employee table & Employee Parent table but some time deleted records in Employee Parent table only. How to find which records are deleted in Parent Table
· Difference between Merge and Merge Join
· One table u Delete Duplicate Records without used CTE
· Triggers and Explain Types.
· What is the Mechanism for Triggers and Explain Briefly(How to Run Triggers)
· Indexes and what is the difference between Rebuild Index and Re organize Index
· 3 Triggers in a Table (Table have 20 Columns) First Trigger writes in first 3 columns in a table. Second Trigger writes in 4 to 9 columns in a table. Third Trigger writes in 10 after in a table. Can u Please Explain? If write any Operation what is order of Triggers are Executed.
· What is Select into & Insert into
· One table have Primary key. Can i use Non Clustered Index on Primary key
Session on 03-10-13
· Difference between Union & Union All Transformations?
· How to Run Packages Parallel in SSIS?
· How to Deploy the Cube?
· In DSV Tables or Named Query Calculations. Which one is Execute One?
· Perspectives and Explain what is use of Perspectives?
· How to Delete Duplicate Rows in a Table?
· Logging and Explain how many types are Available?
· Explain SDLC (Software Development Life Cycle)?
· Drawbacks in SSRS Reports?
Session on 12-10-13(FA)
· What is Connection Pooling
· By Using SSIS Aggregation How Many Outputs are there
· Unit Testing in SSIS
· What is Dimension Table & Fact Table
· Different Types of Measures and Explain with Examples
· Explain Clearly Architecture of UDM
· Where is used Surrogate key
· Explain about SCD’s
· Different Types of Schemas and Which one is More Normalized
· Different Types of Containers
· Different Types of Sources in SSIS
· In Ur Project used OLDB or Sql Destinations
· What is Reason u used OLDB Destination
· Synchronous & Asynchronous Transformations (Explain with Example)
· Blocking & Non Blocking & Partial Blocking also(with Examples)
· Difference between Merge & Merge Join & Union all
· With Out Sort Transformation using Merge Join Operation
· Configurations and Explain Direct & In Direct Configurations
· Explain about Sequence Container with Example
· Drill Through & Drill Down Reports
· How Many Types of Subscriptions
· How can handle Multi valued Parameters
· Which Transformation is used Update Command in Data
· How to Maintain Package Optimization
Session on 08-10-13(TL)
1. What are Types of Dimensions
2. Package Configurations used to in ur Project
3. Please Explain XML and Environment Variable Configurations
4. How u Achieve Parallelism in SSIS
5. One Flat file Contains some Codes how to eliminate the codes and finally stores in ur Database
6. What is use of Audit File in ur Packages
7. Difference between Union all & Merge Join & Merge
8. What is the Difference between OLTP and OLAP
9. What is Fact less Fact
10. What type of Reports u Worked on ur Project
11. What is Cascading Parameters
12. What are the Rendering Methods in SSRS
13. Disable One Option in Rendering Methods in SSRS
Session on 08-10-13(TR)
1. What type of Transformations u worked on it
2. What is significant of catch (Full & Partial)
3. What is Types of SCD’s
4. U Create SSIS Package. It have 10 files but i want only deploy 5 files
5. How to Pass Variables from Parent Package to Child Package (Clearly Explain with Example. In this Ex which variable is changed)
6. To create one Package with Data Source but the Data source is down that time Package run or not and What type of Errors are Occurred
7. What is Logging Information
8. How to Aggregate SSAS Cube with SSIS Package
9. U can Only Process Single Partition of the Cube
10. What is use of Having Clause in Sql
11. Can we call SP in side Function
12. What is the Difference between Derived Measure and Calculated Measures?
13. What are Functions used in MDX
14. In SSRS Detailed Report is Display in the Same Page.
15. Standard Terminology of Sub Reports in SSRS
16. What is Degenerated Dimension
· What is Connection Pooling
· By Using SSIS Aggregation How Many Outputs are there
· Unit Testing in SSIS
· What is Dimension Table & Fact Table
· Different Types of Measures and Explain with Examples
· Explain Clearly Architecture of UDM
· Where is used Surrogate key
· Explain about SCD’s
· Different Types of Schemas and Which one is More Normalized
· Different Types of Containers
· Different Types of Sources in SSIS
· In Ur Project used OLDB or Sql Destinations
· What is Reason u used OLDB Destination
· Synchronous & Asynchronous Transformations (Explain with Example)
· Blocking & Non Blocking & Partial Blocking also(with Examples)
· Difference between Merge & Merge Join & Union all
· With Out Sort Transformation using Merge Join Operation
· Configurations and Explain Direct & In Direct Configurations
· Explain about Sequence Container with Example
· Drill Through & Drill Down Report s
· How to Display below Graph
· How Many Types of Subscriptions
· How can handle Multi valued Parameters
· Which Transformation is used Update Command in Data
· How to Maintain Package Optimization
Session on 13-10-13(TL)
1. What is Data Ware house and Advantages
2. How to Improve Performance by using Analysis Services
3. KPI and Example
4. What is use of SSAS
5. How to End users performance with Cube
6. What is use of ad hoc Reporting
7. Perspectives and uses
8. Significance of cube
9. Remove orphan records in ur database( It contains 2 or 3 Millions)
10. Table contains 100 records and View contains 200 records (both the structures are same). How to get 300 records by using SQL Command
11. How to create Data Ware house
12. Types of Dimensions & Measures
13. Cleary Explain Degenerate Dimension & Role Played Dimension
14. Cluster and Non Clustered Indexes
15. How to Explain Indexes Concept Briefly with Example
16. Sub Query’s and Types
Session on 15-10-13(FM)
· Second Max of Emp without using CTE (5th after 7th)
· Please write query for Retrieve Except colour Blue Records only
Product Id
|
Name
|
Colour
|
1
|
Soap
|
Black
|
2
|
Vehicle
|
Blue
|
3
|
XXXX
|
Red
|
4
|
ZZZZ
|
Orange
|
5
|
AAAA
|
Metal
|
6
|
BBBB
|
Blue
|
· What is Difference between Delete and Truncate
· Performance Tuning in SSIS
· Explain Flow draw of ForEachLoop Container with Example
· Difference between Function and SP
· Cluster and Non Cluster Indexes
· Write a Query for Below I/P and O/P
I/P
Year
|
Month
|
Sales
|
2012
|
Jan
|
10000
|
2012
|
Feb
|
20000
|
O/P
Year
|
Jan
|
Feb
|
2012
|
10000
|
20000
|
· What is O/P
Select 1
Union all
Select 1
· How to Create Cube (Clearly Explain)
Session on 26-11-13(F_BOA)
· How to u Find Data is there or not in Source
· What are the Containers are used in ur project and why
· Explains Precedence Constraints
· Difference between DTS & SSIS Package
· How to Handle Errors in SSIS
· How to Store Error Logs in SSIS
· What are the Configurations are used in ur Project
· Variables and Explain when u used Variables in ur Project
· In ur Project u used Sequence Container
· I want to Migrate data from Flat File to Sql Server , How the Performance can be improved
· Briefly Explains How many Stages in ur Project
· Script Task and Explain how many ways to ur Script Task in ur Project
· What type of Script used in ur project and Why
· What is Paradox Server and Explains
Session on 27-11-13(F_SYM)
1) What type of destiantions u use mostly
2) Difference between ole db destination & sql server destiantion
3) What if full cache,partial and no cahce
4) When do u give ignore failure,failure component
5) what is the input query u give in lookup
6) how would you map target tables in the target table
7) what is activex control flow task
8) what is the main purpose of lookup
9) what type of joins exist in it
10) what is the main diff between execute sql task and oledb command
11) what is the use of script component
12) Briefly explain when sources,destiantions and transformations exist
13) How would you map target table column with input units
Session on 01-08-2013(T)
1. Data ware house and types (two) bill inmon and others
2. SCD and types
3. Explain Technically of scd in ur project
4. How many duplicates in table
5. Copy the structure.
6. I have a table bit of 1and 0’s ... how to update 1=0 and 0=1
7. Temp and Temp table explain
8. Cube partition and types, how to create partition in ur cube
9. Isolated Levels in package and types
10. Cube security and levels ( Customers in us to see only us data)
Session on 11-08-2013(T)
1. How to move cube to production
2. Named Calculations and where they are created?
Session on 11-08-2013(S)
1. What is u r Data source?
2. What is ftp?
3. What is the another use of cte except remove duplicates ?
4. can u write a query using orderby,group by,having and where clause in joins ?
5. performance tunning in sql server?
6. wht is index types of index?
7. wht is cluster and non cluster index?
8. trouble shooting in ssis?
9. Did u face any error in u r project? how u can resolve it?
10. i have 2data sources one data source having 1000 records another data source having 1000 record also how u can identify the row count?
11. wht package configaration? and types? and wht is use of all package configarations?
12. wht is procedures? can u create simple procedures or complex preocedures?
13. triggers and types?
14. how ucan delete the duplicates like name and gender without id tell me?
15. wht is the use of sequential process and paralell processing in ssis?
Session on 11-08-2013(R)
1. 10 text files how to read from folder?
2. Difference merge and union all?
3. Deploy the Packages?
4. Named Set?
5. Calculations?
6. Parallel Period in Ur project?
7. Ascendant and descendent?
8. Set and Tuple Difference?
9. Table and Matrix Difference?
10. Strtoset or strtomember in mdx?
11. Look up
12. Parameters pass to mdx
POLARIS:
1. WHAT IS A DIMENSION and FACT?
2. WHAT IS SLOWLY CHANGING TRANSFORMATION?
3. WHAT IS AGGREGATE TABLE?
4. WHAT IS EXECUTE SQL QUERY?
5. WHAT IS THE FLOW FOR UNZIPPING A FOLDER which is password protected and transform to destinaton?
6. what is the difference between CLUSTERED and UNIQUE indexes?
7. xml task?
8. lookup key?
9. what is parallelism?
10. I have a table with 3 rows and having the identity property for a column. If I delete the 3rd row, what will be the identity?
FIS INTERVIEW
1.CUBE in SSAS alternative name?
Ans: Unified Data model
2. SELECT ROUND(748.58,-4) o/p: 0
3. code to delay the execution of stored procedure for 20 min
ans: WAITFOR DELAY '0:20:00'
4. SCD stands for?
SLOWLY CHANGING DIMENSIONS
5. Stored procedure for renaming a table? sp_rename
6. Is it possible to login to SSAS with SQL Server login? Ans: no
7. Which transformation is used To check the frequency of a particular Term?
Term Lookup
8.How many no. of levels can a stored procedure be nested?
Ans: 32 levels
9. Is it possible to backup SSAS database into SSMS?
10. Which kind of SSIS configuration is not needed to store in local system?
Ans: SQL Server
11. when the SSIS package is updated with check point "Always", what will be happened?
Ans:"Always" Specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.
12. How to backup packages in msdb database?
13. what is the property to set to allow checkpoints for a task on failure?
Ans: FailPackageonFailure
COGNIZANT:
1. what are system databses in SQL server?
Ans: master,model,msdb,Tempdb
2. what are DML queries?
Ans: INSERT, UPDATE, DELETE
3. Join queries without JOIN key words?
4. We have two tables table 1 and table 2. To select non-matched rows from table 1, what is the join applied?
Ans: Left outer join
5, what are the types of deployment in SSIS?
Ans: 1. Cretae a Deployment manifest file from package properties,
Copy the package file
execute the deployment utility file.
2. In BIDS, go to Navigate to File menu --> save the copy as Package......
3. DTUTIL command from command prompt to save,copy,paste ....
4. Login to SSMS with Integration services login
6. what is the challenging transformation you used?
7. How the performance tuning handled in SSIS?
Ans: Parallelism
8. SSIS configuration types?
Ans: XML configuration,
Environment variable configuration,
SQL server configuration,
REGISTRY Entry Configuration,
Parent Package configuration
9. Difference between DELETE and TRUNCATE?
10. If there is a storedprocedure called inside another stored procedure, how to set the condition that if the first procedure fails, the other one also has to fail?
11. Difference between function and procedure?
12. Error handling in SSIS?
13. difference between Rank and dense Rank.
14. Difference between cte, # table and temp table.
15. what is cte? and syntax of cte?
JOINS, EXCEPTION HANDLING, ERROR HANDLING IN T-SQL
Alliance:
1. What ae the steps involved in deploying a package? (steps by using Deployment manifest file)
2. Error handling in SQL server? (TRY CATCH)
3. how the RAISEERROR can be called?
4. purpose of right outer join? (not definition)
5. We have two sets of tasks.One critical and another one is normal. The critical task has failed and normal has been succeed. how to get rolled back of the transactions performed by critical task?
Anser this question for both SSIS and SQL server
MINDTREE:
1.how to identify historical & changinging attributes in SCD?
2.How to load multible csv source files to one destination? (with use of Bulk insert and for each loop container)
3. How to pass a variable from one package to another? (from task to another)
4. A query to retrieve the data from a table with Self join
5. We have two different source files. i.e., Product and Category. If the execution of Product has been failed and category is succeeded. What will be impact of the package in Datawaehousing?
6. what is a confirmed dimension?
7. what are star schema and snow flake schema?
8. what are partitions?
9. what is a surrogate key?
10. difference between merge and Union all?
11. how can you remove duplicate data in SSIS?
12. when to use merge and when to use union all?
13. what is identity column?
14. what is delay validation?
L&t:
1. Execution tree
2. Cursors
3. Diffrence between logical joins and normal joins
4. Scenario for joins
5. SCOPE_IDENTITY & @@IDENTITY
6. cte and temp table
TCS:
1. what is SET NOCOUNT?
Ans:
Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.
But this message creates an extra overhead on the network. By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the performance of our database and our application.
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
Example:
SELECT * FROM [STUDENTDB].[STUDENT];
Messege:
(664 row(s) affected)
-------------------------------------
SET NOCOUNT ON
SELECT * FROM [STUDENTDB].[STUDENT];
SET NOCOUNT OFF
Messege:
Command(s) completed successfully.
SET NOCOUNT NO statement can be useful in store procedures. SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement. Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
-----------------------------------------------------------------------------------------------------------------
2. diff between CHAR & VARCHAR?
3. How to pass a variable from parent to child packages? where can we use the variables in this scenario?
ans: Using Parent Child Configuration.
we can use the variables in this scenario'Value' property of the child package variable
4. Types of Configurations?
5. How to call a stored procedure with output parameter?
6. when to use the parameters with "@.." and "?" ?
7. what are the types of connection managers?
8. what is the difference between OLEDB and SQL server?
9. what is @@identity?
10. can we send html in send mail task?
NUWARE SYSTEMS:
----------------
1. ACID Properties.what are the Isolation levels is SQL server?
Atomicity
Consistency
Isolation
Durability
Isolation levels:
Read committed
Read committed Snapshot
Read uncommitted
Repeatable read
Serializable
2. difference between Function and Procedure?
3. Difference between Clustered and non-clustered indexes?
4. Performance tuning in stored Procedure.
5. what are the types of triggers?
6. How to know the stored procedure execution status in execution plan?
7. ResultSets in Execute SQL task?
Ans: None, Single row, Full resultset, XML
8. Types of Log Providers.
9. Types of Configurations.
10. Check points.
11. For each loop containers.
12. Cache methods in Lookup.
13. how do we handle errors in SSIS?
14. Difference between Star schema and Snow flake schema?
15. what are the 5 relationships in cube?
ASPIRE SYSTEMS?
1. difference between varchar and nvarchar?
2. data type for images?
3. system databases?
4. msdb and tempdb databases properties?
5. performance tuning in Stored Proc?
6. triggers types?
7. how to declare no. of rows effected in stored Proc?
Ans: @@ROWCOUNT
8. difference between function and procedure?
9. how to call a function?
10. types of joins?
11. Is it possible to execute a package created in 32-bit system in 64-bit system and vice versa?
12.
L&T:
1.To select a table as a variable in execute sql task and display full content of a table what is the data type of the variable and what is the result set type?
2. How to insert explicitly into an identity column.
3. what is derived column in SQL?
4. what is schema binding?
5. what is lookup component in SQL?
6. which of the following is an SSIS configuration? a. XML, b. Environment variable 3. Registry entry. d. none of the above
7. Merge statement in SQL
8. Configuration types in SSRS?
9."Broker enabled" property for system databases.
10. Is it possible to edit .rdl file in SSRS?
11. Default value for Protection Level of SSIS package?
Ans: EnableSensitivewithzuserKey
12. Break points can be enabled at which level?
a. control flow level b. Packa level c. DFT transformation level
13. Event handlers at which level?
14. Precedence constraint attributes?
15. what is the language to generate reports in SSRS?
a. Visual Basics b. C# c. C++ d. all
16. COALESCE function purpose
17. Max Size of a table in SQL?
18. Max no. of columns in a table
ans: 1024
Columns per UPDATE statement (Wide Tables) -- 4096
Bytes per row -- 8060
19.SQL server agents can be started only manually?true or false?
20. what is cte?
21. which of the following is not a type of temporary variable?
a. temp b. global c. local
ans: a
22.What is the max number of parameters that can be passed in stored procedure?
Ans: 2100
Max no. of columns in a table ans: 1024
No. of Columns per UPDATE statement (Wide Tables) -- 4096
No. of Bytes to store a row -- 8060
max number of parameters that can be passed in stored procedure: 2100
OUTER APPLY, COMPUTE BY, MERGE
SYNTEL:
1.Difference between script component and script task
2. OLEDB command transformation
3. filter indexes
4. Staging
