SQL Server 2000 Reporting Services Overview 박명은 과장/기술사업부 한국마이크로소프트 Why are we meeting today - context Challenges: Long cycle between releases Lack of field enthusiasm for SQL Server Partners waiting for Yukon need to update their apps to remain competitive Low level of marketing resources for BI compared to competition leading lack of awareness Opportunities: BI continues to help get traction in competitive environments as a key surround strategy Companies investing in BI with continued market growth opportunity No clear leader - fragmented market We have superior technology and greater investment levels than other players We are doing a lot today, but there is so much more that we can do – that is the point of this discussion.

SQL Server Magazine Online Poll (5/2003) 비즈니스 리포팅 현황 기업내에서 요구되는 보고서들 SQL Server Magazine Online Poll (5/2003) 44% - Custom Reporting solutions 45% - 3rd party Reporting solutions 4% - Don’t do much reporting 7% - Satisfied with what we have 현시점에서 각 리포팅 솔루션에 대한 현황 Slide Script: BDM Reporting-- Everyone in business management from HR, Operations, Finance, Marketing, Sales, and the senior executives all make business decisions daily that impact customers, partners, and other employees in some way. We all need to make the best decisions possible to meet customer needs, broaden business insight for all employees and deliver superior productivity and dependability-- all for the best value and ROI possible. BDM’s want to improve market share but have limited resources and dollars. They need to be able to leverage what they have to get that competitive advantage in the marketplace. Industries and customer needs are constantly changing. Having the most up-to-date accurate information at their fingertips when and where they need it is essential to keeping on top of the latest industry trends, avoiding negative company image, and identifying new business opportunities. Common business reports: Sales: for customer relationships, pattern discovery, new products Marketing: for campaign analysis Finance: for budgeting and forecasting Manufacturing: for efficiency and quality control metrics HR: for equal opportunity, force reduction, sick/injury time Customer Service: call times, resolution rates, shift schedules/sizes SQL Server Magazine Online Poll- May 2003- 89 percent of the 333 people who responded to an Instant Poll in that issue of UPDATE said they were interested in the new product. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39215&pg=2/

IT의 현실적 과제 적은 비용과 시간으로 사용자의 요구사항을 충족하는 리포트 생성 Problems: -- 다양한 리포트들이 존재 -- 다양한 데이터베이스 존재 -- 다양한 사용자 요구사항 -- IT 부서는 중간자적인 입장 Slide Script: IT Reporting/ Concerns: The IT department of most organizations are the base of all infrastructure, data management, and reporting capabilities. They bear the weight of keeping up with the latest technologies, keeping the systems up at all times, and ensuring that the company is able to scale its infrastructure for future growth. Technical Decision Makers including Chief Information Officers (CIO) or Chief Technology Officers (CTO), IT Professionals, and developers are being asked to do more with less time and resources. In addition to building their own reports for IT planning, operations results, and forecasting, TDMs oversee the data, systems, and resources for all reports company-wide. That means that IT systems need to be secure, scalable, dependable, integrated and economical. IT Pros are asked to rethink how to get these results without paying for expensive software or developers with costly specialized skills. Technology solutions need to map directly to business value for the company. BI Reporting solutions need to allow for easy adaptation of reports to changing business needs, and for reporting from heterogeneous data sources within and outside the organization using OLE DB, ODBC, Access, Oracle, DB2, and .NET data providers.

현실의 문제점 현 리포팅 솔루션 Issue IT에 의존적 리포트의 Timeless 서로다른 데이터베이스를 연결할수 없음 BI Reporting의 별도 비용추가 확장성 보안 데이터시스템들이나 리포트에 대한 기업내의 표준 부재

Microsoft Solution for BI Reporting 사용자가 직접 리포트 작업 . IT 부서의 자유화를 높여줌 Solution: - IT 는 자원이며 중간자적 역활이 아님 - 사용자는 보고자하는 리포트를 직접 접근할수 있다. Background info: Business Intelligence Reporting is a scalable and robust enterprise-ready reporting solution based on SQL Server 2000 Reporting Services which improves business insight by connecting people to information from any data sources and delivering it to employees via a familiar Web browser, the Office System, or embedded within line of business applications. No longer does IT need to be in the way of users and their reports since users can subscribe to the reports, easily change the report formats (word, html, access, PDF, etc..) or drill down into more details from their desktop. It broadens the adoption of analysis and reporting applications across the organization by giving business users personalized reports that are accurate and real-time—raising the value of data assets and providing a competitive advantage for the organization. It reduces the redundancy of corporate data stored in information silos and allows a unified view of both analytical and transactional production data from a wide range of data sources including: OLE DB, ODBC, Access, Oracle, DB2, and .NET data providers. The solution provides reliable tools for the full reporting lifecycle from authoring to management and delivery, and includes fully integrated security features to control the distribution and viewing of reports to protect corporate data assets. Built to be extensible, the BI Reporting infrastructure supports growth by allowing developers to embed it and scale it in flexible, agile ways that grow along with an organization’s needs.

