1. In this SSIS 2012 demo i will explain how to create an SSIS integration project in SQL Server Integration services project in BIDS 2012 or 2008
2. SSIS 2012 or SSIS 2008 is a powerful tool that is used for Data Migration and Data Integration and is an important part of Microsoft Business Intelligence (MSBI)
3. The tools that we have used are SQL Server 2008 or 2012 ,Business intelligence development studio 2008 or 2012

1. In this i will explain the importance of bulk insert task in ssis 2008 or ssis 2012 and i will also give some examples
2. The Bulk Insert task can only transfer data from a text file into a SQL Server table. 
3. Bulk Insert task supports only Object Linking and Embedding, Database (OLE DB) connections for the destination database.
4. While the Data Flow task can provide this capability, the Bulk Insert task is the most efficient method for this type of transfer, because it cannot perform transformations or error logging on the data while it is moving from the source file to the table.
5. If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. 
6. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task.
7.  The Bulk Insert task is particularly useful in situations where data needs to be retrieved from a system that cannot be connected to SSIS. Text files can act as a great intermediary data source and the Bulk Insert task will minimise the time required to import the data into a SQL Server table.

1. This category includes a Name and Description property.

1.  This category includes a File property to define the source text file connection. 
2. The connection category also contains a Connection and Destination Table property to define the destination SQL Server database and table. 
3.  The text file RowDelimiter and ColumnDelimiter property can be used to define the text file format or if the Format property is set to Use File, a FormatFile can be used to define custom mappings between the text file and SQL Server table. 
4. If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task. 
5. The Bulk Insert task supports both XML and non-XML format files.

1.  On the Options page, the Codepage and DataFileType property is used to define the type of text file. 
2. The BatchSize determine how many rows are inserted into the table as a batch. 
3. A setting of zero means all rows in the text file are inserted as one batch.
4. If a batch size is set, each batch represents a transaction that is committed when the batch finishes running. 
5. The FirstRow and LastRow properties determine the starting and ending row of the data in the text file. 
6. You can also use the MaxErrors property to determine the number of errors that are allowed before the Bulk Insert task fails.

Data Analysis Transformations in SSIS 2012 2008 

1. Pivot Transformation
2. Unpivot Transformation
3. Data Query Transformation

1. The Pivot transformation takes data from a normalised result set and presents the data in a cross tabulated or denormalised structure. 
2. For example, a normalised Orders data set that lists customer name, product and quantity purchased typically has multiple rows for any customer who purchased multiple products, with each row for that customer showing order details for a different product. 
3. By pivoting the data set on the product column, the Pivot transformation can output a data set with a single row per customer. 
4. That single row lists all the purchases by the customer, with the product names shown as column names, and the quantity shown as a value in the product column.
5.  Because not every customer purchases every product, many columns may contain null values.

• Component Properties tab. The Component Properties tab allows you to specify a Name and Description for the OLE DB Command Task. You can also specify a locale with the LocaleID property and whether the data flow is validated at run time or design time by using the ValidateExternalMetadata property.
• Input Columns tab. The Input Columns tab consists of the Available Input Columns property that enables you to select the columns that the Pivot transformation operation will affect. When a column is selected, it appears in the Input Columns list. The Output alias allows you to define the name of the output column. The Usage Type property defines if the data imported is READONLY data or READWRITE data.
• Input and Output Properties tab. The Input and Output Properties tab allows you to set the properties of the input and the output columns. The most important property here is the PivotUsage property. This determines what role the input column will play in creating the pivot table and can be configured with the following values:
• 0. The column is not pivoted, and the values are passed through to the transformation output.
• 1. The column is part of the set key that identifies one or more rows as part of one set.
• 2. The column is a pivot column. At least one column is created from each column value. This data must be sorted input column.
• 3. The values from this column are placed in columns that are created because of the pivot.

Unpivot transformation

