New book: Microsoft SQL Server 2008 Analysis Services Step by Step


New book: Microsoft SQL Server 2008 Analysis Services Step by Step

  • Comments 17

While there may be a shortage of cash, jobs, and common sense in the world right now, there’s certainly no shortage of information. In fact, the already overwhelming amount of data is expected to grow another 35% each and every year. And yet information is critical to making intelligent decisions.

That’s where the field of business intelligence comes in. As Scott Cameron writes in his new book Microsoft SQL Server 2008 Analysis Services Step by Step, “Business intelligence should help you make better decisions faster.” And that’s just what Scott and his colleagues at Hitachi Consulting show you in the book, whether you’re a BI developer, administrator, or business manager.

9780735626201f

In honor of Microsoft’s TechEd 2009 conference, wrapping up tomorrow in Los Angeles, we’d like to give you a more generous than usual sampling from the book. The samples includes the Contents at a Glance page, a table mapping out the book for each segment of the target readership, and an overview of business intelligence explaining how and where Analysis Services fits into the bigger picture.

Note: If you’re a BI developer and would like more detailed information and instruction on MDX, the language used by Analysis Services for data access, we’d like to recommend Microsoft SQL Server 2008 MDX Step by Step by Bryan C. Smith and C. Ryan Clay (the subject of a previous post).

contents_at_a_glance

 

Introduction


Microsoft SQL Server 2008 Analysis Services is the multidimensional online analytical processing
(OLAP) component of Microsoft SQL Server 2008 that integrates relational and OLAP
data for business intelligence (BI) analytical solutions. The goal of this book is to show you
how to use the tools and features of Analysis Services so you can easily create, manage, and
share OLAP cubes within your organization. Step-by-step exercises are included to prepare
you for developing your own BI solutions.


To help you learn the many features of Analysis Services, this book is organized into four
parts. Part I, “Understanding Business Intelligence and Analysis Services,” introduces BI,
multidimensional analysis, and OLAP and explains how Analysis Services implements the
benefits of OLAP. Part II, “Design Fundamentals,” teaches you how to design data sources,
data source views, dimensions, and cubes. Part III, “Advanced Design,” shows you how to use
Multidimensional Expressions (MDX) and aggregate functions to perform complex calculations
and summarizations, and to create key performance indicators (KPIs). In addition, this
part covers special Analysis Services features for advanced dimension design, financial analysis,
globalization, and a variety of interactions that extend the analytical capabilities of cubes.
You will also learn how to create analytical reports using Microsoft Office Excel and SQL
Server 2008 Reporting Services. Part IV, “Production Management,” explains how to use security
to control access to cubes and to restrict the data that a particular user can see, how to
design partitions to manage database scalability, and how to manage and monitor Analysis
Services databases.

Finding Your Best Starting Point in This Book


This book covers the full life cycle of an Analysis Services solution from development to deployment.
If you’re responsible only for certain activities, you can choose to read the chapters
that apply to your situation and skip the remaining chapters. Use the following table to
find your best starting point.

intro

 

 

Introducing Business Intelligence


Business intelligence (BI) is information that has been derived from the data contained in
your organization’s operational systems or external sources. Business intelligence should help
you make better decisions faster. Suppose you are the president of a small, new company,
Adventure Works Cycles, that manufactures and sells bicycles, bicycle components, clothing,
and accessories for North American, European, and Asian markets. Adventure Works needs
to grow, but has limited resources to support expansion. You have decisions to make, and to
make those decisions you must have particular information. You keep up with general business
trends by reading the Wall Street Journal, and you keep a bookmark in your browser
pointed at www.bloomberg.com. This information, along with your experience, enables you
to make subjective, “gut-feeling” decisions. But you want to start making objective, by-the-numbers
decisions. The numbers you need are in your company’s order-processing, accounting,
human resources, and other business systems. You also need to get third-party-provided
market forecasts and exchange rates. You need a tool that will bring together all of this information
and put it at your fingertips. That tool is a BI system. A BI system is the solution for
gathering data from multiple sources, transforming that data so that it is consistent and stored
in a single location, and presenting the information to you for analysis and decision making.


A BI system can have up to five layers:


