Clustering Support SQL Server 2005 provides for improved

Clustering Support SQL Server 2005 provides for improved availability with support for failover clustering, enhanced multi-instance support, and support for backing up and restoring database information. SQL Server running on Windows 2003 Server can support eight clustered servers all sharing in the performance of database tasks required of it. Users will have access to a database during a restore operation, within certain limits. This helps get you up and running faster after problems. Key Performance Indicators The key relevant measures of the business are what are known as key performance indicators or KPIs. After a company defines its key performance measures, reporting them on a daily or even real-time basis helps monitor business performance. Key performance indicators (KPIs) provide businesses with the capability to define graphic, customizable business metrics to help generate and track key corporate benchmarks. Scalability and Performance Features such as parallel partition processing, creation of remote relational online analytical processing (ROLAP) or hybrid online analytical processing (HOLAP) partitions, distributed partitioned cubes, persisted calculations, and proactive caching will greatly improve the scalability and performance of Analysis Services in SQL Server 2005. One-Click Cube When creating a cube in a data warehouse project, the Cube wizard includes an option to enable One-Click Cube detection and suggestions. This option examines the relationships in a data source view and makes suggestions for fact tables, dimension tables, and measures. Proactive Caching Proactive caching combines MOLAP class query performance with real-time data analysis and eliminates the need to maintain OLAP stores. The Proactive Chapter 22: Ten Ways to Hook Into the Microsoft BI Platform 377

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 22 Ten Ways to Hook Into the

your information and allow users to work with information in cubes where multiple dimensions of the data can be manipulated to see trends and deail in the underlying detail data. Integration Services DTS (Data Transformation Services) was the integration tool of SQL Server 2000. It consists of a set of graphical tools and programmable objects that you can use to extract, transform, and load (ETL) data from disparate sources and move it to single or multiple destinations. SQL Server 2005 introduces a complete redesign of DTS that is called Integration Services. Integration Services is loaded with new functionality to better control transformation logic. It automates many previously difficult tasks like handling slowly changing dimension transforms. It greatly adds to developer productivity and database scalability to provide a truly comprehensive platform for doing the basics of data integration: Extract, transform, and load operations. Data Mining Four new data mining algorithms are part of SQL Server 2005 Analytical Services. These algorithms assist developers in constructing complex analytical models and integrating them into business operations. Data mining is helpful in predicting what customers are most likely to buy, what products tend to be purchased together, and what the demand for a product is likely to be next month, and for segmenting customers in logical groups that represent various degrees of loyalty. The algorithms include decision trees, sequence clustering, time series, and neural nets. There are also new wizards and improved tools that will make data mining easier for businesses of any size. Reporting Services SQL Server 2005 Reporting Services is a service-based platform for creating, delivering, and managing traditional and interactive reports. Reports created with Reporting Services can be used for enterprise reporting on the Web, reporting to customers and partners through a secure Internet connection. Running a report is easy and intuitive for the typical business user so that self-service reporting can be achieved with little training. Ad hoc reports can be created using the Report Builder. Finally, developers can create complex and interactive reports easily to enhance the business user experience. 376 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 22 Ten Ways to Hook Into the

Chapter 22 Ten Ways to Hook Into the Microsoft BI Platform In This Chapter Analysis Services Integration Services Data mining Reporting Services Clustering support Key performance indicators Scalability and performance One-Click Cube Proactive caching Integration with Microsoft Office SQL Server 2005 offers some tremendous capabilitities in terms of fulfilling a robust set of data warehousing and business intelligence needs within one database platform. SQL Server Reporting Services is just one of the many capabilities that SQL Server offers to streamline business intelligence design and development. This chapter talks about the top key SQL Server 2005 BI platform capabilities that, together with Reporting Services, make SQL Server 2005 an exciting platform for business intelligence. Analysis Services With SQL Server 2005, Analysis Services moves into the realm of real-time analytics. From scalability enhancements to deep integration with Microsoft Office, SQL Server 2005 helps extend business intelligence to every level of your business. Analysis Services offer ways to outline drill-down levels within

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Instead of using parameters, base your security decisions

Execute reports from cached/snapshot data as opposed to live data whenever possible. Limit non-essential background processing to off-peak hours in order to avoid contention with online users. If you load your report server up with 4GB memory, remember to set the /3GB switch in C:boot.ini so application processes can make use of it. If a single server can t handle the workload, consider locating the Reporting Services catalog on a remote SQL Server instance as your first step toward increasing system capacity. If one report server configured with a remote catalog still doesn t adequately support your workload, consider increasing the available resources on the system hosting your report server (scale-up), or setting up a clustered Web farm of report servers (scale-out). 374 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Instead of using parameters, base your security decisions

