Guys,
Based on my friend request i have been posted latest questions with answers.
Please let me know if you have any comments and keep me updated with the latest questions, i will try to post you answers ASAP.
Take care
~Rav’s
SSIS
1) Use Of Event Handlers
Based on event (On error, on Progress, On Pre/Post Validate..), if you want to perform some action like send email, insert log info into table etc.. we can go for Event Handler Tab.
http://msdn.microsoft.com/en-us/library/ms140011.aspx
http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
2) Diff b/w script task & script component and its advantages.
Script task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.
Normally we can implement custom code by using these components.
By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well. In single sentence i can conclude the usage of this items we can use to perform any ETL operation if you are good at .net.
http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/
3) What is a Master package.
Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called as master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.
http://sqlblog.com/blogs/jorg_klein/archive/2010/01/04/ssis-package-design-pattern-for-loading-a-data-warehouse.aspx
4) Have you worked with data source view in ssis.
You can simply say ‘i never used so far’.
A DSV allows you to create a metadata layer over an existing Data Source. Within a DSV, you can select a subset (or all) of the tables and views available from a Data Source, rename any of the tables/views and/or their columns, add new Named Calculations (which act like computed columns within a table/view), and add new Named Queries (which are complete select statements, which basically act like views).
http://database.blogs.webucator.com/2010/10/20/how-to-create-data-source-views-in-sql-server-integration-services-2008/
5) If there are 100 Packages, do you create 100 configuration files or u create how many.
The answer is based on the requirement we will create 100 config files for 100 packages.If i go for master package and if i am using same kind of connections for the child packages like source and destinations connections then we will create less config files, because Master Package will pass all the connections using master-child relation using the variables. Creation of config files is dependence of the requirement.
http://www.mssqltips.com/tip.asp?tip=1434
http://www.sql-server-performance.com/articles/dba/package_configuration_2005_p1.aspx
ssas
1) Difference b/w Cube-Deploy and Process.
Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual Sql server analysis server. This process is called DEPLOYMENT of cube. (like creating one object(table) in the relational database)
So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table)
So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to a SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube.
Best Practice method to deploy a cube:
http://www.mssqltips.com/tip.asp?tip=1883
2) Different kinds of Dimensions. like Degenerate dimension, Conformed dimension, Junk dimension with example.
Different types of Dimensions:
I.SCD (Slowly Changing Dimension- Type 1 Type 2 and Type 3)
Type 1: The new record replaces the original record. No trace of the old record exists.
Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.
Type 3: The original record is modified to reflect the change
II. There are other three types of Dimensions:
1. Confirmed Dimensions.
2. Junk Dimensions.
3. Degenerate Dimensions.
-Confirmed Dimension:
The dimensions which is used more than one fact table is called conformed dimensions. It means we can definitely use CD in the nature
Ex: Time,Geography,customer,employee,product etc..
-Junk Dimension:
Junk dimensions are dimensions that contain miscellaneous data (like flags and indicators) that do not fit in the base dimension table.
-Degenerate Dimension :
A degenerate dimension is data that is dimensional in nature but stored in a fact table. For example, if you have a dimension that only has Order Number and Order Line Number, you would have a 1:1 relationship with the Fact table. Do you want to have two tables with a billion rows or one table with a billion rows. Therefore, this would be a degenerate dimension and Order Number and Order Line Number would be stored in the Fact table
III. Cube Dimensions
a. Fact Dimensions
b. Role-Playing Dimensions
c. Reference Dimensions
d. Many-to-Many Dimensions
http://consultingblogs.emc.com/christianwade/archive/2005/04/07/1255.aspx
http://www.informaticans.com/blog/data-warehousing-faqs/dimensions-different-types/
3) About Cube-Partions.its advantage.
We used Partitions to Improve query performance.
A partition is a file on a hard disk that contains a subset of the data included in an Analysis Services database. Partitions let you spread data over multiple hard disks. This includes combinations of both local (stored locally on hard disk) and remote (distributed across multiple hard disks) partitions. Partitions rely on storage settings to define the format and processing schedule for the database, and they use writeback settings to enable what-if analysis. What-if analysis enables a user to input their own data and evaluate the changes that cascade throughout their cube.
Method to create Partitons:
http://www.mssqltips.com/tip.asp?tip=1549
4) What is the use of data source view.
Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source
5) iIhave 10 dimensions.i will delete one.and i will process the cube .is that deleted dimension will available in cube?
If you want to remove the dimension from the cube, you can hide/delete the dimension then re-deploy the cube and you need to perform the full process then the deleted dimension won’t appear in the cube.
6) diff b/w calculated members and calculated measures
Member is nothing but column on dimension table. Measure is nothing but column on fact table.
The term calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the measures dimension where a simple MDX expression such as addition or subtraction of two or more base measures results in a new measure. Such calculated members on the measure dimension are referred to as calculated measures.
Calculated Member is related to the Dimension where as calculated Measure is related to the measure in measure group.
http://msdn.microsoft.com/en-us/library/ms166568.aspx
SSRS
1) types of parameters
Single value parameters, Multi value parameters,
Cascading parameters (one parameter is dependent on another parameter selection)
http://msdn.microsoft.com/en-us/library/aa337234.aspx
http://sql-bi-dev.blogspot.com/2010/07/report-parameters-in-ssrs-2008.html
2) what are drill through and drill down reports.
Drill down is when a power play model has several levels within a hierarchy. Given a location dimension with country/ state and city. A starting view will show the summary of countries, Drill down on one country will expose their states, drill down on a state will show their cities. So drill down goes to lower levels of a dimension as designed in the model.
Drill thru is the ability to leave PowerPlay and call a SQL Query, (say in Impromptu) passing the dimension line as filters to the query. If the columns in the query match the columns used to build the dimensions the query will automatically apply those values to the where clause. You can write any query or queries you want to support a drill thru from a cube. Context is important to balance the two
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.
http://www.mssqltips.com/tip.asp?tip=1914
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.
http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx
4) About logging in view.
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6003.htm
Frequently asked Sql Queries with answers
http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/
http://www.java2s.com/Code/SQLServer/CatalogSQLServer.htm
Some Other Interview Questions:
http://www.bigresource.com/MS_SQL-ssis-master-package-fail--MaixRNiy.html
Try to get the info from net.I do not have time to explain all these...
DTS vs SSIS: A basic overview
http://decipherinfosys.wordpress.com/2008/03/11/dts-vs-ssis-a-basic-overview/
How to create a basic Cube
http://www.mssqltips.com/tip.asp?tip=1532