1. A data source layer
2. A data transformation layer
3. A data storage and retrieval layer
4. An analytical layer
5. A presentation layer

The data source layer is composed of the data in the systems your organization uses to
conduct its day-to-day operations; data in text files, Microsoft Office Excel spreadsheets, or
Microsoft Office Access databases; and data you acquire from external sources. Because this
data is in many different sources, it is extremely difficult to use it to create reports and perform
analysis. The data transformation layer is used to extract the data from the multiple sources,
modify the data so that it is internally consistent, and load it into a data storage system.


The data storage and retrieval layer is a data warehouse that has been created in a relational
database management system. The data warehouse is the system of record. It contains authoritative
numbers for your organization. A mature enterprise data warehouse contains data
related to all aspects of your organization. Your data warehouse is a busy and complex place.
Data loads occur monthly, weekly, daily, or even more frequently. Reports and analytical queries
run day and night. To reduce the burden on the data warehouse and to simplify user access,
data about individual subject areas is extracted from your data warehouse, summarized,
and loaded into data marts. The data marts can be relational databases or they can be multidimensional
OLAP (online analytical processing) databases. Analysis Services is one example
of a multidimensional OLAP database.


The purpose of the analytical layer of your BI system is to turn data into information and
to provide quick and easy access to that information for decision makers. Multidimensional
OLAP databases form the analytical layer of your BI system. When detailed data from the
data warehouse is loaded into a multidimensional OLAP database, summarized values are
precalculated. Because summary values are stored in the database, reports and analytical
queries execute quickly. When data is loaded into a multidimensional OLAP database,
metadata is added to the data. Metadata is data about the data. The metadata in an OLAP
database includes information about relationships and hierarchies in the data, how the data
should be sorted and summarized, and how it should be formatted for presentation. The
metadata in the OLAP database is what turns data into information. Complex calculations
can be created and then stored in the OLAP database. This makes information access easier,
because report and query writers don’t have to repeatedly create calculations, and everyone
in your organization will be able to use the exact same calculation formulas.


Reporting and visualization tools form the presentation layer of your BI system. Applications
in the presentation layer of your BI system can query your data warehouse, data marts, or
multidimensional OLAP databases and present it in a variety of formats. Your BI system can
send reports to your outside sales force and delivery workers’ mobile phones to direct their
daily activities. Analytic workflow diagrams illustrate complex business processes and at each
node show the information decision makers need to make high-valued choices. Interactive
analytical reports with slice-and-dice, pivot, and drilldown capabilities and multidimensional
data visualizations help managers and analysts troubleshoot issues and find information to
help your organization operate more efficiently and take advantage of new opportunities.
Dashboard reports with key performance indicators enable executives to quickly determine
whether the organization is meeting its strategic objectives.

