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
$54.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with Print?

Product feature icon Instant access to your digital copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Redeem a companion digital copy on all Print orders
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

Shipping Address

Billing Address

Shipping Methods
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
Estimated delivery fee Deliver to Colombia

Standard delivery 10 - 13 business days

$19.95

Premium delivery 3 - 6 business days

$40.95
(Includes tracking information)

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 : 9781847197221
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with Print?

Product feature icon Instant access to your digital copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Redeem a companion digital copy on all Print orders
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

Shipping Address

Billing Address

Shipping Methods
Estimated delivery fee Deliver to Colombia

Standard delivery 10 - 13 business days

$19.95

Premium delivery 3 - 6 business days

$40.95
(Includes tracking information)

Product Details

Publication date : Jul 15, 2009
Length: 360 pages
Edition : 1st
Language : English
ISBN-13 : 9781847197221
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 $ 120.98
SQL Server Analysis Services 2012 Cube Development Cookbook
$65.99
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
$54.99
Total $ 120.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

What is the digital copy I get with my Print order? Chevron down icon Chevron up icon

When you buy any Print edition of our Books, you can redeem (for free) the eBook edition of the Print Book you’ve purchased. This gives you instant access to your book when you make an order via PDF, EPUB or our online Reader experience.

What is the delivery time and cost of print book? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela
What is custom duty/charge? Chevron down icon Chevron up icon

Customs duty are charges levied on goods when they cross international borders. It is a tax that is imposed on imported goods. These duties are charged by special authorities and bodies created by local governments and are meant to protect local industries, economies, and businesses.

Do I have to pay customs charges for the print book order? Chevron down icon Chevron up icon

The orders shipped to the countries that are listed under EU27 will not bear custom charges. They are paid by Packt as part of the order.

List of EU27 countries: www.gov.uk/eu-eea:

A custom duty or localized taxes may be applicable on the shipment and would be charged by the recipient country outside of the EU27 which should be paid by the customer and these duties are not included in the shipping charges been charged on the order.

How do I know my custom duty charges? Chevron down icon Chevron up icon

The amount of duty payable varies greatly depending on the imported goods, the country of origin and several other factors like the total invoice amount or dimensions like weight, and other such criteria applicable in your country.

For example:

  • If you live in Mexico, and the declared value of your ordered items is over $ 50, for you to receive a package, you will have to pay additional import tax of 19% which will be $ 9.50 to the courier service.
  • Whereas if you live in Turkey, and the declared value of your ordered items is over € 22, for you to receive a package, you will have to pay additional import tax of 18% which will be € 3.96 to the courier service.
How can I cancel my order? Chevron down icon Chevron up icon

Cancellation Policy for Published Printed Books:

You can cancel any order within 1 hour of placing the order. Simply contact customercare@packt.com with your order details or payment transaction id. If your order has already started the shipment process, we will do our best to stop it. However, if it is already on the way to you then when you receive it, you can contact us at customercare@packt.com using the returns and refund process.

Please understand that Packt Publishing cannot provide refunds or cancel any order except for the cases described in our Return Policy (i.e. Packt Publishing agrees to replace your printed book because it arrives damaged or material defect in book), Packt Publishing will not accept returns.

What is your returns and refunds policy? Chevron down icon Chevron up icon

Return Policy:

We want you to be happy with your purchase from Packtpub.com. We will not hassle you with returning print books to us. If the print book you receive from us is incorrect, damaged, doesn't work or is unacceptably late, please contact Customer Relations Team on customercare@packt.com with the order number and issue details as explained below:

  1. If you ordered (eBook, Video or Print Book) incorrectly or accidentally, please contact Customer Relations Team on customercare@packt.com within one hour of placing the order and we will replace/refund you the item cost.
  2. Sadly, if your eBook or Video file is faulty or a fault occurs during the eBook or Video being made available to you, i.e. during download then you should contact Customer Relations Team within 14 days of purchase on customercare@packt.com who will be able to resolve this issue for you.
  3. You will have a choice of replacement or refund of the problem items.(damaged, defective or incorrect)
  4. Once Customer Care Team confirms that you will be refunded, you should receive the refund within 10 to 12 working days.
  5. If you are only requesting a refund of one book from a multiple order, then we will refund you the appropriate single item.
  6. Where the items were shipped under a free shipping offer, there will be no shipping costs to refund.

On the off chance your printed book arrives damaged, with book material defect, contact our Customer Relation Team on customercare@packt.com within 14 days of receipt of the book with appropriate evidence of damage and we will work with you to secure a replacement copy, if necessary. Please note that each printed book you order from us is individually made by Packt's professional book-printing partner which is on a print-on-demand basis.

What tax is charged? Chevron down icon Chevron up icon

Currently, no tax is charged on the purchase of any print book (subject to change based on the laws and regulations). A localized VAT fee is charged only to our European and UK customers on eBooks, Video and subscriptions that they buy. GST is charged to Indian customers for eBooks and video purchases.

What payment methods can I use? Chevron down icon Chevron up icon

You can pay with the following card types:

  1. Visa Debit
  2. Visa Credit
  3. MasterCard
  4. PayPal
What is the delivery time and cost of print books? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela