Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services: Design and implement fast, scalable and maintainable cubes with Microsoft SQL Server 2008 Analysis Services with this book and eBook

eBook
€28.99 €32.99
Paperback
€41.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Table of content icon View table of contents Preview book icon Preview Book

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Chapter 2. Building Basic Dimensions and Cubes

Having prepared our relational source data, we're now ready to start designing a cube and some dimensions. This chapter covers the steps you need to go through in order to create simple dimensions and cubes, and although you may be confident that you know how to do this already, we encourage you to read through this chapter nonetheless. You may be familiar with the overall process, but some of the detailed recommendations that we make may be new to you, and they could save you a lot of time and effort later on in your project.

In this chapter, we'll be taking a look at the following topics:

  • Creating Data Sources and Data Source Views

  • Creating dimensions, setting up user hierarchies and configuring attribute relationships

  • Creating a simple cube

  • Deployment and processing

From a methodology point of view, this chapter represents the creation of the first draft of your cube. In subsequent chapters we'll look at how you tackle the more advanced modeling...

Choosing an edition of Analysis Services


Before we start developing with Analysis Services, we need a clear idea of which edition of Analysis Services we're going to be developing for. There are two choices: Standard Edition, which is cheaper but missing some features, and Enterprise Edition, which is more expensive but feature complete. Licensing cost is likely to be the major factor in this decision. If money is no object, then you should use Enterprise Edition. If it is an issue, then you'll just have to live with the limitations of Standard Edition. Of course, if we install Analysis Services on a server that already has SQL Server installed then there are no extra license costs involved, but as we'll see in Chapter 11, we have to be careful they don't compete for resources. This document on the Microsoft website gives a detailed breakdown of which features are available in each edition: http://tinyurl.com/sqlstdvsent.

Don't worry about having to use the Standard Edition though. Some...

Setting up a new Analysis Services project


The first step towards creating a new cube is to create a new Analysis Services project in BIDS. Immediately after doing this, we strongly recommend putting your new project into source control. It's easy to forget to do this, or not bother, because building a cube doesn't seem like a traditional development project, but you'll be glad that you did it when you receive your first request to rollback a change to a complex MDX calculation.

As you're probably aware, there are two ways of working with Analysis Services projects in BIDS:

  • Project mode: where you work with a local Visual Studio project and deploy to your Analysis Services server only when you're happy with all the changes you've made

  • Online mode: where you edit your Analysis Services database live on the server and commit changes every time you click on the Save button

You'll only be able to use source control software effectively if you work in the project mode. Therefore, it's a good idea...

Creating data sources


Once we've created a new project and configured it appropriately, the next step is to create a data source object. Even though you can create multiple data sources in a project, you probably shouldn't. If you've read the previous chapter, then you'll know that we recommend that all of the data needed for your cube should already be present in a single data mart.

You are then faced with the choice of which OLE DB provider to use, since there are often several different options for any given relational database. For SQL Server data sources, you have the option of using the SQLClient .NET data provider, the Microsoft OLE DB provider for SQL Server and the SQL Server Native Client (often referred to as SNAC). You should always choose the SQL Server Native Client since it offers the best performance. For Oracle data sources, the choice is more complicated since, even though Oracle is a supported data source for Analysis Services, there is a long list of bugs and issues....

Creating Data Source Views


In an ideal world, if you've followed all of our recommendations so far, then you should need to do very little work in your project's Data Source View—nothing more than selecting the views representing the dimension and fact tables and setting up any joins between the tables that weren't detected automatically. Of course, in the real world, you have to compromise your design sometimes and that's where a lot of the functionality available in Data Source Views comes in useful.

When you first create a new Data Source View (DSV), the easiest thing to do is to go through all of the steps of the wizard, but not to select any tables yet. You can then set some useful properties on the DSV, which will make the process of adding new tables and relationships much easier. In order to find them, right-click on some blank space in the diagram pane and click on Properties. They are:

  • Retrieve Relationships—by default, this is set to True, which means that BIDS will add relationships...

Designing simple dimensions