Reporting Services Web services Authoring Management Delivery Discuss the three components of reporting services You are highlighting the product (Paul will have only briefly talked about the product itself) Speak to: Authoring RDL Integration w/ VS Multiple data sources .NET expressions Free-form, and wizard Charts, tables, matrix – very flexible Management Security Web services API Report Manager is ASP.Net app sitting on web service Manage – security, history, caching, snapshot, subscriptions Delivery Delivery in a format famialr to the end user .xls, OWC, .PDF, HTML, DHTML, TIFF, XML, Comma delimited text, etc. Integration w/ Office File share / e-mail Web services

리포트 생성 XML RDL (Report Definition Language )를 지원하는 Microsoft SQL Reporting Services 및 3rd party tools 로 가능 다양한 원본 소스로 부터 단일 리포트 생성 가능 (Access, DB2, OLE DB, ODBC, Oracle, and .NET data providers) 디자이너는 리포트미리보기와 레이아웃 및 그래픽 디자인을 지원함 Report Server로 배포함 작성되어 배포된 RDL은 다른 파트너들이 활용할 수 있음.

리포트 관리 리포트 정의, 폴더위치,리소스들은 reporting Web Service에 배포되고 관리된다. 관리되는 리포트들은 on-demand 또는 스케줄되어 delivery되며 리포트의 일관성및 성능을 위해서 캐시 되어 짐 Scalable & Extensible Web Services architecture

리포트 전달 전통적인 방식과 상호작용가능한 리포트들 On-demand (“pull”) or event-based (“push”) 전달 방식 Microsoft Office System 과 완벽하게 통합 multiple formats (html, Word, PDF, XML)으로 export 가능 Any device로 리포트 전달 가능

Reporting Services 아키텍쳐 Browser Office Custom Application Security Security Services (NT, Passport, Custom) XML Web Service Interface Data Processing Data Sources (SQL, OLE DB, XML/A, ODBC, Oracle, Custom) Report Server This chart builds – need you to drill into the particular parts as they build. After this is the demo Report Processing Scheduling & Delivery Delivery Targets (E-mail, SharePoint, Custom) Rendering Output Formats (HTML, Excel, PDF, Custom) SQL Server Catalog

Highlight the robust reporting on status of package deployments in SMS 2003.

Windows 환경에서의 통합 리포팅 서비스 개방적이며, 확장가능한 기업 리포팅 솔루션 오피스시스템과의 완벽한 통합 VS.NET 개발 환경 통합 BI 플랫폼 Open, extensible, easy to customize. Reporting Services provides an open and extensible solution supporting the authoring, management and delivery of rich, interactive reports to the entire enterprise. SQL Server Reporting Services supports a wide range of data sources, including OLE DB and Open Database Connectivity (ODBC), as well as multiple output formats such as familiar Web browsers and Microsoft Office System applications. Reporting Services was designed to be easily embedded and extended by in-house or 3rd party (ISV) developers. Reporting Services offers a flexible tool for customizing reports. IT organizations will find it much easier to customize both simple and more complex reports with RS. Familiar end-user tools. Reporting Services is tightly integrated with the Microsoft Office System. This seamless link to the applications that your business already uses on a daily basis, such as Excel, SharePoint portal, and Project, ensures that every aspect of your business – from operations to sales and marketing - can draw on the information it needs to operate most effectively. Developer productivity. Reporting Services was built from the ground up on the .NET platform and provides developers with an open, XML based development platform for rapid application development and integration. Developers will benefit from a programming model that is more flexible and easier to use than the competition. End to end BI solution. Microsoft is uniquely capable of providing a comprehensive, integrated platform for business intelligence from data warehousing and analysis services capabilities in SQL Server, to end user reporting and collaboration through familiar technologies such as Sharepoint, Office, and SQL Server Reporting Services. With Reporting Services, SQL Server provides the single-most comprehensive BI platform on the market with integrated analytics, data mining, data warehousing and reporting functionality enabling companies to get more for less. For partners and enterprise customers, Reporting Services provides a cost-effective, integrated reporting solution that makes it easy to build custom business intelligence applications.

서버 구성안 : 소규모 조직 Reporting Services SQL Server 2000 Windows Server Database and Reporting Server Data Sources Client OLEDB, ODBC, .NET Flat Files Oracle SQL Server DB2 SQL Server 2000 Reporting Services Windows Server Optional slide to explain the different deployment scenarios. For small to medium business, a single server deployment architecture may be sufficient to process all report requests. In this scenario, both the SQL Server Report Catalog, as well as the Report Server and IIS will be run on the same server.

