Ashish Agarwal

SQL Server Integration Services SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. A simpler way to think of SSIS is that it’s the solution for automating data movements. SSIS provides a way to build packages made up of tasks that can move data around from place to place and alter it on the way. Creating a Package The Import and Export Wizard is easy to use, but it only taps a small part of the functionality of SSIS. To really appreciate the full power of SSIS, you’ll need to use BIDS to build an SSIS package. A package is a collection of SSIS objects including:- • Connections to data sources. • Data flows, which include the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. • A Control flow, which includes t...

Understanding Analysis Services The basic idea of OLAP is fairly simple. Let’s think about that book ordering data for a moment. Suppose you want to know how many people ordered a particular book during each month of the year. You could write a fairly simple query to get the information you want. The catch is that it might take a long time for SQL Server to churn through that many rows of data. And what if the data was not all in a single SQL Server table, but scattered around in various databases throughout your organization? The customer info, for example, might be in an Oracle database, and supplier information in a legacy xBase database. SQL Server can handle distributed heterogeneous queries, but they’re slower. What if, after seeing the monthly numbers, you wanted to drill down to weekly or daily numbers? That would be even more time-consuming and require writing even more queries. This is where OLAP comes in. The basic idea is to trade off increased storage space n...

Using Report Designer Reporting Services includes two tools for creating reports:- • Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface. • Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model before end users can create reports. Report Designer runs inside the Business Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can also use the design tools to modify a report created with the wizard. Using the Report Wizard The easiest way to create a report in Report Designer is to use the Report Wizard...

The Reporting Services Architecture Reporting Services has a quite a few components that work together seamlessly to provide a complete reporting solution. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including URL, SOAP and WMI interfaces. Figure 17-1 shows a simplified diagram of the main Reporting Services components that we’ll be using in this chapter. • Report Server is the core engine that drives Reporting Services. • Report Manager is a Web-based administrative interface for Reporting Services. • Report Designer is a developer tool for building complex reports. • Report Builder is a simplified end-user tool for building reports. • The Report Server database stores report definitions. Reports themselves can   make use of data from many different data sources.

To create and attach a schedule to a job 1.     In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. 2.     Expand SQL Server Agent , expand Jobs , right-click the job you want to schedule, and click Properties . 3.     Select the Schedules page, and then click New . 4.     In the Name box, type a name for the new schedule. 5.     Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation. 6.     For Schedule Type , select one of the following: ·          Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started. ·          Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition. ·     ...

The Methods of deployment Of the Analysis Service Projects There are mainly 5 main methods of the deployment of a analysis project made on the development server – (They are listed according to the advantages) Deployment with AMO Automation. Deployment with XMLA scripts. Deployment Wizard. Deployment with the synchronize wizard. Deployment with Backup/Store. Using the Analysis Services Deployment Wizard – SSAS includes a tool called the Deployment Wizard which will do exactly what you need.  The Deployment Wizard uses the artifacts created by your SSAS project and can perform the deployment and/or generate an XMLA script that can be used to perform the deployment at a later time.  XMLA stands for XML for Analysis Services and it is the native protocol used by SSAS.  All commands sent to SSAS are in the XMLA format.  An XMLA script is an XML file.  The ability to generate the XMLA script allows the developer to p...