Next, let's build some dimensions. As this is one of the more complicated steps in the cube design process, it's a topic we'll return to again in future chapters when we need to deal with more advanced modeling scenarios. Right now we'll concentrate on the fundamentals of dimension design.

Using the 'New Dimension' wizard

Running the New Dimension wizard will give you the first draft of your dimension, something you'll then be able to tweak and tidy up in the Dimension Editor afterwards. The first question you'll be asked, on the Select Creation Method step, is how you want to create the new dimension and there are effectively two choices:

  • Create the dimension from an existing table or view in your data source (the Use an Existing Table option)

  • Have BIDS create a dimension automatically for you and optionally fill it with data (the other three options)

In keeping with our line of argument that all relational modeling work should be done outside BIDS, we recommend you...

Building a Simple Cube


With some dimensions built, the next step is to run the cube wizard to create the cube itself. Remember that at this stage all we want to do is build a very simple cube so that we can test-drive the data, so we're not going to do anything other than run the wizard. You'll be doing a lot of work in the Cube Editor in the next stage of development, but if you've set up the DSV in the way we recommend, then you'll find that when you've finished running the wizard, you will have something that you can deploy, process and browse immediately with no changes required.

Using the 'New Cube' wizard

On the Select Creation Method step of the wizard, as with the same step of the New Dimension wizard, choose the Use an Existing table option—the Create an Empty Cube and the Generate Tables in the Data Source options can be ignored for now. The former is useful in more advanced scenarios but regarding the latter, we'll repeat what we said earlier: you should model your data properly...

Summary


With processing complete, you can take a look at your cube for the first time, either in the Browser tab of the Cube Editor or in your client tool of choice. Now is a good time to reflect on what we've seen of the cube development process so far:

  • We've created a very basic cube from a single fact table and a few dimensions rather than attempting to build something more complex. This has allowed us to get a feel for our data and have something to show our users quickly so that they can check if we're on the right track.

  • We built a single Data Source and Data Source View. Since we spent time getting our data modeling right earlier, there is very little to do here other than connect to our data warehouse and select the tables or views we want to work with.

  • We built a few of the less complex dimensions we need, configuring attribute relationships and creating user hierarchies as necessary.

  • We ran the 'New Cube' wizard to build our basic cube, then deployed and processed it so that it can...

Left arrow icon Right arrow icon

Key benefits

  • A real-world guide to designing cubes with Analysis Services 2008
  • Model dimensions and measure groups in BI Development Studio
  • Implement security, drill-through, and MDX calculations
  • Learn how to deploy, monitor, and performance-tune your cube
  • Filled with best practices and useful hints and tips

Description

Microsoft's SQL Server Analysis Services 2008 is an OLAP server that allows users to analyze business data quickly and easily. However, designing cubes in Analysis Services can be a complex task: it's all too easy to make mistakes early on in development that lead to serious problems when the cube is in production. Learning the best practices for cube design before you start your project will help you avoid these problems and ensure that your project is a success. This book offers practical advice on how to go about designing and building fast, scalable, and maintainable cubes that will meet your users' requirements and help make your Business Intelligence project a success. This book gives readers insight into the best practices for designing and building Microsoft Analysis Services 2008 cubes. It also provides details about server architecture, performance tuning, security, and administration of an Analysis Services solution. In this book, you will learn how to design and implement Analysis Services cubes. Starting from designing a data mart for Analysis Services, through the creation of dimensions and measure groups, to putting the cube into production, we'll explore the whole of the development lifecycle. This book is an invaluable guide for anyone who is planning to use Microsoft Analysis Services 2008 in a Business Intelligence project.

Who is this book for?

This book is aimed at Analysis Services developers who already have some experience but who want to go into more detail on advanced topics, and who want to learn best practices for cube design.