You shouldn’t be dogmatic about what constitutes your BI system. A BI system can have all
the layers mentioned earlier or only a few. For example, if you’re using Analysis Services in a
near-real-time monitoring system, you can load data directly from a manufacturing systems-control
database into your multidimensional OLAP database every few minutes. The presentation
layer of your BI system then reads data from Analysis Services and displays a diagram
showing each machine on the production line and how well it is currently performing compared
to its long-term average. This BI system has no data warehouse and the data transformation
and analytical layers are combined. The complexity of your BI system isn’t important.
What is important is that your BI system provides information that is relevant to the decisions
you need to make, understandable and persuasive, reliable, quick and easy to access and manipulate,
and available when you need it.

  • PingBack from http://asp-net-hosting.simplynetdev.com/new-book-microsoft-sql-server-2008-analysis-services-step-by-step/

  • Hi friends very good book is for the Mssql Hosting. I read it and found that it has very important concepts about the Mssql.I thought that it was very helpful for the learners who wanted to learn about the Mssql.

    Thanks for this g8 book.

  • Unable to install sample database

    following errors are generated, when i run Setup.cmd

    HResult 0x2, Level 16, State 1

    Named Pipes Provider: Could not open a connection to SQL Server [2].

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    and

    HResult 0x2, Level 16, State 1

    Named Pipes Provider: Could not open a connection to SQL Server [2].

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    sql server installed on the local machine, with analysis service Log on as Local service.

    iam loged in to windows with Administrator account.

    is there another way of doing the same thing. which is doen by SSAS_Login.sql

  • Sample database fails to install to SQL2008.  Error message below:

    exec master..sp_attach_db 'SSAS2008SBS', [C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf]

    Msg 5133, Level 16, State 1, Server BIGFRED1, Line 1

    Directory lookup for the file "C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf" failed with the operating system error 5(Access is denied.).

  • Sample database fails to install to SQL2008. Error message below: exec master..sp_attach_db 'SSAS2008SBS', [C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf]

    Msg 5133, Level 16, State 1, Server CHRIS-LAPTOP, Line 1

    Directory lookup for the file "C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf" failed with the operating system error 5(Access is denied.).

  • I got the same problem when trying to install. The error message is: exec master..sp_attach_db 'SSAS2008SBS', [C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf]

    Msg 5133, Level 16, State 1, Server SANDY-PC, Line 1

    Directory lookup for the file "C:\Microsoft Press\Analysis Services 2008 SBS\Setup\SSAS2008SBS.mdf" failed with the operating system error 5(failed to retrieve text for this error. Reason: 15105)

    Mate, you gotta give some support otherwise people will not buy your book or return it like I am about to

  • Well, the support here sucks, I am returning the book today. Last time I buy any books from this author or Microsoft

  • I kept getting the access denied errors.  I'm running Win7 + SQL Server 2008 SP1.  

    I was not sure why I got the error, even though I made sure I had effective permissions within the default folder structure put together by the install CD.  

    To fix the install issue, I copied the Setup.cmd, the modified (per book instructions) SSAS_Login.sql, and the mdf file to a new folder I created off the root of C:  

    Then running Setup.cmd, it attached the DB and ran the login script fine.  Now I'll see if the practice files work when I get to those sections of the book...

    I did notice a lock symbol on the C:\Microsoft Press\Analysis Services 2008 SBS folder, I've run into issues before with being an admin on my own laptop and having access issues when I see this symbol.

    Since Win7 wasn't tested with the book, MAYBE that's why it doesn't work out of the box--but I agree, support for this book does seem to suck if you can't get through the Introduction chapter...

    Cheers if it helps anyone.

  • How it works for me. Delete the log file. Create some directory for example c:\!. Copy attach and mdf files to that directory. Run Managment studio as administrator. Open attach file and change path to c:\!\... Execute.

  • 1) Move the SSAS2008SBS database from

    C:\Microsoft Press\Analysis Services 2008 SBS\Setup

    to

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

    (In case you have the same folders structure, of course, but you will be able to change them properly if youy don´t)

    2) Execute this query:

    USE [master]

    GO

    sp_attach_single_file_db N'SSAS2008SBS', N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SSAS2008SBS.mdf'

    GO

  • I have noticed a lot of people having a problem attaching the database and complaining aboout a missing .ldf file. After you discover the windows login user name(for me it was "Network Service"), you must grant that user name full access or permission to the Microsoft Press folder. After you do this and run the script the .ldf file will be created. The problem is that without permission to the folder SSAS can not connect to SQL, attach the database then create the .ldf file.

  • I just purchased this book in Kindle format, only to discover that there is apparently no method to obtain the database and exercise files (no CD with a kindle book, obviously...) The only web page links to Microsoft in the book are either broken or irrelevant.  

    Is there any support for the Kindle version of this book?  Should I just try to get a refund?

  • Kevin, click "Download Example Code" here: shop.oreilly.com/.../9780735626201.do

  • For 2012 I moved the mdf file SSAS2008SBS.mdf to this folder where SQL Server had access

    C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER_2012\MSSQL\DATA

  • When copying data into 'My Sample Database', writing a query fails as it doesn't recognise data type 202 in "Write a query to specify the data to transfer" in the Specify Table Copy or Query section of the SQL Server Import and Export Wizard.

    Instead use the  "Copy data from one or more tables or views" option and it copies everything

    See this link for details

    connect.microsoft.com/.../sql-server-import-and-export-wizard-does-not-recognise-varchar-and-nvarchar

Page 1 of 2 (17 items) 12
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post