Instead of using parameters, base your security decisions on the User!UserID parameter. UserID is populated as the result of the Windows authentication mechanism, so it is inherently more reliable than parameters. Use the expression: User!UserID Use the value of this global parameter (hidden from the user) to pass to the query in order to filter the information specific for that user. Measuring and Improving Report Performance In Chapter 14, I discuss techniques and provide suggestions for optimizing report performance. You should keep a few issues in mind while you are looking for ways to improve report performance on your system: The bulk of report execution time is spent executing queries and retrieving results. If you re using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries. Database Tuning Advisor can suggest better indexes for your database. The Report Server ExecutionLog table contains data on report performance. If you want a quick look at how long it took to execute certain reports, and where the bulk of the time was spent, TimeDataRetrieval contains the number of milliseconds spent getting data from the report s data source(s). Use the following syntax in Query Analyzer: ReportServer Select * from ExecutionLog with (nolock) order by TimeStart DESC. Make sure to include nolock. The ExecutionLog table is used by the RS runtime and locking it can severely degrade your server s performance. If you don t need data in your report, don t retrieve it. Leveraging database operations such as filtering, grouping, and aggregates can reduce the amount of data that is processed in the report, improving performance. Keep your reports modest in size and complexity. Do users really want to look at a 1,000-page report? If performance is extremely bad even for single users, check the Application Restarts counter in the ASP.NET category. Some antivirus software is known to touch configuration files. For more information, search http://support.microsoft.com/ for articles relating to antivirus and ASP.NET. If performance is slow on the first Web service access after there have not been any accesses for a certain time period, disable the idle timeout on the Performance tab in the Application Pool in IIS Manager. Chapter 21: Ten Cool Tricks with Reporting Services 373

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Dynamically Creating a Report Query You actually write

Suppressing Objects or Formulas in an Excel Rendering of a Report When you don t want to see a document map or formula in an Excel rendering of your report, you can control the properties of the rendering by using the devise information settings when specifying the report using the URL access method. For example, to suppress a document map from a report in Excel, use the URL link as follows: http://servername/reportserver?/SampleReports/Sales Order Detail&rs:Command=Render&rc:Format=HTML4.0 &rcOmitDocumentMap=True When you want only the data values and no formulas generated in Excel for the entire report, you can use the Device Information Setting of OmitFormulas to suppress formula generation as in the following expression: http://servername/reportserver?/SampleReports/Employee Sales Summary&EmployeeID=38&rs:Command= Render&rs:Format=EXCEL&rc:OmitFormulas=true Securing Reports with a UserID Parameter Suppose that you want to integrate a report containing HR information in your application and it requires a value for Employee ID to specify the employee who is viewing the page. Let s assume that the data in the HR database is keyed by the EmployeeID field. One solution to this requirement is to have your application determine the value of EmployeeID for the current user, and you could pass EmployeeID as a report parameter that ll drive its queries. This seemingly natural solution is less than optimal because your users could change the parameter and pass in another EmployeeID, which leads to unwanted information disclosure. You can try to hide the parameter so that it doesn t show up in the Report Viewer toolbar, or not show the toolbar at all. This may seem like a solution, however, parameter values will still show up in the URLs generated in the report. User entered report parameters offer opportunities to hack data from a report for savvy users. Even if you use a custom encryption, those encryptions seldom are strong enough to withstand a serious hacker. Because of this, a general rule to follow is: Never use report parameters to implement security. 372 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Dynamically Creating a Report Query You actually write

Dynamically Creating a Report Query You actually write code to dynamically create a report with parameters. The way you do this is to go to the Data tab in the Report Designer for the report and select the Generic Query Designer. Then enter a code reference like: =Code.GetQuery(Parameters) Then click on the Report Properties menu item on the Report main menu to open the Report Properties dialog box. On the Code tab, define a function like: Public Function GetQuery(repParameters As Parameters) As String End Function The logic in this function can build a query dynamically with string functions. Note that you can refer to parameters in this function with an expression like: repParameters!paramname.Value Changing the Report Manager Folder Icon to a Corporate Logo You may want to customize the icon at the top left of the Report Manager home page and put your corporate logo in its place. If you browse the source of the Report Manager, home page you will see that the file reference for the folder icon is: /Reports/images/48folderopen.jpg Searching around in the program manager, you can locate this file in the directory: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportManagerimages If you open this file, you ll find that it s a 48 x 48 .jpg image. Therefore, if you can make a similar resolution .jpg of your corporate logo and save this new logo file as 48folderopen.jpg, the next time you refresh your Report Manager, you will see your corporate logo in the top-left corner of the Web page. Chapter 21: Ten Cool Tricks with Reporting Services 371

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

