Note:
Vinaytech is not responsible for questions and answers. Just copy and paste from students
INTERVIEW QUESTIONS:
INDEX SEEK VS INDEX SCAN,
INDEX: are created on existing tables to locate rows more qiucly and efficently.
Seek Actions occours to retrieve records from the table. It means you were able to locate the records with out
having to examine every row to locate those records.
Scan occurs when there is no index available or when a poorely created index exisits on a t able.
In a table scan, sql examine every row in a table to satify the query result.
What is a covering index , filter Index ?
=============================================================================================================
UNION , MINUS , INTERSECT
UNION: Record sets are combined from multipe sources.
In Union query only distinct records are selected. No duplicates.
Union checks record by record to filter duplicates , so it will take longer time than Union all.
Union all reurns all records including duplicates.
UNION AND UNION ALL operator requires that query should have same no of column and data types should identical.
datatypes sgould compatabile.
MINUS/Except : All the rows in the first query which are not found in the second query.
INTERSECT: Used to retrieve the common records from left and right query of the Intersect operator. (Same as
Inner join)
when using the INTERSECT operator No of column and order must be same and datatype must be compattiable.
===============================================================================================================
What is FILL FACTOR: SETTING APPLICATEBLE TO INDEX IN SQL SERVER .
HOW MUNCH DATA IN RETAINED ON INDIEX PAGE WHEN IT IS CREADTED ?
========================================================================================================
TYPES OF BACK UP: DIFFERNETIAL DB , FULL DB , TRAN LOG BACK ?
FULL BACK UP: it contains all the data in DB ( set of file groups & Files and also enough log to allow for
recovering the
data).It is the base of both differential back up and transactional log back up.
Differential Back up:
A differential back up is not independent and it must be based on the lasted full backup of the data. that
means there
should have a full back up of the data. A differential back up contains only the data that has changed since
the differential base. typically Differentail back up are smaller and faster to create than the base of full
backup and also require less disk.
Differential back up can save available space and speed up the process of making frequent back up to decrease
the risk
of data loss. At restore time, the full back up is restored first, followed by the most recent differential
back up.
========================================================================================================
CAN WE INSERT THE DATA IF THE CLUSTED INDEX IS DISABLED.. Ans: NO
What is Clustered Index and Non-Clustered Index?
CLUSTERED INDEX: Clusted index determine physical organisation of the data.
2005 : 1 CLUSTERED INDEX + 249 NON CLUSTERED INDEX
2008 : 1 CLUSTERED INDEX + 999 NON CLUSTERED INDEX
When you create a Primary Key on a table, a clustered index is created by default.
NON CLUSTERED INDEX are usefull when users require multiple ways to search for Data.
===============================================================================================================
====
What is meant by Trigger ?
Tigger is a sql procedure that intiates an action when a event (like Insert, update, Delete ) occours on any
object.
DML TRIGGER
INSTEAD OF TRIGGER: are fired in the place of triggering actions such as insert, update and delete.
AFTER TIGGER: are fired following the trigger actions such as insert
, update and delete.
DDL TRIGGER
are fired on DDL Statments like Create , drop , Alter . they are always after tiggers.
LOG ON EVENT: this type of trigger is fired aganist the LOGON event before a user session is establised
===============================================================================================================
======
Diff between getdate() and sysdatetime()?
getdate()- Precession is in milli seconds
sysdatetime()- Precessin is in Nana seconds.
===============================================================================================================
======
What is the use of TOP Command ?
Top is a row limiter helps you limit the no of records affected by DML statmenet.
Accepts Variables and literal Values. it can be used with INSERT, UPDATE, DELETE statment.
===============================================================================================================
=======
SERVICE BROKER:
serive broker is a message queuing technology in sql server that allows developers to integrate sql server
fully into
distribuded applications. It allows databases to send a message without waiting for the response.
so that application will continue to function if the remote DB is temporarily Unavailable.
===============================================================================================================
=======
FILE STREAM:
File stream allow you to store unstructured large object like text, image, videos.
File stream integrate the sql server database engine with NTFS . it stores data in varbinary(max) datatype.
===============================================================================================================
=======
CONSTRAINT:
constraint performs data validations & Maintain the DB integrity by preventing Invaild data being entered.
TYPES
CHECK CONSTRAINT: It restricts the value that user enters into column during INSERT & UPDATE.
===============================================================================================================
=======
STORED PROCEDURE:
SP is a object Stored in DB & contains one or more lines of SQL Code,these stms run in a single execution.
SP is a pre-compiled code that can be reused.
SP can act like Views & selects the Data, they can also make updates and create objects
or Set up a DB back & perform various task.
It reduces networK traffic and improves performance.
Also, prvide Integrity of the Logics.
FUNTIONS:
Function is a object Stored in DB & Contains SQL Code that accepts Parameters.
USERDEFINED FUNCTION:
It is a user defined object Stored in DB & Contains SQL Code that accepts Parameters, Perform task & return
result.
===============================================================================================================
=======
DIFF b/W Function & SP.
Functions and Sp are similar the way they work, but you must call a Function using a SELECT stmt or a WHERE
Clause
with in a SELECT Stmt where as SP cannot be used
Functions can be used any where in where/having/select clause where SP cannot be used.
===============================================================================================================
======
DIFF b/W Triggers & SP.
SP are not event driven and not attached to a specific table as most triggers are .
SP are executed by invoking a call to the procedure . Triggers are executed by events.
Triggers can also execute SP.
===============================================================================================================
======
What is SSIS CONFUGURATION: TYPES OF CONFIGURATION ?
============================================================================================================
STUFF FUNCTION in SQL: ?
===============================================================================================================
======
What is linked servers?
Linked server configuration enables Sql server to execute the queries in remote servers.
System supplied stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to add
new linked servers.
===============================================================================================================
======
What is a Cursor?
A cursor is a Database object used by applications in the procedural logic to manipulate data in a
Row-by row basis, instead of typing sql commands that operate on all.
In order to work with cursors, we need to perform these steps in the following order
1) Declare cursor
2) Open a cursor
3) Fetch a row from the cursor
4) Process the Fetched row
5) Close Cursor
6) Deal locate the cursor.
===============================================================================================================
======
What is the difference between the DELETE and TRUNCATE Commands?
The delete command removes the rows from a table on the basis of the condition that we provide in the where
clause.
Truncate will remove all the rows from the table and there will not be any data in the table after
running truncate command.
TRUNCATE:
Truncate is faster and uses few system and transaction log resources than delete.
Truncate removes data by deallocating the data pages used to store table data and only page deallocations are
recorded in the transaction log.
Truncate removes all the rows from the table but the table structure, its columns, Constraints and indices
remain.
We cannot use truncate table on a table referenced by a foreign key constraint.
Truncate cannot be rolled back unless it is used in a Transaction.
Truncate is a DDL Command
Truncate Resets the identity filed of the table
DELETE:
Delete removes only one record at a time.
Delete can be rolled back
Delete is DML Command
Delete does not reset the identity of the table.
Delete can be used with or without where clause.
===============================================================================================================
======
to find nth RAnk
select top 1 StateProvinceID from
(select distinct top 8 StateProvinceID From Sales.SalesTaxRate
order by StateProvinceID desc) a
order by StateProvinceID
===============================================================================================================
======
What are ACID Properties?
===============================================================================================================
======
Normalisation uses?
===============================================================================================================
======
What are Isolation levls ?
===============================================================================================================
======
Matrix ,tabular, List - Difference ?
===============================================================================================================
======
how error can be handled in SQL/SSIS ?
===============================================================================================================
======
What are Checkpoints ?
Checkpoints cannot be applied on Data flow Level.
===============================================================================================================
======
What are Control Flow tasks?
===============================================================================================================
======
Difference b/w table variable and Temporary variable ?
===============================================================================================================
======
What is the USE of CTE
===============================================================================================================
======
diff b/w CTE And Temporary table?
===============================================================================================================
======
Different types of constraints in sql ?
===============================================================================================================
======
different types of transformation you have used-Expain them with examples ?
===============================================================================================================
======
Q) differences b/w 2008 R2 and 2012
===============================================================================================================
======
Q) How many evironments in your Project
Ans) 2 or 3
Production (DC,DR) & UAT (testing )
DC: there are 3 DB servers and 3 application servers
DR: there are 2 DB servers and 2 application servers
UAT: there is only one DB & Application server
At any point of time we will have access to DR or DC. Data sinking is done automatically after every 5 minutes.
Reports will not be automatically synced and we have to manually do it.
Ideal practice: When we are deploying the reports, it better to deploy it both the servers (DR & DC).
DC :
Database server
1) Misdb001 Main Database & Report server is also maintained here.
2) Misdb002 AB_STGDB
3) Misdb003 it is backup of Misdb001 (data will automatically sink into this DB, if Misdb001 goes down, we can
use this Database.
Application servers
Misap001 ( it will act as a cluster and redirect the request to NLB and balances the load across the servers
while users are accessing the application. )
Misap002
Misap003
DR:
Database server
Mis101 Main Database & Report server is also maintained here.
Mis102 it is backup of Misdb001 (data will automatically sink into this DB, if Mis101 goes down, we can use
this Database.
Application servers
Misap001 (here we are giving IP and no DNS concept is available here)
Misap002
======================================================================================================
HCL
1.How many packages created
2.Mearge & Mearge join Diffen
3.Union & Union All (Database)
4.Lookup Transform
5.Conditional Spilt Transform
6.Viewes (database)
7.Write a 2 higest sal use stroed procedure
8.what is diff table & view
9.Stored procedure
======================================================================================================
Patni
1.Flat different types
2.10 Records are there 3 gents and 7 womens by use conditional split
3.Derived column
4.Look Up transform
5.Look up & Fizzy look up diff
6.Foreach loop(container)
7.Scd
8.Conditional Split
9.Staging Ariea
10.Surget key types
11.Connection Mager
======================================================================================================
Sourceone
1 Excel =Transform = Destination these package move to sequence container any changes is come?
2.For each loop
3.look up,Derived column,Conditional split
4.Configaration
5.Deployee
6.Security
7.Scd
8.Table vs matrix
9.Drill down & drill through
10.Deployement
11.how many roles
12.Linked reports
13.Control flow means
14.How many primary keys and fact tables used in Project?
15.Index & Index Types
16.Procedures & functions
17.Triggers
18.Configaration Types
19.How to process the package
20 FTP Task ,Fille system task
21.Cube Genaration
22.Jion types and what is self join
23.Table Vs temperory variable
24.Common table expression
======================================================================================================
Marlabs
1.Control flow troubleshooting
I gate
1.Configaration
2.Logging
3.Check points
4.Mearge Join
5 Table records month wise instead of month wise jan display in destination I want month number(Jan) reprsents
(1)
6.Syconranise Vs Asyconraise
Theram
1.Delete duplicate records
2.Common table expression
3.Cross tab properties
4.Foreach loop
5.Login Vs User name
6.Windows authentication VS sql server authentication
7.Index and cluster index
8.Triggers and cursors
9.Magic Table
10.firstname,Deptno,employee no need get employeenumer wise who is the manger of the particular employee wise
11.database vs datamart
12.Star schema vs snow flake schema
13.Sp=help va sp=help taxt
======================================================================================================
Monthan Systems
1.Duplicate records
2.stuid,Subid,marks subject wise avg marks?
3.Scd 4.Mearge Join
4.Fact Table
Mind tre
1.eid,Ename,Dob,sal write the query
Eid Ename Dob sal
1 A 3
2 B 3
3 C 3
4 D 1 who is the manger of eid?
2.space ABC,ABC Space how to find out or I nedd to display with out space
3.In ETL source and distination mismatch?
4.In source 100 records are there ,in Destnation load 95 records what you are doing?
======================================================================================================
Inteligroup
1.Bank project credit card process how to implement?
2.Blocks Vs Non Blocks
3.SCD,ACID in sql how to sue
4.for loop vs for each loop
5.Join s
Emirtius
1.Intersection & except
2.Locks
3.Inner join & Outer join
4.Deployment.
5.cube creation what is cube?
6.cube Configaration
7.report deployment
8.Clusterindex & Non clusterindex
======================================================================================================
Spike Systems
1.control flow Vs Dataflow
2.index
3.Mearge and merge join
4.merge and union all
5.What are datamodular then in your project
6.star vs snowflake schema
======================================================================================================
Haxwere
1.SSIS security
2.SSIS transformation
3.merge & merge join
4.project & Deployment.
Capgemini
1.Modeling Types
======================================================================================================
Netpostive
1.Project
2.Merge & Merge Join
3.performance of SSIS
4.Deplyment & Schuduling
======================================================================================================
ANZ Technologies
1. Scheduling,Deployment,Break points
2. Index types
3. OlTP source Conditionl trans Excl destination Task is data destination is load inti sheet 1 and sheet 2
load is it possible?
4. Source flat filles 4 records is there 4 records load into tables 4 destination tables?
======================================================================================================
Keneeindia
1.Coinfigaration Types
2.Deployment types
3.Report1 and report2 need to store in same excel is it possible?
4.In sql data empname,Ename,add1,add2,add3,add4
In report I want empname empid add
Add1
Add2
Add3
That type format report ?
======================================================================================================
Symphoney
1.Delete duplicate rows
2.2 nd highest sal
3.Derived column Vs copyof column
4.2 nd highest salry in ssis
======================================================================================================
TCS
1. Dimension table
2.how to connect starschema to snow falke
3.union all ,mearge
4. Configaration
5. Exception handling stored procedure
6.Multiple table view creation possible or not
7.Condition wise colors need to change in reports?
8Date wise total
9.case cading parameters
10.select getdate () after that mm,dd,yy format want?
11.Fuzzy lookup
======================================================================================================
Satyam
1. Project
2. One package how to control different source like flat fille,Excel,oledb
3. What is delay validation
4. In SSIS source like flatfille 10 records 5 records nullvalue another don’t save null values in
destination?
5. What is view?
6. Diff stored procedure and functions
======================================================================================================
MARLABS
1.Project
2.Merge function in sql
3.Logging
======================================================================================================
Satyam
1.Project
2. Control flow
3. Control flow where you write in storedprocedure
4.Error handling in ssis
5.logging
======================================================================================================
Marlabs
1.Max functions are used
2.MDX syntax
3.Matrix report
4.Drill down ,drill through
5.Report in cube
6.Alternate colour in cube
7.Report performance
======================================================================================================
Inteligroup
1.What are sources of project
2.Fact table source and dimention source
3.What is business key and surgetgey
4.in Drill down subreport calling or not
5.Drilldoen and drill through
6.Condition with in condition is possible or not?
======================================================================================================
Syntel
1. Tell me about your self
2. Scd type1 and type2
3. Suroget key
4. Multicost vs conditional split
5. Merge and union all
6. Union ,union all
7. Jions
8. Star schema ,snowflake schema
9. Incrmental loding
10. Logging
11. Configuration types
12. I have 6 tasks are there 4 th one failed ,how you will handle this?
13. Blocking,unblocking transformations
14. Xml how to handle
15. Check points
======================================================================================================
SIS
1.How to load oltp to database explain
2. Cube?
3.lokup and fuzzy lookup
4. Extract handling ssis
5.How to create variable ,Dynamically changing variable is there how to handle?
6. Shecduling package
7.Cube processing?
8.Flat fille ,xml source is there and ftp task so will can 10 clock morning idont know which fille come so
remaining fille are need to store along with fille?
======================================================================================================
L&T
How to generate execute query I want cumulative total
2.Nth hight Sal
3.In excel 100 records is there in source table loaded into taget destination .After 76 th one delete
information after again load into destination any problem is comeing?
4.I source table 32 bit information is there but destination 64 bit how iyou insert table?
======================================================================================================
Comcast
======
1. Brief introduction to my background
2. Rate yourself on sql(1 - 10)
3. Questions on Joins
4. Diff between Where and Having
5. Indexes, Pro's and con's
6. How to improve slowing running queries
7. SCD(Examples)
8. Custom code
9. how to return query result as XML
10. do you want to do ETL or Tableau job?
11. Challenges you faced
12. Max size of nvarchar
13. Joins vs Subqueries
14. SP use case
15. How to store image in db coming from application(data type)
16. How do you work when req's change
17. why comcast
18. what kind of viz in tableau did u built
19. Error handling in SQL
20. Rate your comunication skills
========================================================================================================
Tempur Sealy
. Tell me about self.
2. Explain any of your project?
3. what is the differnce between Varchar and Nvarchar, what is the maximum size of them?
4. what are the interger data types that you are familiar with?
5. Explain about Merge statement in SQL?
6. Explain about the transactions in SQL? Tell the syntax for that?
7. Difference between CTE and Temp table?
8. Differnece between Foreach loop and Sequence container?
9. Slowly changing dimensions?
10.Types of index and where it is used?
11.How do you optmize the stored procedure?
12.Difference between package level and project level parameters?
========================================================================================================
Vinaytech is not responsible for questions and answers. Just copy and paste from students
SQL
SERVER INTEGRATION SERVICES [SSIS] INTERVIEW QUESTIONS
|
What
do we mean by dataflow in SSIS?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.
•
What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package. 10 unique conditions exist for each breakpoint. Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.
•
Can you name 5 or more of the native SSIS connection managers?
1) OLEDB connection – Used to connect to any data source
requiring an OLEDB connection (i.e.,
SQL Server 2000) 2) Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file 3) ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task 4) Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task 5) File connection – Used to reference a file or folder. The options are to either use or create a file or folder 6) Excel
•
What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.
•
What is Conditional Split transformation in SSIS?
This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions. For example, we can organize the transform for the students in a class who have marks greater than 40 to one path and the students who score less than 40 to another path.
•
How do you eliminate quotes from being uploaded from a flat file to SQL
Server?
This can be done using TEXT QUALIFIER property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
•
Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package: CheckpointFileName – Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name. CheckpointUsage – Determines if/how checkpoints are used. Choose from these options: Never(default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist. SaveCheckpoints – Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.
•
What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution: 1) Never: The package will not use a checkpoint file and therefore will never restart. 2) If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written. 3) Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.
•
What is the ONLY Property you need to set on TASKS in order to configure
CHECKPOINTS to RESTART package from failure?
The one property you have to set on the task is FailPackageOnFailure. This must be set for each task or container that you want to be the point for a checkpoint and restart. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.
•
Where can we set the CHECKPOINTS, in DataFlow or ControlFlow ?
Checkpoints only happen at the Control Flow; it is not possible to checkpoint transformations or restart inside a Data Flow. The Data Flow Task can be a checkpoint, but it is treated as any other task.
•
Can you explain different options for dynamic configurations in SSIS?
1) XML file 2) custom variables 3) Database per environment with the variables 4) Use a centralized database with all variables
•
What is the use of Percentage Sampling transformation in SSIS?
Percentage Sampling transformation is generally used for data mining. This transformation builds a random sample of set of output rows by choosing specified percentage of input rows. For example if the input has 1000 rows and if I specify 10 as percentage sample then the transformation returns 10% of the RANDOM records from the input data.
•
What is the use of Term Extraction transformation in SSIS?
Term Extraction transformation is used to extract nouns or noun phrases or both noun and noun phrases only from English text. It extracts terms from text in a transformation input column and then writes the terms to a transformation output column. It can be also used to find out the content of a dataset.
•
What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at runtime. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the runtime and how it looks like after the transformation occurred. The different types of data viewers are:
1. Grid
2. Histogram 3. Scatter Plot 4. Column Chart.
•
What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.
•
Which are the different types of Control Flow components in SSIS?
The different types of Control Flow components are: Data Flow Tasks, SQL Server Tasks, Data Preparation Tasks, Work flow Tasks, Scripting Tasks, Analysis Services Tasks, Maintenance Tasks, Containers.
•
What are containers? What are the different types of containers in SSIS?
Containers are objects that provide structures to packages and extra functionality to tasks. There are four types of containers in SSIS, they are: Foreach Loop Container, For Loop Container, Sequence Container and Task Host Container.
•
What are the different types of Data flow components in SSIS?
There are 3 data flow components in SSIS. 1. Sources 2. Transformations 3. Destinations
•
What are the different types of data sources available in SSIS?
There are 7 types of data sources provided by SSIS: a.) Data Reader source b.) Excel source c.) Flat file source d.) OLEDB source e.) Raw file source f.) XML source g.) Script component
•
What is SSIS Designer?
It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.
•
What is Control Flow tab?
It is the tab in SSIS designer where various Tasks can be arranged and configured. This is the tab where we provide and control the program flow of the project.
•
What is Data Flow tab?
This is the tab where we do all the work related to ETL job. It is the tab in SSIS Designer where we can extract data from sources, transform the data and then load them into destinations.
•
What is the function of control flow tab in SSIS?
On the control flow tab, the tasks including dataflow task, containers and precedence constraints that connect containers and tasks can be arranged and configured.
•
What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events. For example, we can configure Work Flow when ANY task Fails or Stops or Starts ..
•
What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.
•
What is Solution Explorer?
It is a place in SSIS Designer where all the projects, Data Sources, Data Source Views and other miscellaneous files can be viewed and accessed for modification.
•
How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.
•
How are variables useful in ssis package?
Variables can provide communication among objects in the package. Variables can provide communication between parent and child packages. Variables can also be used in expressions and scripts. This helps in providing dynamic values to tasks.
•
Explain Aggregate Transformation in SSIS?
It aggregates data, similar you do in applying TSQL functions like Group By, Min, Max, Avg, and Count. For example you get total quantity and Total line item for each product in Aggregate Transformation Editor. First you determine input columns, then output column name in Output Alias table in datagrid, and also operations for each Output Alias in Operation columns of the same datagrid. Some of operation functions listed below : • Group By • Average • Count • Count Distinct : count distinct and not null column value • Min, Max, Sum In Advanced tab, you can do some optimization here, such as setting up Key Scale option (low, medium, high), Count Distinct scale option (low, medium, high), Auto Extend factor and Warn On Division By Zero. If you check Warn On Division By Zero, the component will give warning instead of error. Key Scale option will optimize transformation cache to certain number of key threshold. If you set it low, optimization will target to 500,000 keys written to cache, medium can handle up to 5 million keys, and high can handle up to 25 million keys, or you can specify particular number of keys here. Default value is unspecified. Similar to number of keys for Count Distinct scale option. It is used to optimize number of distinct value written to memory, default value is unspecified. Auto Extend Factor is used when you want some portion of memory is used for this component. Default value is 25% of memory.
•
Explain Audit Transformation ?
It allows you to add auditing information as required in auditing world specified by HIPPA and Sarbanes-Oxley (SOX). Auditing options that you can add to transformed data through this transformation are : 1. Execution of Instance GUID : ID of execution instance of the package 2. PackageID : ID of the package 3. PackageName 4. VersionID : GUID version of the package 5. Execution StartTime 6. MachineName 7. UserName 8. TaskName 9. TaskID : uniqueidentifier type of the data flow task that contains audit transformation.
•
Explain Character Map Transformation?
It transforms some character. It gives options whether output result will override the existing column or add to new column. If you define it as new column, specify new column name. Operations available here are: 1. Uppercase 2. Lowercase 3. Byte reversal : such as from 0×1234 to 0×4321 4. Full width 5. Half width 6. Hiragana/katakana/traditional Chinese/simplified Chinese 7. Linguistic casing
•
Explain Conditional split Transformation ?
It functions as if…then…else construct. It enables send input data to a satisfied conditional branch. For example you want to split product quantity between less than 500 and greater or equal to 500. You can give the conditional a name that easily identifies its purpose. Else section will be covered in Default Output Column name. After you configure the component, it connect to subsequent transformation/destination, when connected, it pops up dialog box to let you choose which conditional options will apply to the destination transformation/destination.
•
Explain Copy column Transformation?
This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.
•
Explain Data conversion Transformation?
This component does conversion data type, similar to TSQL function CAST or CONVERT. If you wish to convery the data from one type to another then this is the best bet. But please make sure that you have COMPATABLE data in the column.
•
Explain Data Mining query Transformation?
This component does prediction on the data or fills gap on it. Some good scenarios uses this component is: 1. Take some input columns as number of children, domestic income, and marital income to predict whether someone owns a house or not. 2. Take prediction what a customer would buy based analysis buying pattern on their shopping cart. 3. Filling blank data or default values when customer doesn’t fill some items in the questionnaire.
•
Explain Derived column Transformation?
Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.
•
Explain Merge Transformation?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions : 1. Data should be in sorted order 2. Data type , data length and other meta data attribute must be similar before merged.
•
Explain Merge Join Transformation?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
•
Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
•
Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.
•
Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.
•
Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.
•
What r the possible locations to save SSIS package?
You can save a package wherever you want. SQL Server Package Store File System
•
What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.
•
What is a workflow in SSIS?
A workflow is a set of instructions on how to execute tasks. (It is a set of instructions on how to execute tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.
•
What is the diff between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process activities of data flow and other processes within a package. When we want to extract, transform and load data within a package. You add an SSIS dataflow task to the package control flow.
•
What are the main component of SSIS(project-architecture)?
SSIS architecture has 4 main components 1.ssis service 2.ssis runtime engine & runtime executable 3.ssis dataflow engine & dataflow components 4.ssis clients
•
Different components in SSIS package?
Control flow Data flow Event handler Package explorer
•
What are Connection Managers?
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.
•
What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable. Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
•
How to provide securIty to packages?
We can provide security in two ways 1. Package encryption 2. Password protection.
•
What are Precedence constraints?
Constraints that link executable, container, and tasks wIthin the package control flow and specify condItion that determine the sequence and condItions for determine whether executable run.
•
What is Design time Deployment in SSIS ?
When you
run a package from with in BIDS,it is built and temporarily deployed to the
folder. By default the package will be deployed to the BIN folder in the Package’s
Project folder and you can configure for custom folder for deployment. When
the Package’s execution is completed and stopped in BIDS,the deployed package
will be deleted and this is called as Design Time Deployment.
Q) SSIS
architecture consists of four key parts:
a) Integration
Services service: monitors running Integration Services packages and
manages the storage of
packages.
b) Integration
Services object model: includes managed API for accessing Integration
Services tools, commandline
utilities,
and custom applications.
c) Integration
Services runtime and run-time executables: it saves the layout of
packages, runs packages, and
provides
support for logging, breakpoints, configuration, connections, and
transactions. The Integration Services
run-time
executables are the package, containers, tasks, and event handlers that
Integration Services includes,
and
custom tasks.
d)
Data flow engine: provides the in-memory buffers that move data from
source to destination.
Q) How
would you do Logging in SSIS?
Logging
Configuration provides an inbuilt feature which can log the detail of various
events like onError,
onWarning
etc to the various options say a flat file, SqlServer table, XML or SQL
Profiler.
Q) How
would you do Error Handling?
A SSIS
package could mainly have two types of errors
a)
Procedure flow Error: Can be handled in Control flow through the precedence
control and redirecting the
execution
flow.
b) Data
Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error
Output of a component.
Q) How
to pass property value at Run time? How do you implement Package
Configuration?
A
property value like connection string for a Connection Manager can be passed
to the pkg using package
configurations.
Package Configuration provides different options like XML File, Environment
Variables, SQL Server
Table,
Registry Value or Parent package variable.
Q) how
would you deploy a SSIS Package on production?
1.
Create deployment utility by setting its property as true.
2. It
will be created in the bin folder of the solution as soon as package is
build.
3. Copy
all the files in the utility and use manifest file to deploy it on the Prod.
Q) What
is Execution Tree?
Execution
trees demonstrate how package uses buffers and threads. At run time, the data
flow engine breaks
down
Data Flow task operations into execution trees. These execution trees specify
how buffers and threads are
allocated
in the package. Each tree creates a new buffer and may execute on a different
thread. When a new
buffer
is created such as when a partially blocking or blocking transformation is
added to the pipeline, additional
memory
is required to handle the data transformation and each new tree may also give
you an additional worker
thread.
Q)
Difference between Union all and Merge Join?
a) Merge
transformation can accept only two inputs whereas Union all can take more
than two inputs
b) Data
has to be sorted before Merge Transformation whereas Union all doesn't have
any condition like that.
Q) How
would you restart package from previous failure point? What are Checkpoints
and how can we
implement
in SSIS?
When a
package is configured to use checkpoints, information about package execution
is written to a checkpoint
file.
When the failed package is rerun, the checkpoint file is used to restart the
package from the point of failure.
If the
package runs successfully, the checkpoint file is deleted, and then
re-created the next time that the package
is run.
Q) Where
are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90
stores the actual content and ssydtscategories, sysdtslog90,
sysdtspackagefolders90,
sysdtspackagelog,
sysdtssteplog, and sysdtstasklog do the supporting roles.
Q)
Difference between asynchronous and synchronous transformations?
Asynchronous
transformation have different Input and Output buffers and it is up to the
component designer in
an Async
component to provide a column structure to the output buffer and hook up the
data from the input.
Q) How
to achieve parallelism in SSIS?
Parallelism
is achieved using MaxConcurrentExecutable property of the package. Its
default is -1 and is
calculated
as number of processors + 2.
Questionnaire
1
1.
Difference between synchronous and asynchronous data flow transformations
2.
Understanding of when it’s best to use script vs. the built in components.
3.
Package configurations and how they work?
4. An
idea of the classes and methods used in SSIS.
5.
What's the difference between Control Flow and Data Flow?
6. What
is the Multicast transform used for?
7. What
transform would you use to concatenate two input fields into a single output
Questionnaire
2
1. What
is for-loop container? Give an example of where it can be used.
2. What
is foreach-loop container? Give an example of where it can be used.
3. What
is sequence container? Give an example of where it can be used.
4. What
is the difference between Analysis Services processing task & Analysis
services execute DDL task?
5. What
is the difference between for-loop container & foreach-loop container?
6. What
are the different parameters or configurations that “send mail task”
requires?
7.
Mention few mapping operations that the Character Map transformation
supports.
8.
Explain the functionality of Import Column Transformation and Export Column
Transformation
9.
Explain the functionality of Percentage Sampling transformation
10.
Explain the functionality of SCD transformation
11.
Explain the functionality of Union All transformation
12. What
does “Lookup” transformation used for?
13. What
are checkpoints? For which objects we define checkpoint? How to configure
checkpoint for a package?
14. What
is the use of “package configurations” available in SSIS?
15. What
are the different ways in which configuration details can be stored?
16. How
to deploy a package from development server to production server?
17. How
to create Integration Services Package Deployment Utility?
18. How
to deploy packages to file system?
19. How
to deploy packages to SQL server? Where in database packages will be stored?
20. How
to set security for a package? Explain the same as per different deployment
options.
21.
Explain the architecture of SSIS
22.
Explain the how SSIS engine workflow
Questionnaire
3
1.
Explain the architecture of SSIS and its components
2.
Difference between control flow and data flow
3.
How can assign a value to package variable at job level?
4.
What is pipeline?
5.
What are the three data flow components in SSIS?
6.
How do you dynamically set the server name in connection manager?
7.
How do you redirect error rows to error tables for logging purpose?
8.
What are the command line tools to execute SQL Server Integration Service
9.
Purpose of delay validation
10.
How do you duplicate data flow data for different destination?
|
SQL
SERVER REPORTING SERVICES [SSRS] INTERVIEW QUESTIONS
|
• What is Query parameter in SSRS?
Query parameters is mentioned in the query of the datasources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and can not begin with numeral. For clarity, we use only letters.
• What are the Reporting Service Components in SSRS?
Report Designer: A place where we can create report. Report Server: Provides services for implementation and delivery of reports. Report Manager: A Web-based administration tool for managing the Report Server.
• What is a matrix in SSRS?
A matrix is a data region linked to a report set. Matrix allows us to create crosstab reports with the report variables displaying on rows and columns. It allows us to drag and drop fields into it.
• What are sub reports and how to create them?
A sub report is like any other reports which can be called in main report and can be generate through main report. Parameters can be passed from main report to sub report and basis of that report can be generated.
• What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder. Report model project can be created on bids or report server. This model can have simple view. And using
• What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user. It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start creating reports.
• What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report. Report builder creates reports on database objects available with report model project.
• In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model project on report server you can get error or it might not get created. For this you need to check whether the service pack 22 is installed or not.
• How to deploy the Report?
Report can be deployed in three ways. 1. Using visual studio: In visual studio you can directly deploy the report through solution explorer by providing the report server URL in project properties at Target Server URL. This will deploy entire project or single report as per you selection. 2. Using report server: Can directly go to the report server and deploy the report by browsing the report from the disk location on server. 3. Creating the utility: SQL server provides the utility using that which can be used to create a customize utility for your report deployment in bulk.
• What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report server and can be customize accordingly.
• What is the name of reporting services config file and what’s
it’s used for?
Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config reside at ISS.
• What are the three different part of RDL file explain them?
In visual studio RDL files has three parts. 1. Data: It contains the dataset on which we write the query. Data set is connected with data source. 2. Design: In design you can design report. Can create tables and matrix reports. Drag columns values from source. 3. Preview: to check the preview after the report run.
• Which language rdl files made of?
RDL files are written in XML.
• What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and various other options. 3d charts are also available in reporting services.
• What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source. Data source is the source of data from where we are getting this data i.e. database server and database name connection string.
• What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below. 1. Microsoft SQL Server 2. OLEDB 3. Oracle 4. ODBC 5. SQL Server Analysis Service 6. Report Server Model 7. SAP Net weaver BI 8. Hyperion 9. Teradata 10. XML
• What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.
• How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will find the options for custom code.
• What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.
• What is report snapshot in SSRS?
Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten.
• What is bookmark link in SSRS?
Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images.
• What is Command parameter in SSRS?
A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information.
• What is Format parameter in SSRS?
Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter.
• What is Snapshot parameter in SSRS?
When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report.
• What are the rendering extensions of SSRS?
Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML.
• What is a ReportItem in SSRS?
A ReportItem is one of the subsequent types of items: Rectangle, Textbox, Image, Subreport, Line, CustomReportItem, List, Table, Matrix or Chart.
• What is Datasets in SSRS?
Dataset elements have information about the sets of data recovered as a part of the report.
• What are the main components of reporting services?
The main components of Reporting Services are
• What is Report Designer?
Report Designer is a collection of graphical query and design tools that are hosted within the Microsoft Visual Studio environment.
Report
Designer provides a Report Data pane to organize data used in your report,
and tabbed views for Design and Preview so that you can design a report
interactively.
Report Designer also provides query designers to help specify data to retrieve from data sources and the Expression dialog to specify report data to use in the report layout. When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server.
• What is Report manager?
Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to browse the report server folders or search for specific reports. You can view a report, its general properties, and past copies of the report that are captured in report history.
• What is Report Builder?
Web-based tool to create reports Come free with SQL Server 2005 Launch Report Builder 1.0 to create ad hoc reports that you can save and run on the report server. Users build desired reports themselves; can export to Word, Excel, PDF, and more
• What is Report Server?
The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations.
1.
How do we merge multiple cells?
Ans:
select multiple cells, right click and select merge cells option.
2.
How many ways we deploy reports?
Ans:
There are many ways 1. Build-->deploy 2.Report Manager-->Upload
3.Commandline facility(RS)
3. In
which database tables SSRS maintain the deployed items?
Ans: In
ReportServer database Catalog tables.
4.
Where does scheduled information stored?
Ans:
Schedule table in report server database.
5.Where
does report execution stored?
Ans:
ExecutionLog-->(Gives object level info) ExecutionLog2-->(Event Level
Info)
6.
How do we identify how many users connected to RS at this point?
Ans:
Going to the Session Data table in RSDB
7.
Encryption file extension in SSRS?
Ans:
.SNK extension
8.
How do we open other Report in the same window?
Ans:
=javaScript:void(window.open["reportpath"]);
9.
The Database locks at the time of report processing where does system stores?
Ans:
SessionLock table
10.
What is Pull Model?
Ans:
Automatically reports will be processes and delivered through file share or
email
11.
Where does cache information stored?
Ans: In
ReportServerTempdb execution cache table.
12.What
makes DataSource pressure less at report processing level?
Ans: By
using cache / snapshot table.
13. Can we maintain history of
Snapshots? and where does Snapshots and history store?
Ans:
Yes, In ReportServer Database Snapshot and History tables.
14.
How do we display all locations selection option to the user?
Ans:
Multivalued parameters
15.
How do we display always starting location as HYD for the report?
Ans: By
setting default options value at the time of parameter creation
16.
How do we provide user enter values option to get the required report?
Ans: By
setting Specify values section as NULL
17.
How do we take check boxes at report level?
Ans:
TextBox-Font->windings and write below expression
=iif(Fields!GenderCode="M",chr(0254),"0")
18.How
to get number of rows in Dataset
=countrows("Datasetname")
19.Display
a popup on click of an item?
="Javascript:void(alert("MSBI
Class"))"
20.How
do we open link in a new window
=Code.Newwindows("www.google.com","no","no")
or
="javascript.void(window.open("www.google.com")"
21.How
do we print negative numbers?
Two step
process a)Format string #,##0.00
b) In
the color property =
iif(Fields!partyincome<0,"red","black")
22.
How do we take new line in textboxes?
Two ways
a)VBCRLF b) By using Chrw(13) and Chrw(10)
Note: At
the beginning and ending if you need new line VBCRLF is an issue
23.
How do we display no rows message when dataset not showing any data in the
region.
Ans:
Highlight the region->rc->No Rows Message->”No rows found”
|
SQL
SERVER ANALYSIS SERVICES [SSAS] INTERVIEW QUESTIONS
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
You can answer more but if you end this with these then the
interviewer feel that you are REAL EXPERIENCED.
The
datawarehouse is an informational environment that
OR
a warehouse is a
OR
Collection
of data in support of management’s decision making process”. He defined the
terms in the sentence as follows.
OR
Subject
oriented:
It
define the specific business domain ex: banking, retail, insurance, etc…..
Integrated:
It
should be in a position to integrated data from various source systems
Ex:
sql,oracle,db2 etc……
Time
variant:
It
should be in a position to maintain the data the various time periods.
Non
volatile:
Once
data is inserted it can’t be changed
A
data mart is a subset of an organizational data store, usually oriented to a
specific purpose or major data subject that may be distributed to support
business needs. Data marts are analytical data stores designed to focus on
specific business functions for a specific community within an organization.
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data
warehouse design methodology the data warehouse is created from the union of
organizational data marts.
They
are 3 types of data mart they are
Datawarehouse
is complete data where as Data mart is Subset of the same.
Ex:
All
the organisation data may related to finance department, HR, banking dept are
stored in data warehouse where as in data mart only finance data or HR
department data will be stored. So data warehouse is a collection of
different data marts.
We
need to identify the bottlenecks to tune the performance, to
overcome the bottleneck we need to following the following.
This
question is either to test whether you are really experienced or when he
doesnot have any questions to ask ..
You
can tell any area where you feel difficult to work. But always the best
answers will be the following.
Steps
to create a cube in ssas
The
data source is the Physical Connection information that analysis service uses
to connect to the database that host the data. The data source contains the
connection string which specifies the server and the database hosting the
data as well as any necessary authentication credentials.
A
data source view is a persistent set of tables from a data source that supply
the data for a particular cube. BIDS also includes a wizard for creating data
source views, which you can invoke by right-clicking on the Data Source Views
folder in Solution Explorer.
A
named calculation is a SQL expression represented as a calculated column.
This expression appears and behaves as a column in the table. A named
calculation lets you extend the relational schema of existing tables or views
in a data source view without modifying the tables or views in the underlying
data source.
Named
calculation is used to create a new column in the DSV using hard coded values
or by using existing columns or even with both.
Named
query in DSV is similar to View in Database. This is used to create Virtual
table in DSV which will not impact the underlying database. Named query is
mainly used to merge the two or more table in the datasource view or to
filter columns of a table.
A
named query is used to join multiple tables, to remove unnecessary columns
from a table of a database. You can achieve the same in database
using Views but this Named Queries will be the best bet whe you don’t have
access to create Views in database.
By
using named calculations we can add a new column to an existing table in the
data source view. Named Calculation is explained above.
A
dimension table contains hierarchical data by which you’d like to summarize.
A dimension table contains specific business information, a dimension table
that contains the specific name of each member of the dimension. The name of
the dimension member is called an “attribute”
The
key attribute in the dimension must contain a unique value for each member of
the dimension. This key attribute is called “primary key column”
The
primary key column of each dimension table corresponding to the one of the
key column in any related fact table.
A
fact table contains the basic information that you wish to
summarize. The table that stores the detailed value for measure is
called fact table. In simple and best we can define as “The table which
contains METRICS” that are used to analyse the business.
It
consists of 2 sections
1)
Foregine key to the dimesion
2)
measures/facts(a numerical value that used to monitor business activity)
This
is very important interview question. The “Factless Fact Table” is a table
which is similar to Fact Table except for having any measure; I mean that
this table just has the links to the dimensions. These tables enable you
to track events; indeed they are for recording events.
Factless
fact tables are used for tracking a process or collecting stats. They are
called so because, the fact table does not have aggregatable numeric values
or information. They are mere key values with reference to the dimensions
from which the stats can be collected
Attribute
relationships are the way of telling the analysis service engine that how the
attributes are related with each other. It will help to relate two or
more attributes to each other.Processing time will be decreased if
proper relationships are given. This increases the Cube Processing
performance and MDX query performance too.
In
Microsoft SQL Server Analysis Services, attributes within a
dimension are always related either directly or indirectly to the key
attribute. When you define a dimension based on a star schema, which is where
all dimension attributes are derived from the same relational table, an
attribute relationship is automatically defined between the key attribute and
each non-key attribute of the dimension. When you define a dimension based on
a snowflake schema, which is where dimension attributes are derived from
multiple related tables, an attribute relationship is automatically defined
as follows:
They
are 2 types of attribute relationships they are
Rigid: In Rigid relationships where the relationship between the
attributes is fixed, attributes will not change levels or their respective
attribute relationships.
Example: The
time dimension. We know that month “January 2009″ will ONLY belong to Year
“2009″ and it wont be moved to any other year.
Flexible : In Flexible relationship between the attributes is changed.
Example: An
employee and department. An employee can be in accounts department today but
it is possible that the employee will be in Marketing department tomorrow.
They
are 3 types of dimensions:
Confirm
dimension: It is the dimension which is sharable across the
multiple facts or data model. This is also called as Role Playing Dimensions.
junk
dimension: A number of very small dimensions might be lumped (a small
irregularly shaped) together to form a single dimension, a junk dimension –
the attributes are not closely related. Grouping of Random flags and text
Attributes in a dimension and moving them to a separate sub dimension is
known as junk dimension.
Degenerated
dimension: In this degenerate dimension contains their values in fact
table and the dimension id not available
in dimension table. Degenerated Dimension is a dimension key
without corresponding dimension.
Example:
In the PointOfSale Transaction Fact table, we have:
Date
Key (FK), Product Key (FK), Store Key (FK), Promotion Key (FP), and
POS Transaction Number
Date
Dimension corresponds to Date Key, Production Dimension corresponds to
Production Key. In a traditional parent-child database, POS Transactional
Number would be the key to the transaction header record that contains
all the info valid for the transaction as a whole, such as the transaction
date and store identifier. But in this dimensional model, we
have already extracted this info into other dimension. Therefore, POS
Transaction Number looks like a dimension key in the fact table but does
not have the corresponding dimension table.
They
are 3 types of database schema they are
Star schema: In star schema fact table will be directly linked with all
dimension tables. The star schema’s dimensions are denormalized with each
dimension being represented by a single table. In a star schema a
central fact table connects a number of individual dimension tables.
Snowflake: The snowflake schema is an extension of the star schema, where each point of the star
explodes into more points. In a star schema, each dimension is represented by
a single dimensional table, whereas in a snowflake schema, that dimensional
table is normalized into multiple lookup tables, each representing a level in
the dimensional hierarchy. In snow flake schema fact table will be
linked directly as well as there will be some intermediate dimension tables
between fact and dimension tables.
Star flake: A
hybrid structure that contains a mixture of star(denormalized) and
snowflake(normalized) schema’s.
We
can hide the attribute by selecting “AttributeHierarchyVisible = False” in
properties of the attribute.
By
selecting “ AttributeHierarchyEnabled = False”, we can make an
attribute not in process.
In
Analysis Service we generally see all dimension has All member. This is
because of IsAggregatable property of the attribute. You can set its value to
false, so that it will not show All member. Its default member for that
attribute. If you hide this member than you will have to set other attribute
value to default member else it will pick some value as default and this will
create confusion in browsing data if someone is not known to change in
default member.
Key column of any attribute: Contains the column or columns
that represent the key for the attribute, which is the column in the
underlying relational table in the data source view to which the attribute is
bound. The value of this column for each member is displayed to users unless
a value is specified for the NameColumn property.
Name column of an attribute: Identifies the column that
provides the name of the attribute that is displayed to users, instead of the
value in the key column for the attribute. This column is used when the key
column value for an attribute member is cryptic or not otherwise useful to
the user, or when the key column is based on a composite key. The NameColumn
property is not used in parent-child hierarchies; instead, the NameColumn
property for child members is used as the member names in a parent-child
hierarchy.
Value columns of an attribute: Identifies the column that
provides the value of the attribute. If the NameColumn element of the
attribute is specified, the same DataItem values are used as default values
for the ValueColumn element. If the NameColumn element of the attribute is
not specified and the KeyColumns collection of the attribute contains a
single KeyColumn element representing a key column with a string data type,
the same DataItem values are used as default values for the ValueColumn
element.
A
hierarchy is a very important part of any OLAP engine and allows users to
drill down from summary levels hierarchies represent the way user
expect to explore data at more detailed level
hierarchies
is made up of multipule levels creating the structure based on end
user requirements.
->years->quarter->month->week
,are all the levels of calender hierarchy
They
are 2 types of hierarchies they are
Natural hierarchy: This means that the attributes are intuitively related to one
another. There is a clear relationship from the top of the hierarchy to the
bottom.
Example: An
example of this would be date: year, quarter and month follow from each
other, and in part, define each other.
Unnatural hierarchy: This means that the attributes are not clearly related.
Example:
An example of this might be geography; we may have country -> state ->
city, but it is not clear where Province might sit.
An
attribute hierarchy is created for every attribute in a dimension, and each
hierarchy is available for dimensioning fact data. This hierarchy consists of
an “All” level and a detail level containing all members of the hierarchy.
you
can organize attributes into user-defined hierarchies to provide navigation
paths in a cube. Under certain circumstances, you may want to disable or hide
some attributes and their hierarchies.
AttributeHierarchyDisplayFolder: Identifies the folder in
which to display the associated attribute hierarchy to end users. For example
if I set the property value as “Test” to all the Attributes of a dimension
then a folder with the name “Test” will be created and all the Attributes
will be placed into the same.
AttributeHierarchyEnabled: Determines whether an attribute
hierarchy is generated by Analysis Services for the attribute. If the
attribute hierarchy is not enabled, the attribute cannot be used in a
user-defined hierarchy and the attribute hierarchy cannot be referenced in
Multidimensional Expressions (MDX) statements.
AttributeHierarchyOptimizedState: Determines the level of
optimization applied to the attribute hierarchy. By default, an attribute
hierarchy is FullyOptimized, which means that Analysis Services builds
indexes for the attribute hierarchy to improve query performance. The other
option, NotOptimized, means that no indexes are built for the attribute
hierarchy. Using NotOptimized is useful if the attribute hierarchy is
used for purposes other than querying, because no additional indexes are
built for the attribute. Other uses for an attribute hierarchy can be
helping to order another attribute.
AttributeHierarchyOrdered: Determines whether the
associated attribute hierarchy is ordered. The default value is True.
However, if an attribute hierarchy will not be used for querying, you can
save processing time by changing the value of this property to False.
AttributeHierarchyVisible : Determines whether the attribute
hierarchy is visible to client applications. The default value is True.
However, if an attribute hierarchy will not be used for querying, you can
save processing time by changing the value of this property to False.
There
are three standard storage modes in OLAP applications
Summary
and comparison
MOLAP
(Multi dimensional Online Analytical Processing) : MOLAP is the most used
storage type. Its designed to offer maximum query performance to the users.
the data and aggregations are stored in a multidimensional format, compressed
and optimized for performance. This is both good and bad. When a
cube with MOLAP storage is processed, the data is pulled from the
relational database, the aggregations are performed, and the data is stored
in the AS database. The data inside the cube will refresh only when the cube
is processed, so latency is high.
Advantages:
ROLAP
(Relational Online Analytical Processing) : ROLAP does not have the high
latency disadvantage of MOLAP. With ROLAP, the data and aggregations are
stored in relational format. This means that there will be zero latency
between the relational source database and the cube.
Disadvantage
of this mode is the performance, this type gives the poorest query
performance because no objects benefit from multi dimensional storage.
Advantages:
Hybrid
Online Analytical Processing (HOLAP): HOLAP is a combination of MOLAP and
ROLAP. HOLAP stores the detail data in the relational database but stores the
aggregations in multidimensional format. Because of this, the aggregations
will need to be processed when changes are occur. With HOLAP you kind of
have medium query performance: not as slow as ROLAP, but not as fast as
MOLAP. If, however, you were only querying aggregated data or using a cached
query, query performance would be similar to MOLAP. But when you need to get
that detail data, performance is closer to ROLAP.
Advantages:
Translation: The translation feature in analysis service allows you to
display caption and attributes names that correspond to a specific language.
It helps in providing GLOBALIZATION to the Cube.
All
the dimensions that are created using NEW DIMENSION Wizard are database
dimensions. In other words, the dimensions which are at Database level are
called Database Dimensions.
A
cube dimension is an instance of a database dimension within a cube is called
as cube dimension. A database dimension can be used in multiple cubes, and
multiple cube dimensions can be based on a single database dimension
To
add a dimension to a cube follow these steps.
Slowly
changing dimensions (SCD) determine how the historical changes in the
dimension tables are handled. Implementing the SCD mechanism enables users to
know to which category an item belonged to in any given date.
It
is a concept of STORING Historical Changes and when ever an IT guy finds a
new way to store then a new Type will come into picture. Basically there are
3 types of SCD they are given below
Type 1: In Type 1 Slowly Changing Dimension, the new information simply
overwrites the original information. In other words, no history is kept.
In
our example, recall we originally have the following table:
After
Christina moved from Illinois to California, the new information replaces the
new record, and we have the following table:
Advantages: This
is the easiest way to handle the Slowly Changing Dimension problem, since
there is no need to keep track of the old information.
Disadvantages:
All history is lost. By applying this methodology, it is not possible to
trace back in history.
Usage:
About 50% of the time.
When
to use Type 1: Type 1 slowly changing dimension should be used when it
is not necessary for the data warehouse to keep track of historical changes.
Type 2: In Type 2 Slowly Changing Dimension, a new record is added to
the table to represent the new information. Therefore, both the original and
the new record will be present. The new record gets its own primary key.
In
our example, recall we originally have the following table:
After
Christina moved from Illinois to California, we add the new information as a
new row into the table:
Advantages: This
allows us to accurately keep all historical information.
Disadvantages:
Usage:
About 50% of the time.
Type3 : In Type 3 Slowly Changing Dimension, there will be two columns
to indicate the particular attribute of interest, one indicating the original
value, and one indicating the current value. There will also be a column that
indicates when the current value becomes active.
In
our example, recall we originally have the following table:
To
accommodate Type 3 Slowly Changing Dimension, we will now have the following
columns:
Customer
Key,Name,OriginalState,CurrentState,Effective Date
After
Christina moved from Illinois to California, the original information gets
updated, and we have the following table (assuming the effective date of
change is January 15, 2003):
Advantages:
Disadvantages: Type
3 will not be able to keep all history where an attribute is changed more
than once. For example, if Christina later moves to Texas on December 15,
2003, the California information will be lost.
Usage:
Type 3 is rarely used in actual practice.
Role play dimensions: We already discussed about this. This is nothing but CONFIRMED
Dimensions. A dimension can play different role in a fact table you
can recognize a roleplay dimension when there are multiple columns
in a fact table that each have foreign keys to the same dimension table.
Ex1:
There are three dimension keys in the factinternalsales,factresellersales
tables which all refer to the dimtime table,the same time dimension is used
to track sales by that contain either of these fact table,the
corresponding role-playing dimension are automatically added to the
cube.
Ex2
: In retail banking, for checking account cube we could have transaction
date dimension and effective date dimension. Both dimensions have date,
month, quarter and year attributes. The formats of attributes are the same on
both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format.
Both dimensions have members from 1993 to 2010.
Measure groups : These measure groups can contain different dimensions and be at
different granularity but so long as you model your cube
correctly, your users will be able to use measures from each of these measure
groups in their queries easily and without worrying about the underlying
complexity.
Creating
multiple measure groups : To create a new measure group in the Cube Editor,
go to the Cube Structure tab and right-click on the cube name in the Measures
pane and select ‘New Measure Group’. You’ll then need to select the fact
table to create the measure group from and then the new measure group will be
created; any columns that aren’t used as foreign key columns in the DSV will
automatically be created as measures, and you’ll also get an extra measure of
aggregation type Count. It’s a good idea to delete any measures you are not
going to use at this stage.
Measures : Measures are the numeric values that our users want to
aggregate, slice, dice and otherwise analyze, and as a result, it’s important
to make sure they behave the way we want them to. One of the fundamental
reasons for using Analysis Services is that, unlike a relational database it
allows us to build into our cube design business rules about measures: how
they should be formatted, how they should aggregate up, how they interact with
specific dimensions and so on.
An
attribute is a specification that defines a property of an object, element,
or file. It may also refer to or set the specific value for a given instance
of such.
A
surrogate key is the SQL generated key which acts like an alternate primary
key for the table in database, Data warehouses commonly use a surrogate key
to uniquely identify an entity. A surrogate is not generated by the user but
by the system. A primary difference between a primary key and surrogate key
in few databases is that primarykey uniquely identifies a record while a
Surrogatekey uniquely identifies an entity.
Ex: An
employee may be recruited before the year 2000 while another employee with
the same name may be recruited after the year 2000. Here, the primary key
will uniquely identify the record while the surrogate key will be generated
by the system (say a serial number) since the SK is NOT derived from the
data.
They
are six relation between the dimension and measure group, they are
No relationship: The dimension and measure group are not related.
Regular: The
dimension table is joined directly to the fact table.
Referenced: The
dimension table is joined to an intermediate table, which in turn,is joined
to the fact table.
Many to many:The dimension table is to an intermediate fact table,the
intermediate fact table is joined , in turn, to an intermediate dimension
table to which the fact table is joined.
Data mining:The target dimension is based on a mining model built from the
source dimension. The source dimension must also be included in the cube.
Fact table: The dimension table is the fact table.
Calculations
are item in the cube that are eveluated at runtime
Calculated
members: You can create customized measures or dimension members, called
calculated members, by combining cube data, arithmetic operators, numbers,
and/or functions.
Example:
You can create a calculated member called Marks that converts dollars to
marks by multiplying an existing dollar measure by a conversion rate. Marks
can then be displayed to end users in a separate row or
column. Calculated member definitions are stored, but their values exist
only in memory. In the preceding example, values in marks are displayed to
end users but are not stored as cube data.
In
Analysis Services, a KPI is a collection of calculations that are associated
with a measure group in a cube that are used to evaluate business
success. We use KPI to see the business at the particular point, this is
represents with some graphical items such as traffic signals,ganze etc
Actions
are powerful way of extending the value of SSAS cubes for the end
user. They can click on a cube or portion of a
cube to start an application with the selected item as a parameter,
or to retrieve information about the selected item.
One
of the objects supported by a SQL Server Analysis Services cube is the
action. An action is an event that a user can initiate when accessing cube
data. The event can take a number of forms. For example, a user might be able
to view a Reporting Services report, open a Web page, or drill through to
detailed information related to the cube data
Analysis
Services supports three types of actions..
Report action: Report action Returns a Reporting Services report that is
associated with the cube data on which the action is based.
Drill through: Drillthrough Returns a result set that provides detailed
information related to the cube data on which the action is based.
Standard: Standard has five action subtypes that are based on the
specified cube data.
Dataset: Returns
a mutlidimensional dataset.
Proprietary: Returns a string that can be interpreted by a client
application.
Rowset: Returns
a tabular rowset.
Statement: Returns
a command string that can be run by a client application.
URL: Returns
a URL that can be opened by a client application, usually a browser.
You
can use the Partition Wizard to define partitions for a measure group in a
cube. By default, a single partition is defined for each measure group in a
cube. Access and processing performance, however, can degrade for large partitions.
By creating multiple partitions, each containing a portion of the data for a
measure group, you can improve the access and processing performance for that
measure group.
In
2005 a MAX of 2000 partitions can be created per measure group and that limit
is lifted in later versions.
In
any version the MINIMUM is ONE Partition per measure group.
Aggregations
provide performance improvements by allowing Microsoft SQL Server Analysis
Services (SSAS) to retrieve pre-calculated totals directly from cube storage
instead of having to recalculate data from an underlying data source for each
query. To design these aggregations, you can use the Aggregation Design
Wizard. This wizard guides you through the following steps:
Perspectives
are a way to reduce the complexity of cubes by hidden elements like measure
groups, measures, dimensions, hierarchies etc. It’s nothing but slicing
of a cube, for ex we are having retail and hospital data and end user is
subscribed to see only hospital data, then we can create perspective
according to it.
Bulid: Verifies
the project files and create several local files.
Deploy: Deploy the structure of the cube(Skeleton) to the server.
Process: Read
the data from the source and build the dimesions and cube structures
Elaborating the
same is given below.
Build: Its is a used to process the data of the cube
database. Build is a version of a program. As a rule, a build is a
pre-release version and as such is identified by a build number, rather than
by a release number. Reiterative (repeated) builds are an important part of
the development process. Throughout development, application components are
collected and repeatedly compiled for testing purposes, to
ensure a reliable final product. Build tools, such as make or Ant, enable developers to automate
some programming tasks. As a verb, to build can mean either to write code or to put individual coded
components of a program together.
Deployment: During development of an Analysis Services project in Business
Intelligence Development Studio, you frequently deploy the project to a
development server in order to create the Analysis Services database defined
by the project. This is required to test the project.
for
example, to browse cells in the cube, browse dimension members, or verify key
performance indicators (KPIs) formulas.
The maximum size of the dimension is 4 gb.
They
are 6 types of processing in ssas ,they are
Process Full: Processes an Analysis Services object and all the objects that
it contains. When Process Full is executed against an object that has already
been processed, Analysis Services drops all data in the object, and then
processes the object. This kind of processing is required when a structural
change has been made to an object, for example, when an attribute hierarchy
is added, deleted, or renamed. This processing option is supported for cubes,
databases, dimensions, measure groups, mining models, mining structures, and
partitions.
Process Data: Processes data only without building aggregations or indexes. If
there is data is in the partitions, it will be dropped before re-populating
the partition with source data. This processing option is supported for
dimensions, cubes, measure groups, and partitions.
Process Index: Creates or rebuilds indexes and aggregations for all processed
partitions. This option causes an error on unprocessed objects. This
processing option is supported for cubes, dimensions, measure groups, and
partitions.
Process Increment: Adds newly available fact data and process only to the relevant
partitions. This processing option is supported for measure groups, and
partitions.
Process Structure: If the cube is unprocessed, Analysis Services will process, if
it is necessary, all the cube’s dimensions. After that, Analysis Services
will create only cube definitions. If this option is applied to a mining
structure, it populates the mining structure with source data. The difference
between this option and the Process Full option is that this option does not
iterate the processing down to the mining models themselves. This processing
option is supported for cubes and mining structures.
Unprocess : Drops the data in the object specified and any lower-level
constituent objects. After the data is dropped, it is not reloaded. This
processing option is supported for cubes, databases, dimensions, measure groups,
mining models, mining structures, and partitions.
Process Default: Detects the process state of an object, and performs processing
necessary to deliver unprocessed or partially processed objects to a fully
processed state. This processing option is supported for cubes, databases,
dimensions, measure groups, mining models, mining structures, and partitions.
The basic unit of storage and analysis in Analysis Services is
the cube. A cube is a collection of data that’s been aggregated
to allow queries to return data quickly.
For
example, a cube of order data might be aggregated by time period and by
title, making the cube fast when you ask questions concerning orders by week
or orders by title.
The
full form of AMO is Analysis Managament Objects. This is used to create or
alter cubes from .NET code.
Just
open the datasourceview and on right click we find the option REFRESH. Click
the REFRESH then it will add new attributes to the table which can be added
to Cube.
REAL TIME INTERVIEW QUESTIONS
-
Answer
to this question varies from project to project and mainly depends on how BIG
is your database and how COMPLEX the database design is. Generally for the
database with a TRANSACTION TABLE of 50 crore records, the cube size will be
around 100GB. So, better go with 100GB as answer to this question.
You can expect this question immediately after you answer 100GB
to the last question. The database size will be 600 to 800GB for which the
cube will come to 100 GB. So go with 800GB for this question.
This will be the next question if you answer 800GB as your
dataabase size. Here he is not expecting SIZE in GBs but the interviewer will
be expecting NUMBER OF ROWS in the Transaction table. Go with 57Crore records
for this question.
You have to be very careful here. Frequency of processing cube
depends on HOW FREQUENTLY YOU ARE GETTING NEW DATA. Once the new data comes
then SSIS team loads it and send a mail to SSAS team after load is completed
successfully. Once SSAS team receives the mail then these guys will look for
best time to PROCESS.
Typically we get data either Weekly or Monthly. So you can say
that the processing of the cube will be done either Weekly or monthly.
This answer should be based on your last answer. IF you answered
WEEKLY to last question then the Answer to this question also should be
WEEKLY. IF MONTHLY for last question then this answer also should be MONTHLY.
This is the toughest question to answer. This depends on DATA
you have and CLIENTS requirements. Let me explain here.
By defining roles we provide security to cubes. Using roles we
can restrict users from accessing restricted data. Procedure as follows
-
There are many ways to do the same. Let me explain four here and
cleverly you can say “I worked on 4 SSAS projects till date and implemented
different types in all the four.”
There are couple of this where we face difficulty.
Partitions can be created on different data. Few people do it on
PRODUCT NAME wise and many prefer to do it on DATE data wise. you go
with DATE wise.
In dates, we can create MONTH wise,WEEK wise,QUARTER wise and
some times YEAR wise. This all depends on how much data you are coming per
WEEK or MONTH or QUARTER … If you are getting 50 lakhs records per month then
tell you do MONTH wise.
47 to 50.
Total 10 and in that 4 are Fact tables and remaining 6 are Fact
less fact tables.
Snowflake
My
data base design doesn’t support STAR Schema.
Don’t add much to this as the questions in this will be tricky.
Just tell that you worked on couple of KPI and you have basic knowledge on
this. (Don’t worry, this is not MANDATORY)
We defined the aggregations for MOST FREQUENTLY USED data in
SSRS reports.
Just
2 guys as we guys are really in demand and lot of scarcity:)
Only 2 and one in morning shift and another in Evening shift.
This is Very very important question. This again depends on the
SIZE of database,Complexity of the database and your server settings. For
database with 50 cr transaction records, it generally takes 3.5 hrs.
I answer more than 5000 and if you tell the same then you are
caught unless you are super good in MDX. Best answer for you is “Worked on 50
calculations”
|
INDEX SEEK VS INDEX SCAN,
INDEX: are created on existing tables to locate rows more qiucly and efficently.
Seek Actions occours to retrieve records from the table. It means you were able to locate the records with out
having to examine every row to locate those records.
Scan occurs when there is no index available or when a poorely created index exisits on a t able.
In a table scan, sql examine every row in a table to satify the query result.
What is a covering index , filter Index ?
=============================================================================================================
UNION , MINUS , INTERSECT
UNION: Record sets are combined from multipe sources.
In Union query only distinct records are selected. No duplicates.
Union checks record by record to filter duplicates , so it will take longer time than Union all.
Union all reurns all records including duplicates.
UNION AND UNION ALL operator requires that query should have same no of column and data types should identical.
datatypes sgould compatabile.
MINUS/Except : All the rows in the first query which are not found in the second query.
INTERSECT: Used to retrieve the common records from left and right query of the Intersect operator. (Same as
Inner join)
when using the INTERSECT operator No of column and order must be same and datatype must be compattiable.
===============================================================================================================
What is FILL FACTOR: SETTING APPLICATEBLE TO INDEX IN SQL SERVER .
HOW MUNCH DATA IN RETAINED ON INDIEX PAGE WHEN IT IS CREADTED ?
========================================================================================================
TYPES OF BACK UP: DIFFERNETIAL DB , FULL DB , TRAN LOG BACK ?
FULL BACK UP: it contains all the data in DB ( set of file groups & Files and also enough log to allow for
recovering the
data).It is the base of both differential back up and transactional log back up.
Differential Back up:
A differential back up is not independent and it must be based on the lasted full backup of the data. that
means there
should have a full back up of the data. A differential back up contains only the data that has changed since
the differential base. typically Differentail back up are smaller and faster to create than the base of full
backup and also require less disk.
Differential back up can save available space and speed up the process of making frequent back up to decrease
the risk
of data loss. At restore time, the full back up is restored first, followed by the most recent differential
back up.
========================================================================================================
CAN WE INSERT THE DATA IF THE CLUSTED INDEX IS DISABLED.. Ans: NO
What is Clustered Index and Non-Clustered Index?
CLUSTERED INDEX: Clusted index determine physical organisation of the data.
2005 : 1 CLUSTERED INDEX + 249 NON CLUSTERED INDEX
2008 : 1 CLUSTERED INDEX + 999 NON CLUSTERED INDEX
When you create a Primary Key on a table, a clustered index is created by default.
NON CLUSTERED INDEX are usefull when users require multiple ways to search for Data.
===============================================================================================================
====
What is meant by Trigger ?
Tigger is a sql procedure that intiates an action when a event (like Insert, update, Delete ) occours on any
object.
DML TRIGGER
INSTEAD OF TRIGGER: are fired in the place of triggering actions such as insert, update and delete.
AFTER TIGGER: are fired following the trigger actions such as insert
, update and delete.
DDL TRIGGER
are fired on DDL Statments like Create , drop , Alter . they are always after tiggers.
LOG ON EVENT: this type of trigger is fired aganist the LOGON event before a user session is establised
===============================================================================================================
======
Diff between getdate() and sysdatetime()?
getdate()- Precession is in milli seconds
sysdatetime()- Precessin is in Nana seconds.
===============================================================================================================
======
What is the use of TOP Command ?
Top is a row limiter helps you limit the no of records affected by DML statmenet.
Accepts Variables and literal Values. it can be used with INSERT, UPDATE, DELETE statment.
===============================================================================================================
=======
SERVICE BROKER:
serive broker is a message queuing technology in sql server that allows developers to integrate sql server
fully into
distribuded applications. It allows databases to send a message without waiting for the response.
so that application will continue to function if the remote DB is temporarily Unavailable.
===============================================================================================================
=======
FILE STREAM:
File stream allow you to store unstructured large object like text, image, videos.
File stream integrate the sql server database engine with NTFS . it stores data in varbinary(max) datatype.
===============================================================================================================
=======
CONSTRAINT:
constraint performs data validations & Maintain the DB integrity by preventing Invaild data being entered.
TYPES
CHECK CONSTRAINT: It restricts the value that user enters into column during INSERT & UPDATE.
===============================================================================================================
=======
STORED PROCEDURE:
SP is a object Stored in DB & contains one or more lines of SQL Code,these stms run in a single execution.
SP is a pre-compiled code that can be reused.
SP can act like Views & selects the Data, they can also make updates and create objects
or Set up a DB back & perform various task.
It reduces networK traffic and improves performance.
Also, prvide Integrity of the Logics.
FUNTIONS:
Function is a object Stored in DB & Contains SQL Code that accepts Parameters.
USERDEFINED FUNCTION:
It is a user defined object Stored in DB & Contains SQL Code that accepts Parameters, Perform task & return
result.
===============================================================================================================
=======
DIFF b/W Function & SP.
Functions and Sp are similar the way they work, but you must call a Function using a SELECT stmt or a WHERE
Clause
with in a SELECT Stmt where as SP cannot be used
Functions can be used any where in where/having/select clause where SP cannot be used.
===============================================================================================================
======
DIFF b/W Triggers & SP.
SP are not event driven and not attached to a specific table as most triggers are .
SP are executed by invoking a call to the procedure . Triggers are executed by events.
Triggers can also execute SP.
===============================================================================================================
======
What is SSIS CONFUGURATION: TYPES OF CONFIGURATION ?
============================================================================================================
STUFF FUNCTION in SQL: ?
===============================================================================================================
======
What is linked servers?
Linked server configuration enables Sql server to execute the queries in remote servers.
System supplied stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to add
new linked servers.
===============================================================================================================
======
What is a Cursor?
A cursor is a Database object used by applications in the procedural logic to manipulate data in a
Row-by row basis, instead of typing sql commands that operate on all.
In order to work with cursors, we need to perform these steps in the following order
1) Declare cursor
2) Open a cursor
3) Fetch a row from the cursor
4) Process the Fetched row
5) Close Cursor
6) Deal locate the cursor.
===============================================================================================================
======
What is the difference between the DELETE and TRUNCATE Commands?
The delete command removes the rows from a table on the basis of the condition that we provide in the where
clause.
Truncate will remove all the rows from the table and there will not be any data in the table after
running truncate command.
TRUNCATE:
Truncate is faster and uses few system and transaction log resources than delete.
Truncate removes data by deallocating the data pages used to store table data and only page deallocations are
recorded in the transaction log.
Truncate removes all the rows from the table but the table structure, its columns, Constraints and indices
remain.
We cannot use truncate table on a table referenced by a foreign key constraint.
Truncate cannot be rolled back unless it is used in a Transaction.
Truncate is a DDL Command
Truncate Resets the identity filed of the table
DELETE:
Delete removes only one record at a time.
Delete can be rolled back
Delete is DML Command
Delete does not reset the identity of the table.
Delete can be used with or without where clause.
===============================================================================================================
======
to find nth RAnk
select top 1 StateProvinceID from
(select distinct top 8 StateProvinceID From Sales.SalesTaxRate
order by StateProvinceID desc) a
order by StateProvinceID
===============================================================================================================
======
What are ACID Properties?
===============================================================================================================
======
Normalisation uses?
===============================================================================================================
======
What are Isolation levls ?
===============================================================================================================
======
Matrix ,tabular, List - Difference ?
===============================================================================================================
======
how error can be handled in SQL/SSIS ?
===============================================================================================================
======
What are Checkpoints ?
Checkpoints cannot be applied on Data flow Level.
===============================================================================================================
======
What are Control Flow tasks?
===============================================================================================================
======
Difference b/w table variable and Temporary variable ?
===============================================================================================================
======
What is the USE of CTE
===============================================================================================================
======
diff b/w CTE And Temporary table?
===============================================================================================================
======
Different types of constraints in sql ?
===============================================================================================================
======
different types of transformation you have used-Expain them with examples ?
===============================================================================================================
======
Q) differences b/w 2008 R2 and 2012
===============================================================================================================
======
Q) How many evironments in your Project
Ans) 2 or 3
Production (DC,DR) & UAT (testing )
DC: there are 3 DB servers and 3 application servers
DR: there are 2 DB servers and 2 application servers
UAT: there is only one DB & Application server
At any point of time we will have access to DR or DC. Data sinking is done automatically after every 5 minutes.
Reports will not be automatically synced and we have to manually do it.
Ideal practice: When we are deploying the reports, it better to deploy it both the servers (DR & DC).
DC :
Database server
1) Misdb001 Main Database & Report server is also maintained here.
2) Misdb002 AB_STGDB
3) Misdb003 it is backup of Misdb001 (data will automatically sink into this DB, if Misdb001 goes down, we can
use this Database.
Application servers
Misap001 ( it will act as a cluster and redirect the request to NLB and balances the load across the servers
while users are accessing the application. )
Misap002
Misap003
DR:
Database server
Mis101 Main Database & Report server is also maintained here.
Mis102 it is backup of Misdb001 (data will automatically sink into this DB, if Mis101 goes down, we can use
this Database.
Application servers
Misap001 (here we are giving IP and no DNS concept is available here)
Misap002
======================================================================================================
HCL
1.How many packages created
2.Mearge & Mearge join Diffen
3.Union & Union All (Database)
4.Lookup Transform
5.Conditional Spilt Transform
6.Viewes (database)
7.Write a 2 higest sal use stroed procedure
8.what is diff table & view
9.Stored procedure
======================================================================================================
Patni
1.Flat different types
2.10 Records are there 3 gents and 7 womens by use conditional split
3.Derived column
4.Look Up transform
5.Look up & Fizzy look up diff
6.Foreach loop(container)
7.Scd
8.Conditional Split
9.Staging Ariea
10.Surget key types
11.Connection Mager
======================================================================================================
Sourceone
1 Excel =Transform = Destination these package move to sequence container any changes is come?
2.For each loop
3.look up,Derived column,Conditional split
4.Configaration
5.Deployee
6.Security
7.Scd
8.Table vs matrix
9.Drill down & drill through
10.Deployement
11.how many roles
12.Linked reports
13.Control flow means
14.How many primary keys and fact tables used in Project?
15.Index & Index Types
16.Procedures & functions
17.Triggers
18.Configaration Types
19.How to process the package
20 FTP Task ,Fille system task
21.Cube Genaration
22.Jion types and what is self join
23.Table Vs temperory variable
24.Common table expression
======================================================================================================
Marlabs
1.Control flow troubleshooting
I gate
1.Configaration
2.Logging
3.Check points
4.Mearge Join
5 Table records month wise instead of month wise jan display in destination I want month number(Jan) reprsents
(1)
6.Syconranise Vs Asyconraise
Theram
1.Delete duplicate records
2.Common table expression
3.Cross tab properties
4.Foreach loop
5.Login Vs User name
6.Windows authentication VS sql server authentication
7.Index and cluster index
8.Triggers and cursors
9.Magic Table
10.firstname,Deptno,employee no need get employeenumer wise who is the manger of the particular employee wise
11.database vs datamart
12.Star schema vs snow flake schema
13.Sp=help va sp=help taxt
======================================================================================================
Monthan Systems
1.Duplicate records
2.stuid,Subid,marks subject wise avg marks?
3.Scd 4.Mearge Join
4.Fact Table
Mind tre
1.eid,Ename,Dob,sal write the query
Eid Ename Dob sal
1 A 3
2 B 3
3 C 3
4 D 1 who is the manger of eid?
2.space ABC,ABC Space how to find out or I nedd to display with out space
3.In ETL source and distination mismatch?
4.In source 100 records are there ,in Destnation load 95 records what you are doing?
======================================================================================================
Inteligroup
1.Bank project credit card process how to implement?
2.Blocks Vs Non Blocks
3.SCD,ACID in sql how to sue
4.for loop vs for each loop
5.Join s
Emirtius
1.Intersection & except
2.Locks
3.Inner join & Outer join
4.Deployment.
5.cube creation what is cube?
6.cube Configaration
7.report deployment
8.Clusterindex & Non clusterindex
======================================================================================================
Spike Systems
1.control flow Vs Dataflow
2.index
3.Mearge and merge join
4.merge and union all
5.What are datamodular then in your project
6.star vs snowflake schema
======================================================================================================
Haxwere
1.SSIS security
2.SSIS transformation
3.merge & merge join
4.project & Deployment.
Capgemini
1.Modeling Types
======================================================================================================
Netpostive
1.Project
2.Merge & Merge Join
3.performance of SSIS
4.Deplyment & Schuduling
======================================================================================================
ANZ Technologies
1. Scheduling,Deployment,Break points
2. Index types
3. OlTP source Conditionl trans Excl destination Task is data destination is load inti sheet 1 and sheet 2
load is it possible?
4. Source flat filles 4 records is there 4 records load into tables 4 destination tables?
======================================================================================================
Keneeindia
1.Coinfigaration Types
2.Deployment types
3.Report1 and report2 need to store in same excel is it possible?
4.In sql data empname,Ename,add1,add2,add3,add4
In report I want empname empid add
Add1
Add2
Add3
That type format report ?
======================================================================================================
Symphoney
1.Delete duplicate rows
2.2 nd highest sal
3.Derived column Vs copyof column
4.2 nd highest salry in ssis
======================================================================================================
TCS
1. Dimension table
2.how to connect starschema to snow falke
3.union all ,mearge
4. Configaration
5. Exception handling stored procedure
6.Multiple table view creation possible or not
7.Condition wise colors need to change in reports?
8Date wise total
9.case cading parameters
10.select getdate () after that mm,dd,yy format want?
11.Fuzzy lookup
======================================================================================================
Satyam
1. Project
2. One package how to control different source like flat fille,Excel,oledb
3. What is delay validation
4. In SSIS source like flatfille 10 records 5 records nullvalue another don’t save null values in
destination?
5. What is view?
6. Diff stored procedure and functions
======================================================================================================
MARLABS
1.Project
2.Merge function in sql
3.Logging
======================================================================================================
Satyam
1.Project
2. Control flow
3. Control flow where you write in storedprocedure
4.Error handling in ssis
5.logging
======================================================================================================
Marlabs
1.Max functions are used
2.MDX syntax
3.Matrix report
4.Drill down ,drill through
5.Report in cube
6.Alternate colour in cube
7.Report performance
======================================================================================================
Inteligroup
1.What are sources of project
2.Fact table source and dimention source
3.What is business key and surgetgey
4.in Drill down subreport calling or not
5.Drilldoen and drill through
6.Condition with in condition is possible or not?
======================================================================================================
Syntel
1. Tell me about your self
2. Scd type1 and type2
3. Suroget key
4. Multicost vs conditional split
5. Merge and union all
6. Union ,union all
7. Jions
8. Star schema ,snowflake schema
9. Incrmental loding
10. Logging
11. Configuration types
12. I have 6 tasks are there 4 th one failed ,how you will handle this?
13. Blocking,unblocking transformations
14. Xml how to handle
15. Check points
======================================================================================================
SIS
1.How to load oltp to database explain
2. Cube?
3.lokup and fuzzy lookup
4. Extract handling ssis
5.How to create variable ,Dynamically changing variable is there how to handle?
6. Shecduling package
7.Cube processing?
8.Flat fille ,xml source is there and ftp task so will can 10 clock morning idont know which fille come so
remaining fille are need to store along with fille?
======================================================================================================
L&T
How to generate execute query I want cumulative total
2.Nth hight Sal
3.In excel 100 records is there in source table loaded into taget destination .After 76 th one delete
information after again load into destination any problem is comeing?
4.I source table 32 bit information is there but destination 64 bit how iyou insert table?
======================================================================================================
Comcast
======
1. Brief introduction to my background
2. Rate yourself on sql(1 - 10)
3. Questions on Joins
4. Diff between Where and Having
5. Indexes, Pro's and con's
6. How to improve slowing running queries
7. SCD(Examples)
8. Custom code
9. how to return query result as XML
10. do you want to do ETL or Tableau job?
11. Challenges you faced
12. Max size of nvarchar
13. Joins vs Subqueries
14. SP use case
15. How to store image in db coming from application(data type)
16. How do you work when req's change
17. why comcast
18. what kind of viz in tableau did u built
19. Error handling in SQL
20. Rate your comunication skills
========================================================================================================
Tempur Sealy
. Tell me about self.
2. Explain any of your project?
3. what is the differnce between Varchar and Nvarchar, what is the maximum size of them?
4. what are the interger data types that you are familiar with?
5. Explain about Merge statement in SQL?
6. Explain about the transactions in SQL? Tell the syntax for that?
7. Difference between CTE and Temp table?
8. Differnece between Foreach loop and Sequence container?
9. Slowly changing dimensions?
10.Types of index and where it is used?
11.How do you optmize the stored procedure?
12.Difference between package level and project level parameters?
========================================================================================================
MSBI Characteristics
Criteria Result
Analytics Dashboards,
guided navigation and drill down
Type of Solution End-to-end
Business solution with extended ETL
User-friendly Ease
of installation, use, maintenance
2. Define Query parameter in SSRS?
Query parameters is revealed in the query of the datasources
that are to be included into the SQL script’s where clause of the SQL that can
accept parameters. Query parameters begin with the symbol @.
3. What are the Reporting Service Constituents in SSRS?
A. Report Manager : A Web-based administration tool for
managing the Report Server.
B Report Server : Provides services for implementation and
delivery of reports.
C. Report Designer : A place where we can create report.
4. What do you understand by matrix in SSRS?
A matrix is a data region related to a report set. Matrix
permits us to create crosstab reports with the report variables showing on rows
and columns. It allows us to drag.
5. What do you mean by sub reports and how we can create
them?
A sub report is like any other reports which can be termed
in main report and can be generate through main report. Parameters can be
conceded from main report to sub report and basis of that report can be
created.
6. Define report model project?
Report model project is used for creating Adhoc reporting.
We can create the adhoc reports over report builder. Report model project can
be created on bids or report server. This model can have simple view.
7. What do you understand by report server project?
Report Server Project comprises of RDL file and it need to
be deployed on report server to view the report files to application and user.
It is a solution where we can design our reports. We can add it by going into
BIDS clicking on new item and then selecting reports server project. Once the
solution is formed we can start forming reports.
Interested in learning Microsoft Business Intelligence? We
have the right Training Course.
8. Explain report builder?
Report builder is used to create small reports and it is a
define interface. We can’t change the report interface in report builder it pre
designed. We can just drag columns in the report. It creates reports on
database objects available with report model project.
9. In which SQL Server type report builder introduced?
Report builder presented in SQL Server 2005. While creating
or arranging report model project on report server we can get error or it might
not get formed. For this we need to check whether the service pack 22 is
installed or not.
10. How to organise the Report?
Report can be organized in three ways :
1. Using
visual studio : In visual studio we can directly deploy the report through
solution explorer by providing the report server URL in project properties at
Target Server URL. This will organize entire project or single report as per
our selection.
2. Using
report server : We can directly go to the report server and deploy the report
by looking the report from the disk location on server.
3. Creating
the utility : SQL server provides the utility which can be used to create a
modify utility for our report deployment in bulk.
Get a detailed understanding of MSBI in this MSBI Tutorial
now.
11. Define RS.exe utility?
Rs.exe utility is used for organizing the report on report
server. It comes with the report server and can be modify accordingly.
12. What is the name of reporting services config file and
what it is used for?
Reporting service config file is used for report
configuration details. It contains the report format and also the report import
types. Report service config exist in ISS.
13. What are the three different part of RDL file elaborate
them?
In visual studio RDL files has three parts.
1. Data : It
covers the dataset on which we write the query. Data set is associated with
data source.
2. Design : In
design we can design report. We can create tables and matrix reports. We Drag
columns values from source.
3. Preview :
It ia used to check the preview after the report run.
14. Which language rdl files made of?
RDL files are printed in XML.
15. What do you understand by chart in report?
Chart reports are for graphical representation. We can get
pie charts columns harts and various other options. 3d charts are also
presented in reporting services.
16. Define Data Set in report?
Data set are the set of data which we want to show in
report. Data creates on data source. Data source is the source of data from
where we are receiving this data i.e. database server and database name joining
string.
17. Name different types of data sources in SSRS?
SSRS use different data source. Some of them are listed
below.
• OLEDB.
• SQL
Server SAP Net weaver BI.
• Oracle.
• Report
Server Model.
• SQL
Server Analysis Service OLEDB.
• ODBC.
• SAP Net
weaver BI.
• Hyperion.
• Teradata.
• XML
18. What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By retrieving
this web service we can access all report server section and also get the
report organized on report server.
19. How to enhance the custom code in Report?
To enhance the custom codes in report go to report tab on
top then properties and there you will find the selections for custom code.
20. Define cache in SSRS?
Report server can lay up a copy of processed report in a
memory and return the copy when a user opens the report. This server memory is
called as cache and the process is known as caching.
21. Elaborate Web service task in SSIS?
• First we
configure HTTP Connection manager which will point to WSDL of a web service.
• Web
service task customs this HTTP Connection manager and let us invoke methods in
it.
• It return
values of method value we can store it in some variables and can use as input
for some other tasks.
Give your career a big boost by going through our MSBI
Online Training Videos now!
22. Explain transfer SQL Server object task?
It allows us to allocate different SQL server objects
between different instances of SQL Server.Object incomes from table, stored
procedures, user defined functions etc.
23. In SSIS is it conceivable to communicate with MSMQ?
Yes, it is possible for that we have Message Queue task. It
contract us send messages to MSMQ and receive message from MSMQ.
24. What is the advantage of using MSMQ?
MSMQ helps in communicating two applications with each other
asynchronously. Specialty is two application may be built using dissimilar
technology and it works even offline messaging. Sender will stock messages
inside queue and reader reads it wherever required.
25. What are the Different Lookup Cache Modes Available in
SSIS?
There are basically 4 Cache Modes available in SSIS Lookup
Transformation
1. Full
Cache Mode.
2. Partial
Cache Mode.
3. No Cache
Mode.
4. Full
Cache Mode
26. Differentiate between SSRS 2005 and SSRS 2008
The major differences between SSRS 2005 and SSRS 2008 are as
follows:
SSRS 2005 report server requires IIS, whereas SSRS 2008
comes with a build-in web server and hence does not require IIS.
27. Explain architecture of SSIS?
SSIS architecture involves four key parts :
a) Integration Services service :
It monitors running Integration Services packages and
accomplishes the storage of packages.
b) Integration Services object model :
It contains managed API for reading Integration Services
tools, command-line utilities, and custom applications.
c) Integration Services runtime and run-time executables :
It keeps the layout of packages, runs packages, and provides
support for logging, breakpoints, configuration, connections, and transactions.
d) Data flow engine :
It provides the in-memory buffers that move data from source
to destination.
28. How we can do Logging in SSIS?
Logging Configuration provides an inbuilt feature which can
log the detail of various events like onError, onWarning etc .
29. How we can do Error Handling?
SSIS package contain mainly have two types of errors :
a) Procedure Error : It can be handled in Control flow bythe
precedence control and redirecting the execution flow.
b) Data Error : it is handled in DATA FLOW TASK by
redirecting the data flow using Error Output of a component.
30. How we can pass property value at Run time?
A property value like connection string for a Connection
Manager can be passed to the pkg using package configurations.
31. How can we organize a SSIS Package on production?
A)Through Manifest we can organize it :
1. Create
deployment utility by setting its properties as true .
2. It will
be formed in the bin folder of the solution once package is build.
3. Copy all
the files in the utility and use manifest file to display it on the Prod.
B) Using DtsExec.exe utility.
C)Import Package directly in MSDB from SSMS by logging in
Integration Services.
32. Define Execution Tree?
Execution trees demonstrate how package uses buffers and
threads. At run time, the data flow engine breaks down Data Flow task
operations into execution trees.
33. Differentiate between Unionall and Merge Join?
a) Merge transformation can take only two inputs whereas
Union all can take more than two inputs
b) Data has to be sorted before Merge Transformation whereas
Union all doesn’t have any boundation like that.
34. How we can restart package from previous failure point?
Define Checkpoints and how they are implement in SSIS?
When a package is arranged to use checkpoints, material
about package execution is written to a checkpoint file. When the failed
package is rerun, the checkpoint file is used to restart the package from the
point of failure. If the package runs effectively, the checkpoint file is
deleted, and then re-created the next time that the package is run.Learn more
about MSBI in this MSBI Certifications Training to get ahead in your career!
35. Where do you store SSIS package in the SQL Server?
MSDB.sysdtspackages90 stores the original content .
36. What are the three stages of Enterprise Reporting Life
Cycle ?
a. Management
b. Authoring
c. Access and Delivery
37. What are the apparatuses included in SSRS?
1. A
Complete set of Tools that can be used to generate, succeed and view reports.
2. A Report
Server component that hosts and processes reports in many formats. Output
formats include HTML, PDF and more.
3. An API
that allows developers to mix or spread data and report processing in custom applications,
or create custom tools to build and manage reports.
38. What are the benefits of using embedded code in a
report?
The benefits are:
1. Reuseability
of Code: function created in embedded code to achieve a logic can be then used
in manifold expressions
2.
Centralized code: it helps in better manageability of code.
39. Which programming language can be used to code embedded
functions in SSRS?
Visual Basic .NET Code is used to code embedded fuctions in
SSRS.
40. What important terms can be used in the reporting
services?
1. Report
definition: A report definition contains information about the query and layout
for the report.
2. Report
snapshot: A report snapshot is actually a report definition that contains a
dataset instead of query instructions.
3. Rendered
report: A fully processed report that contains both data and layout
information, in a format suitable for viewing (such as HTML).
4.
Parameterized report: A published report that accepts input values through
parameters.
5. Shared
data source: A predefined, standalone item that contains data source connection
information.
6. Shared
schedule: It is a predefined, standalone item that covers schedule information.
7. Report-specific
data source: Data source information which is defined within a report
definition.
8. Report
model: A semantic description of business data, used for ac hoc reports created
in Report Builder.
9. Linked
report: It is a report that derives its definition through a link to another
report.
41. What are the Command Line Utilities available in
Reporting Services?
• Rsconfig
Utility (Rsconfig.exe): encrypts and stores connection and account values in
the RSReportServer.config file.
• RsKeymgmt
Utility: it is used to Extracts, restores, creates, and deletes the symmetric
key used to protect sensitive report server data against unauthorized access
• RS
Utility: this utility is mainly used to automate report server deployment.
42. How we can know Report Execution History?
We can know Report Execution History as the execution log
table in report server database contains all the logs from the last two months
so we can see it from there.
Select-from reportserver.dbo.ExecutionLog
43. Differentiate between Tablular and Matrix report?
1. Tablular
report: It is the most basic type of report. Each column corresponds to a
column selected from the database.
2. Matrix
report: A matrix report is a cross-tabulation of four groups of data:
a. One group of data is displayed across the page.
b. One group of data is displayed down the page.
c. One group of data is the cross-product.
d. One group of data is displayed as the “filler” of the
cells.
44. How we can create Drill-through reports?
By Using Navigation property of a cell and scenery child
report and its parameters in it we can create Drill-through reports.
45. How to create Drill-Down reports?
1. By
grouping data on essential fields
2. Then
toggle reflectivity based on the grouped filed
46. How we can schedule a SSIS packages?
Using SQL Server Agent we can schedule a SSIS packages.
47. Define Asynchronous transformation?
Asynchronous transformation have various Input and Output
buffers and it is up to the component designer. It provide a column structure
to the output buffer and hook up the data from the input.
48. How we can achieve parallelism in SSIS?
Parallelism is achieved using MaxConcurrentExecutable stuff
of the package. Its default is -1 and is calculated as number of computers + 2.
49. How we can do incremental load?
Accurate and fastest way to do incremental load is by using
Timestamp column in source table and then storing last ETL timestamp.
50. How we can handle Early Arriving Facts.
Early Arriving Facts sometime get unavoidable because delay
or error in Dimension ETL or may be due to logic of ETL. To handle Early
arriving Facts, we can create dummy Dimension with natural/business key and
keep rest of the attributes as null or default.
51. Define SQL Server Reporting Services(SSRS)?
SQL Server Reporting Services is a server-based reporting
platform that we can use to create and manage tabular, matrix, graphical, and
free-form reports that contain data from relational and multidimensional data
sources.
SQL
SERVER INTERVIEW QUESTIONS
|
1. What are STATISTICS? What are their purposes?
2. How does non-clustered index differ from clustered index?
3. How is non-clustered index stored physically in the absence of
clustered index in table?
4. What is index seek and index scan? Which one will perform better
in which scenario?
5. How do you resolve dead lock issue when it happens at index level?
6. What are extent and its size?
7. What is the purpose of latch in SQL Server?
8. What is the difference between latches and locks?
9. Explain the purpose of column store index
10. Explain the difference between Loop, Hash and Merged Join types
|
Share training and course content with friends and students:
msbi course fee
msbi
msbi training in hyderabad
msbi course
msbi course details
MSBI Training Institute in Hyderabad Hyderabad Telangana
msbi course fee in hyderabad
msbi online training
best msbi online training
msbi training
Msbi training inistitutes in ameerpet
msbi courses
msbi classes in hyderabad
msbi online classes
best msbi online training in hyderabad
best msbi training
Microsoft BI training in hyderabad
Microsoft BI training
Microsoft BI online training
SSIS training
SSAS training
SSRS training
ssis interview questions
ssas interview questions
ssrs interview questions
power bi training
power bi training in hyderabad
power bi online traning
msbi course fee
msbi
msbi training in hyderabad
msbi course
msbi course details
MSBI Training Institute in Hyderabad Hyderabad Telangana
msbi course fee in hyderabad
msbi online training
best msbi online training
msbi training
Msbi training inistitutes in ameerpet
msbi courses
msbi classes in hyderabad
msbi online classes
best msbi online training in hyderabad
best msbi training
Microsoft BI training in hyderabad
Microsoft BI training
Microsoft BI online training
SSIS training
SSAS training
SSRS training
ssis interview questions
ssas interview questions
ssrs interview questions
power bi training
power bi training in hyderabad
power bi online traning
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.