What is SQL Server Integration Services (SSIS)?
- SQL
Server Integration Services (SSIS) is component of SQL Server 2005 and
later versions. SSIS is an enterprise scale ETL (Extraction,
Transformation and Load) tool which allows you to develop data integration
and workflow solutions. Apart from data integration, SSIS can be used to
define workflows to automate updating multi-dimensional cubes and
automating maintenance tasks for SQL Server databases.
How does SSIS differ from DTS?
- SSIS
is a successor to DTS (Data Transformation Services) and has been
completely re-written from scratch to overcome the limitations of DTS
which was available in SQL Server 2000 and earlier versions. A significant
improvement is the segregation of the control/work flow from the data flow
and the ability to use a buffer/memory oriented architecture for data
flows and transformations which improve performance.
What is the Control Flow?
- When
you start working with SSIS, you first create a package which is nothing
but a collection of tasks or package components. The control flow
allows you to order the workflow, so you can ensure tasks/components get
executed in the appropriate order.
What is the Data Flow Engine?
- The
Data Flow Engine, also called the SSIS pipeline engine, is responsible for
managing the flow of data from the source to the destination and
performing transformations (lookups, data cleansing etc.). Data flow
uses memory oriented architecture, called buffers, during the data flow
and transformations which allows it to execute extremely fast. This means
the SSIS pipeline engine pulls data from the source, stores it in buffers
(in-memory), does the requested transformations in the buffers and writes
to the destination. The benefit is that it provides the fastest transformation
as it happens in memory and we don't need to stage the data for
transformations in most cases.
What is a Transformation?
- A
transformation simply means bringing in the data in a desired format. For
example you are pulling data from the source and want to ensure only
distinct records are written to the destination, so duplicates are
removed. Anther example is if you have master/reference data and
want to pull only related data from the source and hence you need some
sort of lookup. There are around 30 transformation tasks available and
this can be extended further with custom built tasks if needed.
What is a Task?
- A task
is very much like a method of any programming language which represents or
carries out an individual unit of work. There are broadly two categories
of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All
Control Flow tasks are operational in nature except Data Flow tasks.
Although there are around 30 control flow tasks which you can use in your
package you can also develop your own custom tasks with your choice of
.NET programming language.
What is a Precedence Constraint and what types of
Precedence Constraint are there?
- SSIS
allows you to place as many as tasks you want to be placed in control
flow. You can connect all these tasks using connectors called Precedence
Constraints. Precedence Constraints allow you to define the logical
sequence of tasks in the order they should be executed. You can also
specify a condition to be evaluated before the next task in the flow is
executed.
- These
are the types of precedence constraints and the condition could be either
a constraint, an expression or both
- Success
(next task will be executed only when the last task completed
successfully) or
- Failure
(next task will be executed only when the last task failed) or
- Complete
(next task will be executed no matter the last task was completed or
failed).
What is a container and how many types of containers are
there?
- A
container is a logical grouping of tasks which allows you to manage the
scope of the tasks together.
- These
are the types of containers in SSIS:
- Sequence
Container - Used for grouping logically related tasks together
- For
Loop Container - Used when you want to have repeating flow in package
- For
Each Loop Container - Used for enumerating each object in a collection;
for example a record set or a list of files.
- Apart
from the above mentioned containers, there is one more container called
the Task Host Container which is not visible from the IDE, but every task
is contained in it (the default container for all the tasks).
What are variables and what is variable scope?
- A
variable is used to store values. There are basically two types of
variables, System Variable (like ErrorCode, ErrorDescription, PackageName
etc) whose values you can use but cannot change and User Variable which
you create, assign values and read as needed. A variable can hold a value
of the data type you have chosen when you defined the variable.
- Variables
can have a different scope depending on where it was defined. For example
you can have package level variables which are accessible to all the tasks
in the package and there could also be container level variables which are
accessible only to those tasks that are within the container.
What are data regions?
Data regions are
report items that display repeated rows of summarized information from
datasets.
You want to generate a report that is formatted as a chart. Can
you use the Report Wizard to create such a report?
No, the Report Wizard lets you create only tabular and matrix reports. you must
create the chart report directly by using the Report Designer.
You want to use BIDS to deploy a report to a different server
than the one you chose in the Report Wizard. How can you change the server URL?
You can right-click the project in Solution Explorer and then change the
Target-Server URL property.
What are the three SSRS command-line utilities
and their primary functions?
RSConfig.exe is
used to define the connection properties from the SSRS instance to the Report
Server database; RSKeyMgmt.exe performs encryption key operations and scale-out
deployment setup; RS.exe runs Report Server Script files that can perform
report deployment and management operations.
Can you always create a cache of a report?
No, you can create a cache of a report only when certain requirements, such
as having credentials stored in the Report Server, are met.
Can you edit the .rdl code associated with a
linked report?
No, because a linked report has no .rdl code of its own. It refers to the .rdl
code of the base report
What is Analysis Services
1.Microsoft SQL Server 2012 Analysis Services (SSAS) delivers online analytical
processing (OLAP) and data mining functionality for business intelligence
applications.
2.Analysis Services supports OLAP by letting we design, create, and manage
multidimensional structures that contain data aggregated from other data
sources, such as relational databases.
3.For data mining applications, Analysis Services lets we design, create, and
visualize data mining models that are constructed from other data sources by
using a wide variety of industry-standar data mining algorithms
What are the features of Analysis Services
1.Ease of use with a lot of wizards and designers.
2.Flexible data model creation
and management
3.Scalable architecture to handle OLAP
4.Provides integration of
administration tools, data sources, security, caching, and
reporting etc.
5.Provides extensive support for
custom applications
What is the need for SSAS 2012 component?
1.Analysis Services is the only
component in SQL Server using which we can perform Analysis and Forecast
operations
2.Faster Analysis and Troubleshooting.
3.Ability to create and manage Data warehouses
4.Apply efficient Security Principles
What
languages are used in SSAS 2012
1.Structured Query Language (SQL)
2.Multidimensional Expressions (MDX) - an industry standard query language
orientated towards analysis
3.Data Mining Extensions (DMX) - an industry standard query language oriented
toward data mining.
4.Analysis Services Scripting Language (ASSL) - used to manage Analysis
Services database objects
What is the difference between
a derived measure and a calculated measure?
1. The difference
between a derived measure and a calculated measure is when the calculation is
performed.
2. A
derived measure is calculated before aggregations are created, and the values
of the derived measure are stored in the cube.
3. A
calculated measure is calculated after aggregations are created, and the values
of a calculated measure aren’t stored in the cube.
4. The
primary criterion for choosing between a derived measure and a calculated
measure is not efficiency, but accuracy
What is FASMI ?
A database is called a OLAP
Database if the database satisfies the FASMI rules
1. Fast
Analysis– is defined in the OLAP scenario in five seconds or less.
- Shared
– Must support access to data by many users in the factors of
Sensitivity and Write Backs.
- Multidimensional
– The data inside the OLAP Database must be multidimensional in
structure.
- Information
– The OLAP database Must support large volumes of data.
What is a partition?
-
A partition in Analysis Services is the physical location of
stored cube data.
-
Every cube has at least one partition by default. Each time we
create a measure group, another partition is created.
-
Queries run faster against a partitioned cube because Analysis Services
only needs to read data from the partitions that contain the answers to
the queries.
-
Queries run even faster when partition also stores aggregations, the
pre calculated totals for additive measures. Partitions are a powerful and
flexible means of managing cubes, especially large cubes
What are the roles of an Analysis Services Information Worker?
The role
of an Analysis Services information worker is the traditional "domain
expert" role in business intelligence (BI) someone who understands the
data employed by a solution and is able to translate the data into business
information.The role of an Analysis Services information worker often has one
of the following job titles: Business Analyst (Report Consumer), Manager
(Report Consumer), Technical Trainer, Help Desk/Operation, or Network
Administrator
Explain the TWO-Tier Architecture of SSAS?
- SSAS uses both server
and client components to supply OLAP and data mining functionality BI
Applications.
- The server component is
implemented as a Microsoft Windows service. Each instance of Analysis
Services implemented as a separate instance of the Windows service.
- Clients communicate with
Analysis Services using the standard the XMLA (XML For Analysis) ,
protocol for issuing commands and receiving responses, exposed as a web
service
How many types of dimension are possible in SSAS?
- Bill of Materials
- Currency
- Channel
- Customer
- Geography
- Organizations
- Products
- promotion
- Regular
- Scenario
- Time
- Unary
- Accounts