1. The Unpivot transformation takes data from a denormalised or cross-tabulated result set and presents the data in a normalised structure. 
2. The Unpivot transformation can be configured with the following properties.
3. At the bottom of the Unpivot Transformation Editor dialog box is the Pivot key value column name. 
4. Here, you define a column heading for the column that will hold the pivoted data that is converted into normalised data such as Products or Fruits.
5. The Available Input Columns property enables you to select the input columns that the Unpivot transformation operation turns into rows. 
6. When a column is selected, it appears in the Input Columns list. Any columns that are not selected are passed through to the data flow output. The Destination Column allows you to define the name of the destination column in the normalised output.
7. In the Unpivot scenario, multiple input columns are usually mapped to one destination column. 
8. For example, the Available Input Columns may consist of column headings such as Apples, Pears and Peaches.
9.  All of these input columns are mapped to a destination column named Fruits that may be defined by the Pivot key value column name property.
10. The Pivot Key value property specifies the value that is used in the rows in the normalised result set and, by default, uses the same name as the input column but can be changed.

Fact Tables and Dimension Tables in Microsoft BI Datawarehouse

1. A data warehouse is a relational database that centrally stores a consistent view of the data from transactional systems in a denormalised format. 
2. This makes the retrieval of the data more efficient than it would be from a transactional system.
3. A data warehouse will consist of one or more data marts. 
4. A data mart typically represents an area of the business such as sales or finance departments. Within each data mart, there are two types of tables created
5. fact tables and dimension tables

Dimension Tables

1. Dimension tables contain columns that hold information that represents a business entity. 
2. Examples of dimension tables can include time, region and customers. The columns will contain information that is specific to the entity. In the case of the customers dimension table, columns can include Firstname and Lastname. 
3. You can define as many columns as you want to precisely define the customer entity. However, placing unnecessary columns increases the volume and time it would take to populate the dimension table with data by using SSIS.
4. Dimension tables should consist of two columns that are important in loading the data warehouse. The first column is typically a dimension key known as a surrogate key. 
5. This is usually a primary key column that uses an identity constraint to automatically populate the column value. It is also used to provide a relationship to the fact table. The second column is referred to as an application key column. This column is used to hold the value of the record in the original transactional system.
6. You may also wish to add columns to the dimension table that can be used to track changes to the records within the dimension table. As information within the table changes, you can add a ModifiedDate column that can be used to track the date when changes occurred to a record. To keep historical records of changed data, you could include StartDate and EndDate columns that help you to determine the duration that a record was in a particular state.
7. Dimension tables contain columns that hold information that represents a business entity. You can define many dimension tables. 
8. An example of a business entity that is represented as a dimension table could be customers. The columns will contain information that is specific to that entity. In the case of the customers dimension table, columns can include Firstname and Lastname. Dimension tables will also contain a primary key column that uniquely identifies the records in the dimension table. The primary key also defines a one-to-many relationship with an associated column or columns within a fact table.
9. Also note that dimension tables can be shared between different data marts. A common example of this is a Time dimension table. This could be used by different data marts to provide consistent information about time across multiple data marts. These types of dimension tables are known as conformed dimensions. In order to manage the movement of data from source systems to the data warehouse, staging tables may also be created in the data warehouse. This allows tables to act as intermediary tables for cleansing data before loading the cleansed data into the respective dimension tables.

Fact Tables

