39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
Analysis Services have been the number one OLAP engine for years. With the increased focus on business intelligence solutions, there is a shortage of professionals in this area. Start your journey into becoming a BI developer using the popular tools included in every SQL Server installation.
Getting Started with SQL Server 2012 Cube Development teaches you through clear step-by-step exercises to create business intelligence solutions using Analysis Services. The knowledge gained through these practical examples can immediately be applied to your real-world problems.
Getting Started with SQL Server 2012 Cube Development begins with an introduction to business intelligence and Analysis Services, the world's most-used cube engine. Guiding you through easy-to-understand examples to become a cube developer.
Learn how to create a cube including all the advanced features such as KPIs, calculated measures, and time intelligence. Security and performance tuning will also be explored. You will learn how to perform and automate core tasks like deployment and processing. The main focus is on multidimensional cubes, but the creation of in-memory models will also be covered.
You will learn everything you need to get started with cube development using SQL Server 2012.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 277
Veröffentlichungsjahr: 2013
Ähnliche
Table of Contents
Getting Started with SQL Server 2012 Cube Development
Getting Started with SQL Server 2012 Cube Development
Copyright © 2013 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: September 2013
Production Reference: 1040913
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-950-2
www.packtpub.com
Cover Image by Suresh Mogre (<[email protected]>)
Credits
Author
Simon Lidberg
Reviewers
David Loo
Richard Louie
Donabel Santos
Acquisition Editor
James Jones
Lead Technical Editor
Dayan Hyames
Technical Editors
Anusri Ramchandran
Dennis John
Kapil Hemnani
Gaurav Thingalaya
Project Coordinator
Apeksha Chitnis
Proofreader
Mario Cecere
Indexer
Tejal Daruwale
Graphics
Yuvraj Mannari
Production Coordinator
Manu Joseph
Cover Work
Manu Joseph
About the Author
Simon Lidberg is a database veteran, who has worked in the Computer industry since the mid nineties. He has had roles as Consultant, Support Engineer, Escalation Engineer, and Technical Presales Specialist. In the past 15 years, he has worked with databases and ERP Systems at companies such as Digital Equipment, Compaq, and Microsoft.
He has been an expert in Microsoft SQL Server since he started to work with Version 6.5 at Microsoft as a Support Engineer. Since 2006, he has worked with the Microsoft Business Intelligence stack that includes Analysis Services.
Since then, he has helped numerous companies to start using Analysis Services as well as have trained hundreds of people on Microsoft BI.
Simon is also a frequent speaker at conferences such as PASS SQLRally and Microsoft TechDays. He currently blogs at http://blogs.msdn.com/b/querysimon
I wish to thank the people at Packt Publishing who gave me the opportunity to write this book. I have tried to write the book that I felt was missing when I moved into the Business Intelligence space, after having worked with databases for 10 years. I hope that you, as a reader, will find it beneficial and that it will help you know Analysis Services.
I also want to thank the people who have helped me while writing this book, Mikael, Stephen, and all the rest; thanks for your help and inspiration.
Most of all, I wish to thank my wife, Marita, for the support during the work with the book.
About the Reviewers
David Loo is a Senior Software Development Professional with over 25 years of experience in both software development and people management. He is respected for his ability to focus teams on service excellence and for designing and implementing practical process improvements. Always on the lookout for ways to contribute his knowledge and experience of software development, team-building, and development best practices.
Richard Louie is a Business Intelligence developer at Redwood Trust, a residential and commercial mortgage investment firm. He has extensive experience in Oracle and Microsoft SQL for ETL, SSIS, SSRS, SSAS, and VB.NET. Richard is ASQ Green Belt Certified. He is a graduate in Information and Computer Science from the University of California, Irvine.
Donabel Santos is a SQL Server MVP and is the Senior SQL Server Developer/DBA/Trainer at QueryWorks Solutions, a consulting and training company in Vancouver, BC. She has worked with SQL Server since Version 2000 in numerous development, tuning, reporting, and integration projects with ERPs, CRMs, SharePoint, and other custom applications. She holds MCITP certifications for SQL Server 2005/2008, and an MCTS for SharePoint. She is a Microsoft Certified Trainer (MCT), and is also the lead instructor for SQL Server Administration, Development, Tableau, and SSIS courses at British Columbia Institute of Technology (BCIT). Donabel is a proud member of PASS (Professional Association of SQL Server), and a proud BCIT alumna (CST diploma and degree).
Donabel blogs at www.sqlmusings.com and her twitter handle is @sqlbelle. She speaks and presents at SQLSaturday, VANPASS, Vancouver TechFest, and so on. She writes for Packt, Idera, SSWUG, and so on. She is the author of Packt's SQL Server 2012 with PowerShell V3 Cookbook, and a contributing author of Manning's PowerShell Deep Dives.
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
Most books about Analysis Services are targeted at people who already work in the BI space and want to become experts. I wanted to write a book that could be read and understood by a novice who wants to become a BI developer. I have made this journey myself; I had worked with database solutions for more than 10 years when I started to work with BI solutions. I had a hard time finding a book that would cover the introduction to multidimensional modeling.
The intent of this book is not to be a complete book on Analysis Services development, but to serve as an introduction that will allow the user to get started. The book also contains links to where a reader can find more in-depth material on the topics covered. This will allow the user to start as a novice and to move into the role of an intermediate Analysis Services developer.
What this book covers
This book is a step-by-step instruction on how to get started with cube development. It takes the reader through the steps of installing and developing a BI solution built on Analysis Services. It contains the following chapters:
Chapter 1, Self-service Business Intelligence, Creating Value from Data, serves as an introduction to Business Intelligence solutions and specifically self-service solutions.
Chapter 2, Installing SSAS and Preparing for Cube Development, discusses the different models available to a user in Analysis Services. It covers the installation of Analysis Services and an introduction to the development environment.
Chapter 3, Creating Your First Multidimensional Cube, starts with an introduction to data warehousing modeling followed by a step-by-step instruction covering the initial development of the first OLAP cube.
Chapter 4, Deploying and Processing Cubes, covers the deployment and processing of cubes that are necessary in cube development. You will learn how you can automate processing of cubes to ensure that they contain the latest information from the data warehouse. You also learn how to partition your cubes to minimize processing times.
Chapter 5, Querying Your Cube, serves as an introduction to MDX, the query language used in Analysis Services. You will also learn how Excel can be used as a query tool against multidimensional cubes.
Chapter 6, Adding Functionality to Your Cube, looks at how you can extend your cube with calculations such as calculated measures and members, key performance indicators (KPIs), time calculations, and actions. It also contains information about how you can use your cube in a multi-lingual environment through the use of translations, and how you can build budgeting solutions using Analysis Services.
Chapter 7, Securing Your Cube Project, discusses how security works in Analysis Services and how a cube can be secured. You will learn how you can implement a dynamic security model using MDX functions.
Chapter 8, Using Aggregations to Performance Optimize a Cube, covers how aggregations can be used in Analysis Services to improve the performance of the BI solution. You will learn how you can create aggregations based on the actual usage of the cube.
Chapter 9, In-memory, the Future, introduces how you can build in-memory models in Analysis Services tabular mode. The chapter discusses measures, hierarchies, security, partitioning as well as more advanced topics such as time calculations and KPIs.
Chapter 10, Cubes in the Larger Context, shows how cubes fit into the larger architecture of a BI solution. You will get an introduction to the available frontend tools from Microsoft as well as the third-party tools that can be used as query tools against cubes.
What you need for this book
To follow the step-by-step instructions in this book you need a computer with the following software:
Who this book is for
The audience of this book includes SQL Server developers that previously have not worked with Analysis Services, but want to move into the BI space. It is assumed that you have experience with relational databases but no skills in cube development are required.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "We can include other contexts through the use of the include directive."
A block of code is set as follows:
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking on the Next button moves you to the next screen".
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the erratasubmissionform link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Self-service Business Intelligence, Creating Value from Data
Over the years most businesses have spent considerable amount of time, money, and effort in building databases, reporting systems, and Business Intelligence (BI) systems. IT often thinks that they are providing the necessary information to the business users for them to make the right decisions.
However, when I meet the users they tell me a different story. Most often they say that they do not have the information they need to do their job. Or they have to spend a lot of time getting the relevant information. Many users state that they spend more time getting access to the data than understanding the information.
This divide between IT and business is very common, it causes a lot of frustration and can cost a lot of money, which is a real issue for companies that needs to be solved for them to be profitable in the future. Research shows that by 2015 companies that build a good information management system will be 20 percent more profitable compared to their peers.
Note
You can read the entire research publication from http://download.microsoft.com/download/7/B/8/7B8AC938-2928-4B65-B1B3-0B523DDFCDC7/Big%20Data%20Gartner%20information_management_in_the_21st%20Century.pdf.
So how can an organization avoid the pitfalls in BI systems and create an effective way of working with information? This chapter will cover the following topics concerning it:
Identifying common user requirements for a BI system
In many cases, companies that struggle with information delivery do not have a dedicated reporting system or data warehouse. Instead the users have access only to the operational reports provided by each line of business application. This is extremely troublesome for the users that want to compare information from different systems.
As an example, think of a sales person that wants to have a report that shows the sales pipeline, from the Customer Relationship Management (CRM) system together with the actual sales figures from the Enterprise Resource Planning (ERP) system. Without a common reporting system the users have to combine the information themselves with whatever tools are available to them.
Most often this tool is Microsoft Excel. While Microsoft Excel is an application that can be used to effectively display information to the users, it is not the best system for data integration. To perform the steps of extracting, transforming, and loading data, from the source system, the users have to write tedious formulas and macros to clean data, before they can start comparing the numbers and taking actual decisions based on the information.
Lack of a dedicated reporting system can also cause trouble with the performance of the Online Transaction Processing (OLTP) system. When I worked in the SQL Server support group at Microsoft, we often had customers contacting us on performance issues that they had due to the users running the heavy reports directly on the production system.
To solve this problem, many companies invest in a dedicated reporting system or a data warehouse. The purpose of this system is to contain a database customized for reporting, where the data can be transformed and combined once and for all from all source systems. The data warehouse also serves another purpose and that is to serve as the storage of historic data.
Many companies that have invested in a common reporting database or data warehouse still require a person with IT skills to create a report. The main reason for this is that the organizations that have invested in a reporting system have had the expert users define the requirements for the system. Expert users will have totally different requirements than the majority of the users in the organization and an expert tool is often very hard to learn.
An expert tool that is too hard for the normal users will put a strain on the IT department that will have to produce all the reports. This will result in the end users waiting for their reports for weeks and even months.
One large corporation that I worked with had invested millions of dollars in a reporting solution, but to get a new report the users had to wait between nine and 12 months, before they got the report in their hand. Imagine the frustration and the grief that waiting this long before getting the right information causes the end users.
To many users, BI means simple reports with only the ability to filter data in a limited way.
While simple reports such as the one in the preceding screenshot can provide valuable information, it does not give the users the possibility to examine the data in detail. The users cannot slice-and-dice the information and they cannot drill down to the details, if the aggregated level that the report shows is insufficient for decision making.
If a user would like to have these capabilities, they would need to export the information into a tool that enables them to easily do so. In general, this means that the users bring the information into Excel to be able to pivot the information and add their own measures. This often results in a situation where there are thousands of Excel spreadsheets floating around in the organization, all with their own data, and with different formulas calculating the same measures.
When analyzing data, the data itself is the most important thing. But if you cannot understand the values, the data is of no benefit to you. Many users find that it is easier to understand information, if it is presented in a way that they can consume efficiently.
This means different things to different users, if you are a CEO, you probably want to consume aggregated information in a dashboard such as the one you can see in the following screenshot:
On the other hand, if you are a controller, you want to see the numbers on a very detailed level that would enable you to analyze the information. A controller needs to be able to find the root cause, which in most cases includes analyzing information on a transaction level.
A sales representative probably does not want to analyze the information. Instead, he or she would like to have a pre-canned report filtered on customers and time to see what goods the customers have bought in the past, and maybe some suggested products that could be recommended to the customers.
Getting user value through self-service reporting
SQL Server Analysis Services is an application that allows you to create a semantic model that can be used to analyze very large amounts of data with great speed. The models can either be user created, or created and maintained by IT.
If the user wants to create it, they can do so, by using a component in Microsoft Excel 2010 and upwards called PowerPivot. If you run Microsoft Excel 2013, it is included in the installed product, and you just need to enable it. In Microsoft Excel 2010, you have to download it as a separate add-in that you either can find on the Microsoft homepage or on the site called http://www.powerpivot.com. PowerPivot creates and uses a client-side semantic model that runs in the context of the Microsoft Excel process; you can only use Microsoft Excel as a way of analyzing the data. If you just would like to run a user created model, you do not need SQL Server at all, you just need Microsoft Excel. On the other hand, if you would like to maintain user created models centrally then you need, both SQL Server 2012 and SharePoint.