After you create the two parameters for your

In the next case, consider when your query criteria is an integer. Assume that the report parameter is Year. Suppose that your dataset expression in the Query Designer, you specify a criteria like: >= @MinYear and < = @MaxYear Define a query parameter expression for MaxYear as: =iif(Parameters!Year.Value=0,10000,Parameters!Year.Value) Define a query parameter expression for MinYear as: =iif(Parameters!Year.Value=0,0,Parameters!Year.Value) In this case, the All parameter should be mapped to the value of 0. When All is selected for the parameter value, then the like search criteria expressions become: >= 0 and < = 10000 This will return rows containing any value (other than NULL) in the year field. Using the wildcard method above can be a security risk and could be vulnerable to SQL injection attacks. An alternative way of accomplishing this is to define the following parameter query for the parameter @Year: SELECT All UNION ALL SELECT Year FROM DateDim Then your dataset query expression would be: SELECT YearName, Col2, Col3 FROM FactTable WHERE 1 = CASE WHEN @Year = All THEN 1 WHEN @Year = YearName THEN 1 ELSE 0 END Using this dynamic filter is a very powerful form of using parameters. 370 Part VII: The Part of Tens Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

After you create the two parameters for your

After you create the two parameters for your report, you need to add two expressions to the table control in your report. To do this, follow these steps: 1. Click the top-left part of the table to select it. Right-click the table object to display a pop-up menu of options. 2. Select Properties. The Table Properties dialog box appears. 3. Select the Sorting tab and enter the following expression for an ascending sort: =iif(Parameters!Direction.Value= Ascending , Fields(Parameters!SortBy.Value).Value,0) 4. Add the following expression for a descending sort: =iif(Parameters!Direction.Value= Descending , Fields(Parameters!SortBy.Value).Value,0) When you preview the report, you will see two parameters at the top of the report. Enter a value of the column in the Sortby parameter. Simulating the All Parameter Value Situations exist when you want to see on a parameterized report a parameter choice indicating that all values are selected. To do this, when you define the report parameter in the Report Parameter dialog box, define a valid values list of the parameter including the value All. This means that you will see the All value in the list of values for the parameter. In the first case, consider when the parameter in question is a text field like Department. In your dataset expression in the Query Designer, you need to specify in the query the following expression: WHERE department like @department In this case, the All parameter should be mapped to the value of %, which is a wildcard. When the All value is specified, then the like search criteria becomes: WHERE department like % This expression returns rows containing anything in the Department field. Chapter 21: Ten Cool Tricks with Reporting Services 369

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 21 Ten Cool Tricks with Reporting Services

In Reporting Services, to simulate the stripe effect when printing, you need to do conditional formatting on the contents of table cells. To change the BackgroundColor to be an expression that alternates between green and white, use this expression for the BackgroundColor property for all cells in the report line: =iif(RowNumber(Nothing) Mod 2, Green , White ) Controlling Page Breaks If you want to better control page breaks within a report, you can do so with expressions on groups in a matrix or table. If you know that you would like to do a page break on a specific row count, you can specify that in the group expression. That way, you can introduce a hidden page break grouping. Just right-click a row on the table grid and select Edit Group from the pop-up menu. In the Expression Edit dialog box that appears, enter the following expression to insert a page break after 20 report lines: =Ceiling(RowNumber(Nothing)/20) Running Totals In some reporting situations, you may want to express the cumulative total for a report column as it changes row by row. In order to do this, you need to enter an expression for the value in the table cell. Let s assume you want to specify a running total for a field called SalesAmt. The following aggregate function will provide you with running totals: =RunningValue(Fields!SalesAmt.Value, Sum, Nothing) Simulating End-User Sorting If you want to use a parameter to provide a dynamic sorting capability, you need to create two report parameters called Sortby and Direction. The values of the Sortby report parameter should be set to the names of the fields by which you need to have the users sort. The values of the Direction parameter should be set to values Ascending, Descending, and None. Then enter the above information into the Report Parameters dialog box that appears. 368 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services