1. The fact table contains information about historical transactions and typically represents this information as integer data. 
2. For example, you can have three dimension tables named Customers, Region and Time. Each has a primary key column that relates to the fact table. In this example, Customer 6231 in the fact table is equal to Francois Ajenstat in the dimension table. Region 4 is equal to the value of West and the Time value of 3423 is equal to the 18th of August, 1975 in the Time dimension table. The additional columns will contain measures about the transaction such as order quantity and sales amount. Using integer data to represent historical transactions allow SQL Server Analysis Services to process the data much more efficiently
3. The fact table is at the heart of the data mart and typically consists of three groups of columns. The first is a primary key column to maintain the integrity of the fact table itself. The second group of columns are known as foreign keys.
4. Each of these columns relate to a dimension table primary key to provide a relationship between the fact table and the contextual information provided by the dimension table. Imagine that the image represents a Sales data mart, and that the three smaller tables represent dimension tables called CustomerDim, TimeDim and RegionDim. 
5. The fact table, which would be the big table in the image, would contain three foreign key columns that would relate directly to each dimension table.
6. The fact table also contains a third group of columns known as measures. This group of columns hold business metrics such as sales units, sales amounts and cost amounts. As a result, the fact table ideally consists of business information represented by integer values.
7. On occasions, a fourth group of columns may be seen within the fact table. This may be one or more columns that are referred to as degenerative dimensions. A degenerative dimension is information about a business entity that is stored within the fact table itself rather than held within a separate dimension table. Using the example of the CustomerDim, TimeDim and RegionDim, these three dimension tables provide context information about customers, time and regions. A central fact table holds foreign key references to the dimension tables and includes measures such as SalesUnits and OrderQuantity. We can include a SalesOrderNumber for each record of a sale. However, rather than creating a separate dimension table that would require a join to the dimension table just to return one piece of contextual information, it is more efficient to store this piece of information within the fact table itself.
8. Fact tables are created like any table that is created in SQL Server. You also define the primary and foreign key relationships within the database engine. Therefore, you are limited to the 8-KB limit on the amount of information that is stored in a row within a table

SSAS 2012 2008 Introduction to SQL Server Analysis Services for beginners and scenarios to use SSAS 

1. SSAS 2012 is the technology that is provided by SQL Server to create cubes. 
2. Organizations use cubes to manage large volumes of data so that complex queries can be retrieved in a more efficient manner than if the queries were to be executed against a relational database. 
3. With cubes, there is also the ability to preaggregate data and store them in the cube for faster retrieval of summary information.
4. The most underused aspect of SSAS is data mining that enables a business to perform trend analysis and identify relationships between data that may not be immediately apparent to end users when looking at the data. 
5. Analysis Services is a fantastic tool that can host the data in a central point as information
6. SQL Server Analysis Services provides the ability to efficiently query multidimensional data. It also provides the capability to use data mining to extrapolate trends and patterns within data. 
7. There are core components that you should be aware of to create a cube.
8. At its core, data sources must be defined to provide connection information to the source data for the cube. You can then define data source views to define the specific tables on which the cube is based. This will typically involve dimension tables and fact tables from a data warehouse.
9. You can then define dimensions within the cube against dimension tables within the data warehouse. The dimensions will provide contextual information and act as the axis for the cube. For improved readability, you can also define hierarchies to allow the user to drill down into the dimension data.
10. On completing the creation of the dimensions, we can then run the cube wizard to create the cube. The wizard allows you to create a cube using existing dimensions or from data from a table. 
11. You can also create an empty cube if you do not have the data available. Once the cube is created, you can configure cube partitions. This enables you to spread the data over multiple physical disks to improve the performance of cube processing and querying data in the cube. 
12. SQL Server Analysis Services also provides additional OLAP objects to extend the functionality of the cube, including calculated members, KPIs, actions and translations.
13. For data mining, you can use new or existing data sources and data source views to point to the source data. The source data for data mining can be an existing cube or data from a table or a view. 
14. You can then create a data mining structure that integrates the source data with a data mining algorithm, known as a data mining model in order to find patterns in the data using the data mining model. On completing this, you have the ability to view and validate the results that are returned back to the data mining viewer.

SSAS 2012 2008 SQL Server Analysis Services Components

1. There are core components that are used to create an Analysis Services solution within Business Intelligence Development Studio. 
2. Understanding these components will help you to start multidimensional and data mining solutions within your BI solution.

1. Data sources
2. Data source views
3. Cubes
4. Dimensions
5. Measures
6. Slice and dice
7. Data mining

1. Data sources provide the connection information for the source system on which the Analysis Services solution is based.
2. Typical configuration for data sources involve specifying the name of the server on which the data is stored. You also specify the credentials required to connect to the server and the name of the database to access. You should also consider that additional settings may be required between different data sources.
3. The data sources that are supported by SSAS  2012 2008 include:

• SQL Server 7.0 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
• SQL Server 2000 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
• SQL Server 2005 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
• SQL Server 2008 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
• Oracle 9.0 using the Microsoft OLE DB Provider for Oracle or the .NET native OLE DB provider (x86 only).
• IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64)—only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition.
• Access with Microsoft Jet 4.0 OLE DB provider (x86 only).
• Teradata v2R6 with OLE DB 1.3 provider from NCR (x86 only).
• Teradata managed provider (x86, x64 and ia64).  

Data source views in SSAS 2012

1. Data source views are objects based on a data source that provide an abstraction of a subset of tables, columns and relationships from the data source that you require as part of your Analysis Services solution.
2. This abstraction layer holds metadata about the objects and allows you to create an Analysis Services solution without a permanent connection to the data source.
3. To run the cube wizard or the data mining wizard, a data source view must be created.
4. Annotations can be added to provide friendly names for the tables and columns to improve the readability for the user.
5. The type of information that is defined within a data source view includes:

• A data source view name.
• A definition of any subset of the schema retrieved from one or more data sources including:
• Tables
• Columns
• Relationships

1. An OLAP database can hold one or more cubes. A cube is a fundamental unit of multidimensional analysis.
2. A cube consists of dimensions and measures. 
3. The dimensions form the axis of the cube and provide contextual information for the data that resides in the cube.
4. This information is known as measures.

1. Dimensions provide the contextual information for the data that resides in a cube. 
2. The dimensions in a cube typically map to a dimension table within the data warehouse.
3. As dimensions are used to describe a business entity, dimensions consist of attributes that provide specific information about the business entity. 
4. These attributes typically map to a specific column within the dimension table in the data warehouse.
5. To improve readability, hierarchies can be introduced within the dimension. 
6. For example, within a time dimension, a hierarchy named CalendarYear can be created containing three levels—Year, Quarter and Month. 
7. These levels allow the user to easily drill down into specific time periods

1. Measures provide the numeric information that resides inside the cube. 
2. They typically map to the measures columns in the fact table of a data warehouse.
3. As well as including the measures information from the fact table, SSAS also creates preaggregated versions of the measures.
4. For example, a fact table may consist of a measure named OrderQuantity. As well as including the detail information that is provided by the fact table, Analysis Service creates preaggregated OrderQuantity totals for yearly, quarterly or monthly totals. By creating this preaggregated data, queries for this information is retrieved more efficiently.

1. Slicing and dicing is the technique that can be used to interrogate the data from within a cube.
2. This technique is implemented in products such as Excel 2007/10/13. This allows you to use the pivot table feature to slice and dice the data to retrieve specific data.
3. Business Intelligence Development Studio provides a browser window that allows you to slice and dice the data to test the results that is returned before the cube is deployed into a production environment.
4. Furthermore, you can also employ this technique within Reporting Services as the basis for providing data for the reports that are deployed through SSRS.

1. Data mining is a feature in SSAS that enables you to extrapolate trends and patterns in the data.
2. You can create data mining structures to explore the data against algorithms that are provided
3. There is also a Data Mining Add-in for Excel 2007 2010 2013 that allows you to explore the results that are returned by a data mining structure. You can also use the add-in to create your own data structures

SSRS 2012 Introduction to SQL Server Reporting Services

Scenarios for Using SQL Server Reporting Services 2012

1. SQL Server Reporting Services 2008 is installed as part of the SQL Server setup. 
2. When Reporting Services is selected in the features selection screen, the Reporting Services Configuration screen provides three options that can be selected to determine how Reporting Services can be accessed. This can be useful if Reporting Services is to be used by internal users, external users or if you wish to integrate Reporting Services with Microsoft Office SharePoint Server.
3. By selecting the Install in Native Mode default configuration option, Reporting Services is installed with all of the default options and is operational once the installation is complete. 
4. This is an ideal choice if the Reporting Services solution is to be used by internal users only and you do not wish to change the default settings.
5. Users will access the reports by typing the URL in Internet Explorer that goes to http://ServerName/reports.
6. The installation allows you to integrate Reporting Services with Microsoft Office SharePoint Server. Assuming the Reporting Services pack is already installed on the SharePoint Server, Reporting Services will be installed in SharePoint integrated mode whereby the user will access the reports from the SharePoint site rather than the Reporting Services home page. Reporting Services can also use the features available to SharePoint Server as well.
7. For external access, it is best practice to select the Install, but do not configure the report server option in the Reporting Services Configuration screen. 
8. In this scenario, you will have to use Reporting Services Configuration Manager to manually set the values for the home page of the Report Server Web site, Report Manager Web site and determine how the Report Server databases are created. 
9. Furthermore, you will have to modify configuration files to secure the traffic that is sent between the Report Server and the external clients.