서버 구성안 : 대규모 조직 Reporting Services Windows Server Data Sources Database Server Reporting Server Web Farm Client OLEDB, ODBC, .NET Flat Files Oracle SQL Server DB2 Reporting Services Windows Server IIS Reporting Services Report Metadata Optional slide to explain the different deployment scenarios. For larger organizations, a single server deployment may not provide the scalability and reliability to run mission critical reporting applications. In this case a clustered SQL Server catalog server running Enterprise Edition provides a highly reliable solution for critical report definition and user security information. A scale out web farm architecture will scale linearly to support even the largest and most demanding organizations for reporting. NOTE: While the web farm is an Enterprise feature, SQL Server Enterprise Edition is only required on the Catalog server, not the web farm. SQL Server 2000 Windows Server

Web-Farm Configuration 다양한 확장 방안 단일서버 구성 기존 SQL Server 2000서버에 Reporting Services 사용시 라이센스 비용 Free 분리 서버 구성 Web-Farm 구성 Config. A Config. B Data Sources Report Server DB DB * Web-Farm Configuration Optional Server * Data Sources Report Server DB Data Sources Report Server DB Data Sources Report Server DB Standard Enterprise <OR> Standard Enterprise

4주만에 구축된 네트워크 모니터링 리포팅 솔루션은 기업 전반의 비즈니스 의사결정을 좀더 향상되고 신속하게 할수 있도록 지원함 효과적인 네트워크 모니터링 리포팅 시스템 부재 사용중인 운영시스템의 한계 NOC reporting portal에 SQL Server Reporting Services 사용 Reporting Services와 함께 End to end business intelligence 구현 사용자수 2,000+ 리포트수 200+ 좀더 신속하고 향산된 의사결정 가능 고객에게 새로운 서비스 제공으로 인한 고객만족도 향샹 리소스의 효율적 사용 Paul’s question # 1 How has Microsoft enabled business intelligence for your organization? Time Winebarger (Cox Communications) Answer # 1 Microsoft Business Intelligence platform has allowed Cox to do things that we otherwise would not be able to with budget constrains. The end-to-end solution gives us the flexibility to design and implement reports that impact multiple organizations at multiple levels. For example, the reports can be designed in such a way that they are relevant to the VP of an organization and allow drill through to the minute detail that an engineer can use to solve a repeat problem. For report design the interface is comfortable and easy to use. We no longer need to hire specialized developers to build and maintain reports. Paul’s question # 2 Why is SQL Server Reporting Services critical to your organization? Time Winebarger (Cox Communications) Answer # 2 Without Reporting Services there is no way we would have been able to do what we’ve done. We can now solve problems faster than ever before and now enabling resources to do more proactive network maintenance, network quality improvement and focus on delivering new services and functionality to our customer faster. These operational efficiencies allow resources to make faster more relevant business decisions Reporting Services has truly made our operational resources much more efficient.

사내 포탈 사이트와 인증서버를 리포팅 솔루션과 통합함으로써, 사용의 편리성 강화 및 사용자별 차별화된 리포팅 구현 사내 포탈 사이트와 인증서버를 리포팅 솔루션과 통합함으로써, 사용의 편리성 강화 및 사용자별 차별화된 리포팅 구현 다양한 리포트 제공 요구사항 증가로 인한 부문별 프로세스 개선 추진 수작업 제공 리포트 자동화 필요 리포팅시스템 업그레이드 필요 1 단계로 별도의 임원대상 리포팅 시스템 구현 전체 임원대상으로 BI Reporting 구현 1일 교육후 개발자가 일일 2개 생성가능 Active directory와의 연동을 통해 사용자별 차별화된 리포팅 제공 상호작용이 가능한 리포트들 구현 개발 생산성이 높음 사용의 편리성 Server Side 솔루션 업데이트된 데이터의 신속한 제공 사용자별 차별화된 리포팅 Bill’s question # 1 Why are Web-based interactive reports so important to Coldwater Creek? Dan Moen (Coldwater Creek) Answer # 1 Success of any company is really dependent on the decisions made by the line managers. Reporting Services has allowed us to bring business intelligence to users of all levels – for example; (How many size medium sweaters should I order; what were the sales yesterday; how did a certain catalog perform compared to last year). We have been able to provide more customized information to everyone; thus, improving the decision making process. More importantly, we have done this without increasing our workload. Additionally, it is very important to us that this solution is web-based in order to get the information to the users (100 store managers nationwide, 1 distribution center, 2 call centers and headquarters). Reporting Services is our new corporate standard. Bill’s Question # 2 How well has Reporting Services integrated with your existing platform and what are the impacts on manageability? Dan Moen (Coldwater Creek) Answer # 2 Reporting Services fits well into our existing SQL Server data warehouse environment. Our QA staff is currently working on procedures to include the XML reports from Reporting Services integrated with our daily update procedure. Our goal is to place all reports into Visual Source Safe, so that we have a complete library of report code, and to maximize the communication between the analysts and the DBAs; Reporting Services allows us to seamless achieve that goal. And since the reports are written in Visual Studio .Net, we are able to customize and extend the product as we see fit. Outside of our production servers, Reporting Services allows us to secure sensitive reports. For example, all our store P&Ls are created with Reporting Services and we are able to restrict the right person to see the right report. Lastly, Reporting Services offers options for us to balance the push and pull features of reporting. Some users want to be able to find whatever they want, and some users want to be given reports via a certain format at a specific time. With Reporting Service’s subscriptions and formatting options, we have many options to provide our customers.

