MSBI SSIS-SSAS-SSRS interview Questions and Answers :-

SSIS :

1. What is a package?


2. What is a workflow in SSIS?

MSBI SSIS-SSAS-SSRS Interview Questions and Answers

3. What is the Difference between control flow Items and data flow Items?


4. What are the main components of SSIS (project-architecture)?


5.different components in SSIS package?


6. How to deploy the package?


7. Connection manager:


8. Tell the Utility to execute (run) the package?


9. How can u design SCD in SSIS?


10. How can u handle the errors through the help of logging in SSIS?


11. What is a log file and how to send log file to mgr?


12. What is environment variable in SSIS?


13. about multiple configurations?


14. How to provide security to packages?


15. as per error handling in T/R, which one handle the better performance? Like fail component, redirect row or ignore failure?


16. Staging area??


17. Task??


18. Event handler & logging?


19. Import & export wizard?


20.what solution explorer?


21. Precedence constraints?


22. Data pipeline?


23. TRANSFORMATIONS?


24. Batch?


25. Use Of Event Handlers


26. Diff b/w script task & script component and its advantages.


27.What is a Master package.


28. Have you worked with data source view in ssis.


29.If there are 100 Packages, do you create 100 configuration files or u create how many.

SSRS:–

1. What are the main components of reporting services?


2. Where can u publish the report?


3. What are the necessity things for creating matrix report?


4. For generating reports which is used like RDBMS OR CUBE?


5. What is .rdl file?


6. types of parameters


7. what are drill through and drill down reports.

SSAS:-

1. What are the fixed measure and calculated measure?


2. What are measures?


3. What are cubes?


4. What are virtual cubes?


5..Difference b/w Cube-Deploy and Process.


6.Different kinds of Dimensions. like Degenerate dimension, Conformed dimension, Junk dimension with example.


7.About Cube-Partitions; its advantage?


8. What is the use of data source view.


9.I will have 10 dimensions. i will delete one and  will process the cube .is that deleted dimension will available in cube?

DATAWARE HOUSE CONCEPTS:-

1. Difference b/w OLTP AND OLAP?


2. Difference b/w star schema and snowflake?


What are fact tables?


3. Types of facts?


4. Data warehouse?


5. Data mart?


6. What is OLAP?


7. What is OLTP?


8. What are dimensions?


9. What are conformed dimension?


10. Staging area?


11. Fact grain (granularity)?


12. What is a fact less fact table?


13. What are measures?


14. What are cubes?


15. What are virtual cubes?


16. SCD’s?

SQL-SERVER-2005:-

1. Surrogate key?

2. Primary key?
a) It can be used to uniquely identify every row of the table.
Unique + not null

3. Foreign key?
a) It is a column r combination of columns that contain values that are found in primary key of some table.
It may be null, not unique.

4. ComposIte key?
a) It is a primary key consisting of more than one column.

4. Indexes?
a) It is an access strategy that is a way to sort and search records in the table.
Indexes are essential to improve the speed with which records can be located and retrieved from a table.
Types: – cluster index (can create only one index on table)
Non-cluster index (can create up to 249 indexes on table)
Unique index
Composite index
(Simple index, reverse key index, bitmap index, function index)

5. View?
a) It is used for data security reason
to reduce the redundant data.

6. Cluster?
a) 1-many access path.
Clusters are used to store data from Difference tables in the same physical data blocks.

7. Sequences?
a) It is used to quickest way to retrieve the data.

8. Cursors?
a) Implicit cursor
explicit cursor
Parameter cursor

9. Triggers?
a) Row trigger
Statement trigger

10. Transactions?
Save point
Commit & rollback.

11. Security?
a) Encryption
Locking
Level of locking row level, page level, table level

12.constraints?
primary
foreign(reference)
check
unique

13. Difference b/w having and where?
a) after performing ‘group by’ operation ‘having will again filter the records based on having condition
‘where’ is used to filter the data based on a condition and It applies to retrieve on a particular column.

14. Joins?
a) Join can combine the information from two tables into a single unit.
Inner join:-
they matched the records together based on one or more common fields (matched-records only).
Outer join:-
full join:-It combines the all rows on both sides of the join.
Cross join:-

15. Union & union-all?
a) Union:-columns, data types should be same
Select distinct values
Remove duplicates
Union-all:-displays all the rows exact & duplicates.

16. Difference b/w drop, delete & truncate?
Delete:-delete all rows at a time
delete a single row data based on condition.
Memory allocation will be there
structure will be there
Truncate:-delete all rows at a time
can’t delete single row at a time
memory allocation deleted
table structure will be there
Drop: – delete all rows at a time
can’t delete single row at a time
memory allocation can be deleted
table structure also be deleted

Database:

1) How u done partitions.
If your database contains very large tables, you may benefit from partitioning those tables onto separate filegroups. This technology, introduced in SQL Server 2005, allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance.

2) Difference b/w view and materliazed view.
Views
A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Materialized Views
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete

3) Have u worked with sequences.
CREATE SEQUENCE will enter a new sequence number generator into the current database. This involves creating and initializing a new single-row table with the name seqname. The generator will be owned by the user issuing the command.