1. Reporting Services Configuration Manager
2. Report Designer
3. Report Builder
4. Report Manager
5. SQL Server Management Studio
6. Command prompt utilities
7. Use the Report Wizard

1. For post-installation configuration, the Reporting Services Configuration tool provides easier management, particularly for those moving from SQL Server 2000 Reporting Services.
2. Introduced in SQL Server 2005 2008 Reporting Services, the notable change to this version, apart from the look, is the fact that there is only one service account that needs to be configured now that the dependency on IIS is removed. 
3. Previous versions required a Windows service account and a Web service account to be configured. Below is a summary of options:

• Server Node. Provides the ability to stop and start the Reporting Services.
• Service Account. Provides the ability to change the security context under which the Reporting Services run.
• Web Service URL. Configures the URL access to the report server by creating a URL reservation. A URL reservation defines the virtual directory, IP address, TCP port and optionally a SSL certificate. HTTP.sys uses the URL reservation to direct users requests and allow access to the URL.
• Database. Provides the ability to change the location of the report server database and to change the credentials for connecting to the report server database.
• Report Manager. Creates an URL reservation to access the Report Manager Web site.
• E-mail Settings. Provides the ability to integrate with e-mail by defining a server address and an e-mail address that has the ability to send e-mail messages from the report server.
• Execution Account. Provides the ability to specify an account to use against report data sources that do not require credentials or to access remote servers.
• Encryption Keys. Allows the management of the keys that are used to encrypt the sensitive data that is stored in the Reporting Services database. There is the ability to back up and restore the encryption keys, change encryption keys and delete the encrypted content within the database.
• Scale-out Deployment. Provides the ability to manage multiple instances of Reporting Services that share the same ReportServer database such as a clustered instance. SQL Server 2005 Reporting Services referred to scale-out deployment as Initialization.

1. SSRS has introduced a new designer that allows business users to connect to data sources and produce richly formatted reports that provide highly visual information in a user-friendly interface. 
2. The report designer is found within Business Intelligence Development Studio and consists of the following core elements:

• Data pane. Provides the ability to define data sources and data sets. Items from the Data pane can be dragged onto the designer canvass.
• Toolbox. Provides the ability to add data regions and other report items to the designer canvass.
• Report canvass designer. Located in the centre of the Report Designer application, it allows you to develop the report in its entirety.
• Grouping pane. A grouping pane is located at the bottom of the Report Designer. This provides a quick way of managing groups of data in the canvas designer, without the need for entering into dialog boxes.
• Properties pane. A context sensitive pane that changes as items on the designer canvass are selected, providing rich formatting and report body options. The Properties pane provides a vast array of options than can vastly improve the visualisation of the reports

1. Report Builder is an application that empowers power users to create their own ad-hoc reports. 
2. Report Builder is available in two versions:

• Report Builder version 1 is a ClickOnce .NET application that allows the creation of unplanned reports. For power users to use the Report Builder version 1, a report model must be created that provides an abstraction layer to the underlying data in a data source. It is also a prerequisite to be able to use the Report Builder version 1. As a result, the report model has a separate project template in Business Intelligence Development Studio.
• Report Builder version 2 has now been introduced and is not constrained to the requirements of a report model. Report Builder version 2 has the same layout as the report designer that is available in Business Intelligence Development Studio. The application is encased within an Office 2007 application with ribbons to provide shortcut to common tasks. With Report Builder version 2, you can make use of data sources directly, providing a more flexible tool than its previous version.

1. Report Manager is the Web site that is used to host the reports that are deployed to the report server. 
2. End users will browse to a predefined URL that is the home page of Reporting Services.
3. From here, users can browse and export reports.
4. If they have the permission, they may also be able to subscribe to reports so that they can be delivered via e-mail.
5. Report Manager also acts as an administrative Web site for the reports.
6.  Report Server administrators have the same capabilities as the end users, however they can manage report execution, report history and security as well.

1. SQL Server Management Studio provides administrative capabilities for the report server. 
2. Within the Reporting Services server type, a report server administrator can create shared schedules, create system and item-level security roles and view the jobs that are running on the report server.
3. You can also manage the report server databases within the database engine, including backing up the database.

• RSConfig.exe. can be used to perform post installation tasks on the report server. The options available within RSConfig.exe are available within the Reporting Services Configuration Manager.
• RSKeymgmt.exe. allows you to back up and restore the report server encryption keys. This feature is also available within the Reporting Services Configuration Manager. Encryption keys are used to protect the sensitive data that is stored in the report server, such as data source credentials and passwords. This uses a symmetric key to encrypt and decrypt the sensitive data. The report server service is used to create and unlock the key. If you change the identity of the Report Server service or if you migrate the report server to a new computer, the private key of the Report Server service will no longer be able to unlock the symmetric key. To restore access to the symmetric key, the symmetric key must be re-encrypted by using the private key of the new Report Server service identity. Restoring the symmetric key is the process by which the re-encryption occurs.  

• RS Utility. The RS Utility is a script host that you can use to perform scripted operations. Use this tool to run Microsoft Visual Basic scripts that copy data between report server databases, publish reports, create items in a report server database and more.

1. Reporting Services 2008/2012 provides a wizard that will enable you to create simple reports. 
2. On completing the wizard, the report is readily available to be deployed to the report server.
3. Alternatively, you can use the report output from the wizard as the basis to edit the report to provide additional functionality that may not be available in the wizard.
4. The wizard will present you with options to define a data source and data set. You can select either the table or the matrix data region within the wizard. 
5. Therefore, if you wish to create a chart or gauge report, the Report Wizard is not appropriate.
6. Beyond these initial options, you then specify the columns to be included within the data region and define the formatting of the report that is similar to the Report Wizard in Microsoft Access.

1. Data sources - Data sets -Data regions Core SQL Server Reporting Services Components exist that are important for creating reports in SSRS 2012 and 2008. 
2. Here we have described this three main components for making better reports

1. Analysis Services allows you to create roles in both Business Intelligence Development Studio and SQL Server Management Studio.
2. Creating roles in SQL Server Management Studio means that the changes are performed on the deployed system and will take effect the next time the user connects to the Analysis Services instance. 
3. Note that the change that is made through SQL Server Management Studio is not reflected within the Business Intelligence Development Studio project. 
4. This results in the roles being overwritten or removed, should the deployment of the project define that the roles members are dropped and created from the project.
5. This is why using Business Intelligence Development Studio is a better alternative for creating roles if you need to keep a record of the roles existence within the project file. 
6. Roles that are created within Business Intelligence Development Studio need to be deployed but a record of the role will be kept in the project file.
7. To create roles, you must be a member of the Server role. 

To create a role in SQL Server Management Studio, perform the following steps:

1. Connect to an instance of Analysis Services.
2. In Object Explorer, expand the Analysis Services database, right-click Roles, and then click New Role.
3. In the New Role dialog box, in the Name box, type a name for the role.
4. To add members, click the Membership tab, and then click Add.

1. Open an Analysis Services solution in Business Intelligence Development Studio.
2. In Solution Explorer, expand the Analysis Services project, right-click Roles, and then click New Role.
3. In the New Role dialog box, in the Name box, type a name for the role.
4. To add members, click the Membership tab, and then click Add.

• Select the Analysis Services source instance and database from which to synchronise.
• Select storage locations for local partitions on the destination instance.
• Select storage locations for remote partitions on other destination instances.
• Select the level of security and membership information to copy from the source instance and database to the destination instance.
• Select whether to synchronise immediately or to save the XMLASynchronize command generated by the Synchronize Database Wizard to a script file for later synchronisation.