Explore topic-wise InterviewSolutions in .

If you CREATE an Aggregate with more than 16 characteristics, then we call that aggregate as FLAT Aggregate. When an aggregate is ACTIVE then new data is not available for REPORTING, these aggregates are called Flat Aggregates.

If you create an Aggregate with more than 16 characteristics, then we call that aggregate as FLAT Aggregate. When an aggregate is active then new data is not available for reporting, these aggregates are called Flat Aggregates.

A conditional REPORT is based on the data gathered, then ANALYZED during the report GENERATION, which REPORTS out results of the analysis in the report's output.

A conditional report is based on the data gathered, then analyzed during the report generation, which reports out results of the analysis in the report's output.

• Business content can come from various parts of your business operations. Some from the finance department and some from the SALES department.
• If these business content are consistent and can be used by both departments then it's all about loading the DATA into a data warehouse for analysis purposes.
• In case data are inconsistent and can't be easily compared, then a data integration tool would help to integrate data from various data sources before it is LOADED into the data warehouse.

BPM is very useful in analyzing the CURRENT performance of your BUSINESS. After analyzing the performance of different operations in your organization you can easily identify the various areas where you need to focus on in ORDER to increase the profits and Revenue.
This process will certainly help you in PREPARING yourself for the future strategies which in turn will help you in competitive advantage and taking corrective effective decisions at the RIGHT time.

BPM is very useful in analyzing the current performance of your business. After analyzing the performance of different operations in your organization you can easily identify the various areas where you need to focus on in order to increase the profits and Revenue.
This process will certainly help you in preparing yourself for the future strategies which in turn will help you in competitive advantage and taking corrective effective decisions at the right time.

• SQL Server INTEGRATION Services:- Used for data transformation and creation. Used in data acquisition form a source system.
• SQL Server Analysis Services: Allows data DISCOVERY using data MINING. Using business logic it supports data enhancement.
• SQL Server REPORTING Services:- Used for Data presentation and distribution access.

Dashboard in BUSINESS INTELLIGENCE is used for rapid prototyping, cloning and deployment for all databases, operational applications or spread sheets through an organization.
A dashboard in BI allows an enterprise’s status/position, heading to, by USING graphs, maps and chars. The drill-down and roll-over capabilities allows organizing things without revealing important information. It is FULLY customizable, INCLUDING free-form design options. Dashboard consolidates vital statistics of business into an easy-to-read page.

Dashboard in business intelligence is used for rapid prototyping, cloning and deployment for all databases, operational applications or spread sheets through an organization.
A dashboard in BI allows an enterprise’s status/position, heading to, by using graphs, maps and chars. The drill-down and roll-over capabilities allows organizing things without revealing important information. It is fully customizable, including free-form design options. Dashboard consolidates vital statistics of business into an easy-to-read page.

A "universe" is a "Business object" terminology. Business OBJECTS also happens to be the name of the company. The universe is the interfacing LAYER between the client and the data WAREHOUSE . The universe DEFINES the relationship among the various tables in the data warehouse.

A "universe" is a "Business object" terminology. Business objects also happens to be the name of the company. The universe is the interfacing layer between the client and the data warehouse . The universe defines the relationship among the various tables in the data warehouse.

You can create multiple QUERIES by USING Data Provider option available in the REPORTING TOOL.

You can create multiple queries by using Data Provider option available in the reporting tool.

1. Operational Data Store has very low data latency. Data moved to ODS mostly on event BASED rather than time based ETL to Data Warehouse/Data MART.
2. ODS is more closer to OLTP system. We don't normally PREFER to store aggregated data in OLTP. So it is with ODS.
3. Unlike data warehouse where data is HISTORICAL, ODS is near REAL time(NRT). So data aggregation is less important is ODS as data keeps changing.

HOLAP: HYBRIDIZATION of OLAP.

HOLAP: Hybridization of OLAP.

ROLAP - Relational OLAP.
Designates ONE or several star schemas stored in relational databases. This TECHNOLOGY permits MULTIDIMENSIONAL analysis with data stored in relational databases. USED for large departments or groups because it supports large AMOUNTS of data and users.

ROLAP - Relational OLAP.
Designates one or several star schemas stored in relational databases. This technology permits multidimensional analysis with data stored in relational databases. Used for large departments or groups because it supports large amounts of data and users.

DOLAP - Desktop OLAP.
Small OLAP products for local multidimensional analysis Desktop OLAP. There can be a mini multidimensional database (using Personal Express), or extraction of a datacube (using Business OBJECTS).
Designed for low-end, single, departmental USER. Data is stored in CUBES on the desktop. It's like having your own spreadsheet. SINCE the data is local, end users don't have to worry about PERFORMANCE hits against the server.

DOLAP - Desktop OLAP.
Small OLAP products for local multidimensional analysis Desktop OLAP. There can be a mini multidimensional database (using Personal Express), or extraction of a datacube (using Business Objects).
Designed for low-end, single, departmental user. Data is stored in cubes on the desktop. It's like having your own spreadsheet. Since the data is local, end users don't have to worry about performance hits against the server.

MOLAP - Multidimensional OLAP.
This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances PERFORMANCES. In the LATTER, joins are computed during the request.
Targeted at groups of users because it's a shared environment. Data is STORED in an EXCLUSIVE server-based format. It PERFORMS more complex analysis of data.

MOLAP - Multidimensional OLAP.
This term designates a Cartesian data structure more specifically. In effect, MOLAP contrasts with ROLAP. In the former, joins between tables are already suitable, which enhances performances. In the latter, joins are computed during the request.
Targeted at groups of users because it's a shared environment. Data is stored in an exclusive server-based format. It performs more complex analysis of data.

• OLAP: It’s an online analytical PROCESSING tool. There are various products AVAILABLE for data ANALYSIS.
• ETL: Extract, Transform and Load. This is a PRODUCT to extract the data from multiple/single source transform the data and load it into a table, flat file or simply a TARGET.

• It’s an BUSINESS INTELLIGENCE TOOLS. Like Brio which was an independent product bought over my Hyperion has converted this product name to Hyperion Intelligence.
• Yes. You can analyze DATA schemas using this tools.

VIEWS

• A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
• All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
• A View can be used to simplify SQL statements for the user or to isolate an application from any future CHANGE to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
• In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized Views

• Materialized views are schema objects that can be used to summarize, precompute, REPLICATE, and distribute data. E.g. to construct a data warehouse.
• A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ORDINARY view, which does not take up any storage space or CONTAIN any data.
• The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

Views

Materialized Views

The TYPES of parameters in SSRS are:

• SINGLE VALUE parameters
• MULTI value parameters
• CASCADING parameters.

The types of parameters in SSRS are:

• Member is nothing but column on dimension table. MEASURE is nothing but column on fact table.
• The TERM calculated member refers to the creation of any MDX object through a calculation. The calculated member can be part of the measures dimension where a simple MDX expression such as addition or subtraction of two or more BASE measures results in a new measure. Such calculated members on the measure dimension are REFERRED to as calculated measures.
• Calculated Member is RELATED to the Dimension where as calculated Measure is related to the measure in measure group.

Data Source Views (DSV) have been INTRODUCED in SQL SERVER Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes WITHOUT being CONNECTED to the underlying source.

Data Source Views (DSV) have been introduced in SQL Server Analysis Services (SSAS) 2005. A DSV is a metadata layer between the cube and the underlying data source that allows us to build our cubes without being connected to the underlying source.

Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the ACTUAL Sql server analysis server. This process is called DEPLOYMENT of cube. (like creating one object(table) in the relational DATABASE).
So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the AGGREGATIONS is called Cube processing.(like inserting the data into your table) So to make a cube ONLINE for the user for QUERYING we need to build the structure of the cube in BIDS and deploy it to a SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube.

Once you build the cube in the BIDS, we can say the structure of the cube is created. So once the cube structure is built we need to deploy the structure to the actual Sql server analysis server. This process is called DEPLOYMENT of cube. (like creating one object(table) in the relational database).
So once you deploy the structure to the SSAS server, we need to load the data into the structure we have created. This process of loading data into the cube and creating the aggregations is called Cube processing.(like inserting the data into your table) So to make a cube online for the user for querying we need to build the structure of the cube in BIDS and deploy it to a SSAS server and the process it to load with data and aggregations, then the user can query on the SSAS cube.

Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) PACKAGES. If you call the other SSIS packages in a new SSIS package USING ‘Execute Package Task’, the new package called as master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql SERVER agent by adding the all child packages values in the master package config file.

Master package is nothing but it is also a normal SSIS package, it act like a master to run the all other (child) packages. If you call the other SSIS packages in a new SSIS package using ‘Execute Package Task’, the new package called as master package. The use of this package(Master) is, instead of running all individual packages if we can run the single master package, then automatically all the child packages will run. Normally we simply schedule the master package in Sql server agent by adding the all child packages values in the master package config file.

SCRIPT task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.
Normally we can implement custom code by using these components. By using this TWO items we can perform any action like DML OPERATIONS on the data from DB, file and we can do any operation using c# or vb.net code we can use it as SOURCE, destination as well.

Script task is control flow level item where as script component is data flow level item, both of the functionalities are same. This 2 are very powerful items in SSIS.
Normally we can implement custom code by using these components. By using this two items we can perform any action like DML operations on the data from DB, file and we can do any operation using c# or vb.net code we can use it as Source, destination as well.

You can USE ALTER CUBE statement. Syntax:

You can use ALTER CUBE statement. Syntax:

Use could usefunction Set.Item(0)
EXAMPLE:

Use could usefunction Set.Item(0)
Example:

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets SAY we want to SEE order counts for each year. In ADVENTURE Works MDX query would be:

Same query USING TopCount:

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

Same query using TopCount:

SIMPLY USING bottomcount will return customers with null SALES. You will have to combine it with NONEMPTY or FILTER.

Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

This is a great question because you only know this answer by EXPERIENCE. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My PERSONAL favorite is CrossJoin because it ALLOWS me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t ROLL up to a natural ancestor.” Indeed, CrossJoin has easily been my BREAD and butter.

This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

The following table lists the business INTELLIGENCE enhancements that are available in Microsoft SQL SERVER 2005 Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a DATA SOURCE and for which no schema has been generated.

The following table lists the business intelligence enhancements that are available in Microsoft SQL Server 2005 Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

Processing is a critical and resource intensive operation in the data warehouse LIFECYCLE and NEEDS to be carefully optimized and executed. ANALYSIS SERVICES 2005 offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

The Enable/Disable Writeback DIALOG box ENABLES or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to AVOID unanticipated data loss.

The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.

We can CREATE AGGREGATIONS for faster MDX statements using Aggregation Wizard or through UBO – Usage Based Optimizations. Always, prefer UBO METHOD in real time PERFORMANCE TROUBLESHOOTING.

We can create aggregations for faster MDX statements using Aggregation Wizard or through UBO – Usage Based Optimizations. Always, prefer UBO method in real time performance troubleshooting.

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.

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.

Under normal circumstances, each LEVEL in a hierarchy in Microsoft SQL Server 2005 Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at LEAST one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. EXPANDING through every level for every drilldown path is then UNNECESSARILY complicated.

Under normal circumstances, each level in a hierarchy in Microsoft SQL Server 2005 Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.

Nonempty BEHAVIOR is important property for ratio calculations. If the denominator is EMPTY, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more MEASURES for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be EVALUATED before the calculation expression is evaluated. If each selected measure is empty, then the expression is also TREATED as empty and no error is returned.

Nonempty behavior is important property for ratio calculations. If the denominator is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then the expression is also treated as empty and no error is returned.

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.

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.

The difference between a derived measure and a CALCULATED measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the CUBE. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary CRITERION for CHOOSING between a derived measure and a calculated measure is not efficiency, but accuracy.

The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

• CUBES are multidimensional models that store data from one or more sources.
• Cubes can also store aggregations.
• SSAS Cubes are created using the Cube Wizard.
• We also build DIMENSIONS when creating Cubes.
• Cubes can see only the DSV( LOGICAL View).

• Structured Query Language (SQL)
• Multidimensional Expressions (MDX) - an industry standard query language orientated towards analysis.
• Data Mining Extensions (DMX) - an industry standard query language oriented toward data mining.
• Analysis SERVICES SCRIPTING Language (ASSL) - used to MANAGE Analysis Services database OBJECTS.

A database is called a OLAP Database if the database satisfies the FASMI rules :

• 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.

A database is called a OLAP Database if the database satisfies the FASMI rules :

• An OLAP ENGINE is used for enabling FAST ad hoc queries by end users. A user can INTERACTIVELY explore data by drilling, slicing or pivoting.
• Drilling refers to the process of exploring details of the data.
• Slicing refers to the process of placing data in rows and columns.
• Pivoting refers to switching categories of data between rows and columns.
• In OLAP, we will be using what are CALLED as Dimensional DATABASES.

• 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.

• Analysis Services is the only component in SQL Server using which we can PERFORM Analysis and Forecast operations.
• SSAS is very easy to use and interactive.
• Faster Analysis and Troubleshooting.
• Ability to create and manage DATA warehouses.
• APPLY efficient Security Principles.

The role of a Unified Dimensional Model (UDM) is to provide a bridge between the USER and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and IMPROVED performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the CONSTRUCTION of the UDM, additional benefits accrue from the richness of metadata that the model can provide.
The UDM provides the FOLLOWING benefits:

• Allows the user model to be greatly enriched.
• Provides high performance queries supporting interactive analysis, even over huge data volumes.
• Allows business rules to be captured in the model to SUPPORT richer analysis.

The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the construction of the UDM, additional benefits accrue from the richness of metadata that the model can provide.
The UDM provides the following benefits:

Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. 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. 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-standard data mining algorithms.
Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence ALSO including reporting features.
Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.
Some of the key features are:

• EASE of use with a lot of wizards and designers.
• Flexible data model creation and management
• Scalable architecture to handle OLAP
• Provides integration of administration tools, data sources, security, caching, and reporting etc.
• Provides extensive support for custom applications.

Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. 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. 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-standard data mining algorithms.
Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence also including reporting features.
Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.
Some of the key features are:

• SQLSERVER:SSIS Service
• SSIS Package Instances
• SQLServer:SSIS Pipeline
• BLOB bytes read
• BLOB bytes written
• BLOB files in USE
• Buffer memory
• BUFFERS in use
• Buffers spooled
• Flat buffer memory
• Flat buffers in use
• PRIVATE buffer memory
• Private buffers in use
• Rows read
• Rows written.

Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The SCRIPTS in the Script task and the Script component can also use variables. The PRECEDENCE constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports TWO types of variables: user-defined variables and SYSTEM variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

BUILDing SSIS Projects provides a Deployment Manifest FILE. We need to run the manifest file and decide whether to DEPLOY this onto File SYSTEM or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQ Server.

BUILDing SSIS Projects provides a Deployment Manifest File. We need to run the manifest file and decide whether to deploy this onto File System or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQ Server.

SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a DIVERSE set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text FILES, SQL SERVER Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are CONFIGURED at the package level. Each task or container in a package can log information to any package log. The TASKS and containers in a package can be enabled for logging even if the package itself is not.

SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

• AGGEGATE - It applies aggregate functions to Record Sets to produce new output records from aggregated values.
• AUDIT - Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc.
• CHARACTER MAP - Performs SQL Server level makes string data changes such as changing data from LOWER case to upper case.
• CONDITIONAL SPLIT – Separates available input into separate output pipelines based on Boolean Expressions CONFIGURED for each output.
• COPY COLUMN - Add a copy of column to the output we can later transform the copy keeping the ORIGINAL for auditing.
• DATA CONVERSION - Converts columns data types from one to another type. It stands for Explicit Column Conversion.
• DATA MINING QUERY – Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
• DERIVED COLUMN - Create a new (computed) column from given expressions.
• EXPORT COLUMN – Used to export a Image specific column from the database to a flat file.
• FUZZY GROUPING – Used for data cleansing by finding rows that are likely duplicates.
• FUZZY LOOKUP - Used for Pattern Matching and Ranking based on fuzzy logic.
• IMPORT COLUMN - Reads image specific column from database onto a flat file.
• LOOKUP - Performs the lookup (searching) of a given reference object set against a data source. It is used for EXACT matches only.
• MERGE - Merges two sorted data sets into a single data set into a single data flow.
• MERGE JOIN - Merges two data sets into a single dataset using a join junction.
• MULTI CAST - Sends a copy of supplied Data Source onto multiple Destinations.
• ROW COUNT - Stores the resulting row count from the data flow / transformation into a variable.
• ROW SAMPLING - Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
• UNION ALL - Merge multiple data sets into a single dataset.
• PIVOT – Used for Normalization of data sources to reduce analomolies by converting rows into columns
• UNPIVOT – Used for demoralizing the data structure by converts columns into rows incase of building Data Warehouses.

• 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.