What you will learn

  • Build a data mart suitable for use with Analysis Services
  • Create and configure an Analysis Services project in Business Intelligence Development Studio
  • Use the Dimension Wizard and the Dimension Editor to build dimensions
  • Create measure groups and associate them with dimensions
  • Add calculations to the cube, including implementing currency conversion and a date tool dimension
  • Explore the security model, including dimension security and cell security, and implement dynamic security
  • Tune queries to get the best possible performance
  • Automate processing and partition creation
  • Monitor your cube to see who s actually using it

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jul 15, 2009
Length: 360 pages
Edition : 1st
Language : English
ISBN-13 : 9781847197238
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Product Details

Publication date : Jul 15, 2009
Length: 360 pages
Edition : 1st
Language : English
ISBN-13 : 9781847197238
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 91.98
SQL Server Analysis Services 2012 Cube Development Cookbook
€49.99
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
€41.99
Total 91.98 Stars icon

Table of Contents

11 Chapters
Designing the Data Warehouse for Analysis Services Chevron down icon Chevron up icon
Building Basic Dimensions and Cubes Chevron down icon Chevron up icon
Designing More Complex Dimensions Chevron down icon Chevron up icon
Measures and Measure Groups Chevron down icon Chevron up icon
Adding Transactional Data such as Invoice Line and Sales Reason Chevron down icon Chevron up icon
Adding Calculations to the Cube Chevron down icon Chevron up icon
Adding Currency Conversion Chevron down icon Chevron up icon
Query Performance Tuning Chevron down icon Chevron up icon
Securing the Cube Chevron down icon Chevron up icon
Productionization Chevron down icon Chevron up icon
Monitoring Cube Performance and Usage Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.4
(19 Ratings)
5 star 78.9%
4 star 5.3%
3 star 0%
2 star 10.5%
1 star 5.3%
Filter icon Filter
Top Reviews

Filter reviews by




Akilucky Nov 20, 2009
Full star icon Full star icon Full star icon Full star icon Full star icon 5
著者の三人は、BI関連の本、Blogで有名な人たちです。本のタイトルが示す通り、Analysis Servicesの初心者向けの本ではありません。MDXとは何かとか構文の説明などはなく、real-worldで直面する実践的な内容が書かれています。第六章の計算されるメンバ(Year-to-date, Ratio over a hierarchy, Previous period growths, Same period previous year etc.)についての説明は必読。無駄なことが書かれていないためこの手の本としては、ページ数も約330と少ないです。
Amazon Verified review Amazon
reader mark Aug 06, 2010
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I read it cover to cover, and have gone through some chapters several times... Book is becoming a little bit ragged, always a good indicator of value for me. Not a novice book - you should have gone through a couple of real world SSAS projects to be comfortable with the content. Especially valuable if you are transitioning into 2008 versions from prior version and want to hit the ground running.There certainly are some "opinions" being put forward - but if you don't have an experience based stance against a recommendation, you should probably go with it and adjust later..
Amazon Verified review Amazon
Eric M. Jan 15, 2011
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This book is not for absolute beginners but I am amazed page after page to read answers about questions I asked myself without always finding the answer.This is both deep and practical.I like the fact that it does not assume SSAS is the best answer to all problems.For instance, it does mention that SSAS is not that great for chart of accounts compared to some other products, or that you would be better off not relying exclusively on SSAS to solve your currency rates problems.In my current project, for instance, our developers have problems handling Year To Date calculations (please don't laugh) and the answer to similar date related calculations is so clearly explained in this book.I thoroughly enjoyed this reading and plan to go back to it regularly for priceless "how to" advice.You may want to check this article as this is how I learned about this book and it might convince you whether or not it is for you(.....)
Amazon Verified review Amazon
Nasar Aug 25, 2011
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I have only read the first chapter and it has so many practical tips (Dos and Don't) from real world development not just theories, that it has paid for itself. If chapter one is any indication of what is in store in rest of the book, then this is a gold mine!
Amazon Verified review Amazon
Vadertime Oct 18, 2010
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I recently inherited a fairly sophisticated analysis cube. Until I started working with cubes, my level of understanding was a 2 on a scale of 10. In about 2 weeks, I was able to get a handle on how the cube I inherited was working and what it was doing. This book has been worth every dollar I paid for it. Use the book in conjunction with an actual cube - it's not for leisure reading. Happy cubing.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.