Why Reporting Services!! 비즈니스 변화에 대한 실시간 현황 제공 기능 기업전반에 대한 보다 향상된 의사결정 기반 제공 유연하며, 기존의 IT 투자를 수용하는 경제적인 리포팅 솔루션 In conversations with our customers about their Business Intelligence solutions, we found that many of our customers had reporting needs that were not being met by current solutions available in the market. Customers wanted a solution that offered: Real-time insight into business trends through familiar tools. Customers want a reporting solution that is tightly integrated with their existing systems to deliver reports in real time to employees, customers, and partners. In addition, customers want to provide employees with the tools they need to analyst that information and act quickly on their conclusions. Better decision making across the entire organization. Rather than providing a specialized reporting package to executives or analysts only, customers are increasingly interested in equipping every knowledge worker in their extended organization with decision making tools. When BI is tightly integrated into business decisions at all levels, it delivers returns that scale with the number of users and decisions affected. This enhances business responsiveness and translates into hundreds or even thousands of better decisions across the organizations. Flexible and cost effective reporting solution. Customers are interested in a reporting solution that is flexible, can be imbedded and extended by in-house or 3rd party developers, and is integrated with their existing IT environment. Customers are also interested in a managed reporting solution that can scale across the enterprise without draining IT resources with never-ending requests for time-consuming custom reports. We decided to build SQL Server 2000 Reporting Services in response to these needs.

Top 10 Reasons to adopt 기존 SQL Server 2000 투자비용 보존 오피스제품과 뛰어난 호환성, 사용자 편리성 개발용이, 개발기간 단축 .NET Technology 기반 (웹 서비스 제공) Open Standard 기반(XML, RDL) 다양한 데이터소스지원(SQL Server, ORACLE, OLEDB, ODBC) MS Access등으로 제작된 report를 그대로 import가능 다양한 리포트 포맷과 배포채널 제공(Web, Email, PDF, Excel) Scale out 이 쉬운 Enterprise급 확장성 Yukon에서 더욱 확장 SQL Server 싸다 포괄적인 말입니다만, 기존 office제품과 호환성등에서 월등하다.)  기존 MS Office, (SPS) 을 폭넓게 지원하며 매우 간단하다.  (예를 들어 웹상에서 office control로 그래픽, 피폿등을 쉽게 지원).  기존 SQL suite에서 제공하는 NS, OLAP 호환성. 일반 사용자를 위한 day-to-day BI soln이다. (ESS를 위주로한 OLAP 보다 폭넓은 사용자 층) 진정한 .NET 기반이다. 이로인한 잇점들은 security, performance, 안정성 등 다양) authoring이 매우 간단하다.  (VS를 사용하지만, VS내에서 코딩, XML writing을 직접 type안해도 되는 저작도구를 제공, 물론 직접 고칠수도 있음) XML 기반의 RDL (reporting definition language)를 사용하며, RDL은 open standard 라서 3rd party 가 확장 밑 business가능. 다종류, 이 기종의 레포팅원본 DB및 소스를 이용한다.  SQL DB만 한정되어 있지 않고, OLAP 소스, Oracle DB, 기타 어떤 OleDB연결가능한 데이터를 쉽게 소스로 가질수 있고,  기존에 MS Access등으로 제작된 report를 그대로 import가능. 다종류 이기종의 레포팅 아웃풋 밑 배포 방법을 제공.  Online(웹), Offline(PDF, Office문서(excel)), email, scheduled delivery, triggered delivery, personalized delivery. Scale out 을 염두에 둔 enterprise

SQL Server 2000 Reporting Services 홈페이지 (korea) http://www.microsoft.com/korea/sql/reporting 2월 중순부터 한글 정품 CD 배포가능 한글 평가판 다운로드 사이트 http://www.microsoft.com/sql/reporting/productinfo /trial.asp#download Call to action: Get it, use it, start to realize the benefits we highlighted on the previous slide