You could use Excel® to format the results, but then you would have to maintain a separate Excel file for each end-user. They will want to see the results in a nicer format – the user interface is very important. You cannot expect them to learn the Transact-SQL language. You cannot give SQL Server Management Studio to all of your end-users. The advantage of ad-hoc querying is that I can do whatever I want – I could continue researching with many different queries. This shows that single people with three cars are also a very interesting group.įor more on Transact-SQL queries for SQL Server 2005, read “Inside Microsoft® SQL Server⢠2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka, ISBN 0-7356-2313-9. Table 3: Pivoting the percentage of bike buyers For example, consider the following simple query to the vTargetMail view:įOR MaritalStatus IN (, ) ) As pvt It’s possible for a developer to gain quite a bit of business intelligence just from using SQL Server Management Studio (SSMS) to write ad hoc queries. The name of the view is vTargetMail you can find it in the AdventureWorksDW database. As a starting point in their learning process, they create a view joining the demographic information of their existing customers with a BikeBuyer column serving as a flag to indicate which customers have purchased a bike in the past. Having obtained the data about prospective buyers, they will need to filter it. They decide to find out what kind of people tend to buy bikes, so that they can perform a more targeted mailing campaign. If the company could achieve a four percent response, they could halve the cost of the campaign while still achieving the same number of responses, but only printing half as many leaflets. The response rate for such campaigns is notoriously poor – typically, only two percent. In order to increase their sales, the company is preparing a mailing campaign. AdventureWorks is an OLTP database and AdventureWorksDW is the data warehouse database of the company. They also sell other sports equipment and accessories. Adventure Works Cycles is a fictitious company that manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. Here, I focus on reporting solutions – from ad-hoc queries through managed reports to report models.īoth articles use the sample AdventureWorks and AdventureWorksDW databases. I aim to demonstrate that, for a complete business intelligence solution, each has its place and that it’s only by the appropriate use of each one that we get the structured range of reports we need.
In this article and the next, I will present all available possibilities in SQL Server 2005 and describe the strengths and weaknesses of each. With SQL Server 2005, we get all the modern analytical technologies, from basic reporting, through OLAP systems, to data mining applications.
The primary focus of today’s IT systems is deriving information from collections of data, and several technologies exist for this purpose. Business Intelligence in SQL Server 2005, Part 1 - Simple Talk Skip to contentĪn Overview of SQL Server 2005 Reporting Solutions
0 kommentar(er)
