SQL Server MVP Deep Dives- P18
Mô tả tài liệu
SQL Server MVP Deep Dives- P18: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback.
Tóm tắt nội dung
Data store of data that serves as a basis for based on business and looking up surrogate keys from dimension between reporting systems and OLTP for all of their reporting to model the data warehouse based on the business, not on the specific 1 OLTP versus reporting data need to be designed to the initial loading of the data SQL Server keys, which are usually IDENTITY columns in SQL Server, intended both sets of data enables the You could stop after creating the data no longer have to know how to write T-SQL or be by static 3), Reporting Services, and Panorama, just to name a in SQL data store that is tuned for OLAP query that arise when trying to represent business data in a model. If there is a data warehouse project in or already at She began working with SQL Server in ver- sion 1.11, and Analysis Services, SSIS, DTS, and Server 2008 want to share some of the Reporting Services in to your (and my) Reporting Services is better this there are a few Reporting Services features that are still under 2008 Report Reporting these reports were heavily processed and formatted data dumps to and Visual Studio report tools, but after a has due to in Microsoft Reporting is Reporting get started with an overview of Reporting Services in terms that anyone can can serve as a powerful tool to create your reports, get them deployed to the Services server, or include them in your ASP, Windows Forms, or other is Reporting Services is just that: a service that runs (starting with SQL Server 2008) as a The SQL Server 2000 and 2005 versions of Reporting Services run as a web service hosted by Internet It’s best if Reporting Services runs on a dedicated system because in a report is deployed to a Reporting Services service instance, its RDL file is into a binary large object (BLOB) and stored in the SQL Server Reporting Ser- the Report Manager, Services, or by the right URL. Now let’s look more closely at the Report Services for reports as Report Server extracts the RDL by virtual directory path and name from the SQL Server Reporting Services report and passed to the Report Report Processor works like a language RDL is the script 1 SQL Server Services 644 CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting SQL Server report catalog tables and passing them, along with the report to the Data a report can and executes the report queries using any the SELECT used to fetch report data or to are imbedded in the report RDL. The Report Processor merges data from the named columns extracted from the query row- sets with the report elements and passes the results to the Report Rendering extension works like a printer but with a number of Reporting Services reports don’t render or Chrome In addition, in SQL Server 2008 the Report Ren- When an or a user requests a report, the RDL might require the user or the Report Processor to supply the values for one or more report are often used to focus the report data on a specific subset or set Services to render dialog boxes in the browser to capture When starting the Report Manager for the first time (or after a period Services compiled and to render the initial menu of available 2 Report as by the Report Visual Studio to create an RDL report As I discuss later, when you set up your report with the Visual Studio Business (BI) tools, you can specify as many query and report as needed, as eters are supplied, the user can click the View Report button to render some cases, the Report Processor might not need to re-run the Using Visual Studio to create an RDL Server Reporting Services is available with all editions of SQL Server including the SQL Server 2008 Express with Advanced Services with SQL Server is a version of Visual Studio used to create CLR and Reporting (among other toolkit to create, preview, and deploy a sample Studio above Express, you’ll be able to create and preview reports as Reporting Services, but I’ll talk about that option a bit SQL Server Compact editions do not support Reporting up Reporting Services is an important step but we don’t have enough room to ate Reporting Services all of the Reporting Services BI test, and deploy you can’t open Visual Studio 2005 BI report So follow along and I’ll walk you through the process of creating your own report. 3 Choose Report Server Project calling mine SQL Bible where the data for your report is means data can come from SQL Server, Analysis Services, SAP, 646 CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting 2000 Reporting Services) and I do not recommend using trusted for reports for many good If you plan to have several reports in the Visual Studio project that use the the data source name you use here might be shared by in other projects so check with your report DBA. 10 Click Next to open the Report Wizard’s Design the Query dialog box. data SQL to return the rowset (just one) used to populate or click the Query Builder to get the wizard to help build it. time you’re creating a report, not an updatable To make your report run faster and keep your DBA happy, be clause in your query to focus the rowset on only the data needed by the For my report, I chose a few columns from the After the query is done, click Next to proceed to the report layout dialog report query with 647Using Visual Studio to create an RDL report 13 Click Next to specify which column values are to be used to specify report in this means the query will fetch data that’s not 15 Choose the Table style (the color used as accents in the report) and click during SQL Server setup and chose to use SSL web traffic means you must change the Report Server path in the Choose the Location dialog box to point to the correct Reporting Services my case, I installed SQL Server 2008 on my BETAV1 system with the before you jump in and change the Report server name, I suggest you see how Reporting Services Setup the virtual directory name by Figure 4 the report page, group breaks, and detail CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting the SQL Server 2008 Reporting Services Reporting Services instance and choose the Web Service URL tab (on the If the Report Server virtual directory is properly it will Report Server Web Service URLs at the bottom of the dialog box as shown in SSL in place when Reporting Services was Reporting Services tool can also see my Services Reporting Services is running properly and to be taken to Server virtual you’ll need to change the Report Server name URL each and every time you create a report from the most cases you’ll leverage existing reports and report projects where the is where Visual Studio will deploy the that anyone with access to an explorer might be able to see your Services Virtual Directory (VD) and the Report Manager, so as you reports, others will be able to see your reports as you learn to use Reporting Report Manager and your DBA to prevent access to reports under and use rights to hide these from casual sure that people don’t run reports they that consume all of the available resources or those that are not after the wizard is the report, check the Preview 5 Reporting Services Service URLs the Visual Studio 2008 Report At this point you’re taken back to Visual Studio where your report layout is shown in the Design Window, the Report Data window is exposed, and Preview tab is are ready to execute the report and you don’t need to have Reporting Services up and running at this be necessary until you’re ready to deploy the the Visual Studio BI tools include a Report Designer that helps trained end users) build reports by columns from pre-built DataSet objects exposed in the Report Data the Visual Studio 2008 Report you’re familiar with SQL Server 2005 Reporting Services, you’ll see a number been replaced by the far more Report Data window, which is used to manage Built-in Fields new for Reporting Services 2008), Images, and the data sources created for the any number of data sources for a single and Matrix report as manage RichText data in your 6 Visual Studio BI project with the newly generated CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting you’re ready to see your report rendered and populated with data, click the Pre- view tab on the Report Design processes the RDL just as would be done by the Reporting Services the report just as the user would see it (well, report your report requires any query (or report) the report is rendered with When the Report finds imbedded in the report, it generates the UI ele- NOTE Report don’t initially have a preset default here in the Visual Studio BI tools or in the Report a Report Parameter by drilling into the Report Data window and a specific Report you define default values for all your report the Report not wait to capture before rendering the you develop your reports you’ll find an improved Chart report to enrich your reports that are exposed through the Reporting Services 7 The report 651Using the Visual Studio 2008 Report Report is directly tied to the amount of work the needs to perform to build and render the that focus the report query on specific, relevant rows your you’re happy with how the report behaves and appears to the user, you’re ready to deploy it to a specific Reporting Services when you ran the Report Wizard, but let’s assume that you did not use Wizard to create your report (and I don’t expect you will after you have first project property page as shown in figure data sources when deploying the into a the to make sure it correctly addresses your Reporting Ser- for report 652 CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting the Reporting Services instance is started and from your system, you’re ready to deploy the an report (.RDL) or the report project and choose is set correctly and the Reporting Services server instance is properly your report should deploy in a few the Report your report is deployed, you’re ready to see how it is rendered in a browser it from Report If you plan to use Report Manager, it assumes you have not I suggest using the SQL Server Reporting Services utility to your Report Manager URL is correctly as shown in figure 10. so launches the Report Manager as shown in figure (it can take 90 sec- NOTE I like to think of the Report Manager as two tools, one to view launch selected reports and another to manage the reports Services Manager Manager In this case we’ll be to the new SQL Bible Report path and launching the new report we just on the SQL Bible Report icon and choose Reporting Services service to start the Report which processes and sends the rendered HTML back to the report that’s shown in the browser should appear to be similar to the report previewed in the Visual Studio you can expect some subtle on complex the 2008 version of Reporting Services, you can now render reports to of Reporting Services would be complete without the latest a Report Processor that previewed the report on the that can use to leverage some RDL tools that create local reports and persist them as are three types of RDL report file RDL as by Reporting Services RDL as by Reporting Services files, they are easily RDL local reports in the Visual Studio 2008 MRV can display that the MRV Report to the control before you can expect it to render the CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting RDLC file the report about the data source, the query and the columns it returns, and how these are mapped to report elements, the MRV control only uses this and custom data report used in imbedded in the items are set via in the report class exposed code to take on many of the tasks that Reporting Services would a specific RDLC report capturing and the parameter the report is to be a to data queries for each dataset defined by the report including Studio makes the process of setting up a locally rendered report a local RDLC report (using the same Report Wizard as discussed you use the Visual Studio Table Adapter Wizard to build the (report data query), Visual Studio saves the code to the Form_Load event handler in your project to fill the bound data in Reporting Services 2008 in Reporting Services 2008 for Services 2008 no longer needs IIS—at least not to host the virtual up a to be able to access your reports via Secure Sockets Layer a report without SSL is like your big sister’s Services 2008 has also changed the default virtual directory name used to reference the Report Manager and Report didn’t set up SQL Server with a named instance, you can skip on down to the next For the rest of you, when it comes time to address the Report Manager, or to deploy your Visual Studio BI Report Project, you’ll discover the syntax shown in Report Manager (and you’ll want to), you’ll need to use this URL: SQL Server Reporting Services Reporting Services (any version post 2000) needs to visit this tool and walk 656 CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting going to be pretty tough to get your reports back without these I visited the Report Manager URL link and saw the page as shown in figure Services still does not have an easy way to export your reports, so you was to restore the entire Reporting Services to Reporting Services and invented a (free) utility1 to suck out the RDL and data sources and all of the other catalog items from the although it initially did not seem to support SQL Server 2008 I was able to get it to connect to my SS2K8 instance and script out the reports as well as cre- ble to propagate my test reports to another Reporting Services team My all of you will expose the Report Manager utility to your users, but those of you who do might also want to enable the My Reports feature to help users maintain a directory of reports based on their domain login My Reports, Reporting Services included a new directory tree for my I could use to help catalog specific reports that I use on a regular SQL Server Studio but instead of to a choose Reporting Services from the initial Connect to Server dialog 13 The Reporting Services the Report Manager in Reporting Services 2008 for Services Object Explorer is one way to create specific Reporting reports that they should not the My Reports enabled, you’ll be able to set the role applied to each of the My Windows users open the Services catalog for domain Services Figure 15 The Report Manager with My Reports CHAPTER 51 Unlocking the secrets of SQL Server 2008 Reporting with the Report report designers are available to Reporting Services Report Designer used in Visual Studio 2003—This designer has the Reporting Services 2000 RDL Report Designer used in Visual Studio 2005—This designer cannot see or work with Reporting Services 2008 (at all) but it can import Reporting Services 2003 (SQL Server 2000) Reporting Services RDL reports and interface with pollinate with SQL Server 2005 Reporting Services RDL Report Designer used in Visual Studio 2008—This designer still cannot see or work with Reporting Services 2008 (at all) or even open Reporting cross pollinate with SQL Server 2005 Reporting Services RDL Report Designer used in Visual Studio 2008 SP1—This designer can now work with Reporting Services 2008 and even open Reporting Services 2005 Reporting Services RDL reports but not Reporting Services 2008 RDL. The Report Designer launched by the Reporting Services BI (Visual designer is designed to work with Reporting Services 2008 and also Reporting Services 2005 projects and converts them to the Visual why we can’t do what we’ve been told to do when creating real story is, there is a SQL Server Reporting Services 2008 RDL Server Reporting Services 2008’s rendering engine sup- The problem is that the 2008 rendering engine and the Report Designers who BI tools to build these new reports and leverage the RDL-style that reporting is a way of life for any serious (and many it’s easy to see how are these evolving to reduce the amount of code needed to present data (and graphics) to end as well that the Report Processor is designed to take on the of and subject matter expert on Visual Studio, SQL Services, and data access Reporting Services design reports with the best possible but you’ll also come away Services (RS), you’ll find something new in this it does best and use it to retrieve the smallest set of data that the report first apply the to the data, then RS performs rendering are useful when snapshot to apply report at runtime to focus the report on the data servers allow you to treat data that resides on a different server as if it’s on You may perform a query against a linked server in two SQL Server has to make decisions on how to process the that causes the entire remote server table to be brought over the wire to SQL In SQL Server 2000, four- All the data is brought over from the returns an entire year of sales type of report would mean that all the year’s sale data is being processed by the A better way of handling this is to design a report instead, because RS for this type of the same example as before, I’d create a report that I’d add a column and put in a text box report item that Then I’d use Jump To Report to open up the By Day CHAPTER 52 Reporting Services tips and advantage of reports is that the reports you design are reports seen people try to use RS as a data tool like SSIS or of RS, if the data is large enough, you’ll want the fastest way possible to get the data from the report into reports, you need to do two your data source must be to use a specified user and SQL Server to run in mixed mode, which allows me to use a SQL the data sources to use the same login, pooling kicks in. use for the ADO.Net OLE DB and SqlClient .NET data providers use use temp tables and with both SQL Server and not necessary to drop the tables, as SQL Server will properly dispose of temp table in your stored procedure that’s then filled with data from create this beautiful report that your users export to CHAPTER 52 Reporting Services tips and data. In doing this, text boxes so that the Reporting Services renderer doesn’t create a such as those put in by the report table control, and put my report titles, parameter and so forth in RS 2008 renders reports to Excel more than previous web queries and allows users to build on the data in by analysis or building their own as the for designing a report that does the same is to pull the report data into way is to create a to the database and execute the query or the query or writing VBA code to pull in the is resolved by setting the height for the report to to do is perform a one-off data load when the is open, you have a to Reporting Services and you now have a that all your users If your report can take a static parameter or no you’re to refresh the data during the design the data refresh when the cell value CHAPTER 52 Reporting Services tips and might find it handy to have RS host file types other than used on Reports; choose Add, Existing Item; and point to an the file just like you’d deploy a versions of Reporting Services prior to RS 2008, I I had to delete the document from Report Manager and then redeploy for you need to design your reports for 668 CHAPTER 52 Reporting Services tips and default HTML, or I want the report to be rendered to a different you desire is the same as Jump to Report, and you want to reference a to you enter the name of the enter the and report name. You have to deploy the report to test the link. The other for making your site portable has nothing to do with users with the link to Report Manager on your server or wherever you RS into your solution, use this virtual server T-SQL in a it’s possible to embed T-SQL in a report, this can be a source of the Generic Query Designer button in the Data tab. Use an data source (RS T-SQL in the which makes a report a multistep T-SQL, I created some first develop a static SQL and once the report was finished, I’d change it to an 2008 data sources, which are a step like to use data sources—I prefer to use shared data develop and test the T-SQL outside of the its own with RS it now works with the Report Wizard, and where the report are created and all fields are T-SQL with the Report @SQL = 'select from ' + @Database + you might want to develop a report where you limit the data on the specific user the to determine who can run a report; anyone with the rights for can see all the data, but this isn’t always what you want. I have some reports that for the reports that lists the user, what reports they can run, and the parameter that report dataset joins to this doesn’t have to be mapped to a report the user running the report snapshots can use the wrong UserID when accessed via web Snapshots work correctly with UserID only when the report via Report advice is to only use UserID for on-demand Services is both a flexible and powerful tool, but there are a number of things we need to be aware of to derive maximum from our with data ranging from real-time control databases to SQL Server to has been working with Reporting Ser- MVPs to be awarded for Reporting SQL Server Audit, SQL Server 2008 three new features for tracking data and within the database: change tracking, change data capture, and talking about the new features in SQL Server 2008, it will be house or a data cache server, without the overhead of database SQL SQL Server 2008, people would use tools, or write code years ago, titled “How do I audit changes to SQL Server data?” shows how CHAPTER 53 SQL Server Audit, change tracking, and change data time, SQL Server customers have been clamoring for some by providing these three new features with SQL Server 2008. How does SQL Server 2008 solve these does SQL Server 2008 solve these Server Audit SQL Server Audit adds a much easier and more flexible manner of auditing configure audits at the server or database level, all using a more thorough treatment of SQL Server list of actions you can monitor with SQL Server Audit is 2 Creating a server and database audit SQL SQL Server MVPs SQL Server tools for data handling in SQL Server and the tables to store the data Why does SQL Server remove the DTD from my XML data? How do I query XML data? How do I query a single value from my XML data from XML using is SQL the SQL Server Browser the SQL Server instance SQL Server every SQL developer needs a tools and data modeling and database and upgrading SQL server report reporting in SQL with SQL Server 2000 and with SQL Server SQL Server using auditing in SQL Server a server audit using the Windows Security Log server audit for stored (SQL Server 2008 unused stored (SQL Server 2008 how SQL Server reuses table 3—Restart SQL Server in mode Step 4—Connect to SQL Server using Dedicated 6—Remove -m startup parameter and restart SQL database mirroring to upgrade to SQL Server database mirroring to move data changes in SQL Server 2005 SQL Server 2008 data Server index usage SQL Server Extended Events the secrets of SQL Server 2008 Reporting should care about Reporting is Reporting Visual Studio to create an RDL the Visual Studio 2008 Report report your the Report in Reporting Services 2008 for SQL Server Reporting Services My with the Report Services tips and web queries and T-SQL in a Server Audit, change tracking, and change data does SQL Server 2008 solve these Server Audit Why profile in SQL Server flow data flow