book

Microsoft® SQL Server™ 2005 Analysis Services Step by Step

by Reed Jacobsen, Stacia Misner, Hitachi Consulting

March 2006

Beginner

400 pages

11h 35m

English

Microsoft Press

Read now

Unlock full access

Microsoft® SQL Server™ 2005 Analysis Services Step by Step
A Note Regarding Supplemental Files
Introduction
Finding Your Best Starting PointAbout the Companion CD-ROMSystem RequirementsInstalling and Using the Sample FilesConventions and Features in This Book
I. Getting Started with Analysis Services
1. Understanding Business Intelligence and Data Warehousing
Introducing Business IntelligenceReviewing Data Warehousing ConceptsThe Purpose of a Data WarehouseThe Structure of a Dimensional DatabaseA Fact TableDimension TablesAttributes That Change over TimeStars and SnowflakesAlternative Dimension Table StructuresChapter 1 Quick Reference
2. Understanding OLAP and Analysis Services
Understanding OLAPConsistently Fast ResponseMetadata-Based QueriesSpreadsheet-Style FormulasUnderstanding Analysis ServicesAnalysis Services and SpeedAnalysis Services and MetadataDimension MetadataCube MetadataAnalysis Services FormulasFinance FormulasAnalysis Services ToolsChapter 2 Quick Reference
3. Building Your First Cube
Exploring Business Intelligence Development StudioExamining the Contents of an Analysis Services ProjectOpen the SSAS Step by Step solutionExploring Menu CommandsExplore menu commandsPreparing to Create a CubeReviewing the Analysis RequirementsCreating a New Analysis Services ProjectStart a new Analysis Services projectCreating a CubeUsing the Cube Wizard Without a Data SourceBuild a new cubeSpecify a data sourceAdd measuresAdd dimensionsDefine time periodsAdd a special calendarRelate dimensions to measuresFinish the Cube WizardReviewing the Cube Structure in the Cube DesignerReview and modify the cube structureGenerating a SchemaUsing the Schema Generation WizardCreate a relational schemaLoading Data into the Relational SchemaLoading the schema with dataProcessing and Browsing a CubeDeploying and Processing a CubeDeploy and process an Analysis Services projectBrowsing a CubeBrowse a cubeChapter 3 Quick Reference
II. Design Fundamentals
4. Designing Dimensions
Reviewing the Data Warehouse StructureBuilding a Standard DimensionAdding a Data SourceAdd a data source to an Analysis Services projectCreating a Data Source ViewUse the Data Source View WizardUsing the Dimension WizardCreate a standard dimensionDeploying a DimensionDeploy a dimension to the Analysis ServerChanging Attribute PropertiesDisable the AttributeHierarchyEnabled propertyWorking with a Time DimensionModifying a Data Source ViewAdd tables to a data source viewCreating a Time DimensionBuild a time dimensionWorking with Role-Playing DimensionsAdd an object to a projectAdd role-playing time dimensions to a cubeCreating a Parent-Child DimensionAdding an Employee DimensionCreate a parent-child dimensionTotaling Data for Non–Leaf-Level Data MembersSet the MembersWithData propertyManaging Levels within a Parent-Child DimensionRemove the All level from a hierarchyCreate a level naming template for a parent-child hierarchyChapter 4 Quick Reference
5. Designing Measure Groups and Measures
Adding Measure Groups to a CubeBuilding a CubeUse the Cube WizardChanging Properties for Measure Groups and MeasuresRename measure groupsFormat measuresSpecifying Dimension UsageReview the relationship between cube dimensions and measure groupsBrowsing Multiple Measure GroupsExamine the interaction between attributes and measure groups in a cubeAggregating Semiadditive MeasuresAdding a Measure Group to an Existing CubeAdd a measure group to a cubeUsing a Semiadditive Aggregate FunctionUse the LastNonEmpty aggregate functionCalculating Distinct CountsAdding a Distinct Count MeasureCreating Simple CalculationsAdding a Calculation to a CubeAdd a weighted average calculationApplying Conditional FormattingAdd a color expression to a calculationChapter 5 Quick Reference

6. Working with a Finance Measure Group
Designing an Account DimensionWorking with Account IntelligenceCreate an Account dimensionUsing Unary OperatorsBrowse the default aggregations of an Account dimensionSet the UnaryOperatorColumn propertyAggregating by AccountUse the LastNonEmpty function with an Account dimensionUse the ByAccount aggregate functionDesigning Nonadditive Financial MeasuresCreating a Nonadditive MeasureAdd an Operating Profit Per Employee measureChapter 6 Quick Reference
7. Designing Aggregations and Hierarchies
Understanding Aggregation DesignUsing the Aggregation Design WizardDesign aggregations for a cube partitionInspecting AggregationsExamine aggregations for a dimension in a cube partitionChanging Partition CountsEdit fact table and granularity attribute countsAdding Attributes to the Aggregation DesignSet the AggregationUsage property for an attributeDesigning User HierarchiesAdding a User HierarchyAdd user hierarchies to a dimensionAggregating User HierarchiesUpdate the aggregation design with attributes from a user hierarchyOptimizing AggregationsUsing the Query LogPopulate the query logViewing Usage DataBrowse the query logUsing the Usage-Based Optimization WizardDesign usage-based aggregationsMaintaining the Query LogDelete records from the query logChapter 7 Quick Reference
III. Advanced Design
8. Using MDX
Creating Tuple-Based Calculated MembersCreating an MDX Calculation for Percent of TotalAdd a Percent of Total calculation to a cubeCreating an MDX Calculation for Percent of ParentAdd a Percent of Parent calculation to a cubeQuerying with MDXExecuting MDX QueriesUse SQL Server Management Studio to execute MDX queriesWorking with Basic MDX QueriesUse sets and set functions in MDX queriesDesigning Custom MembersCreating a Calculated Member Using a Set-Based FunctionUse the Avg functionCreating Cumulative CalculationsCompare the Sum and PeriodsToDate functionsWorking with MDX ScriptsManaging the Sequence of CalculationsOrganize scriptsAdding a Script AssignmentOverride cube values with a script assignmentDeveloping Key Performance IndicatorsComparing Cube Values to GoalsCreate a simple key performance indicatorUsing MDX Expressions with Key Performance IndicatorsCreate a ratio key performance indicatorChapter 8 Quick Reference
9. Exploring Special Features
Defining Dimension RelationshipsUsing a Referenced Relationship TypeCreate a referenced dimensionUsing a Many-to-Many Relationship TypeBuild a many-to-many dimension relationshipSupporting Currency ConversionsLocalizing CubesAdding TranslationsAdd translated captions and attribute valuesBrowsing TranslationsChange language in the cube browserOrganizing Information with Folders and PerspectivesOrganizing MeasuresUse display folders for measures and calculationsUsing PerspectivesCreate and browse a perspectiveChapter 9 Quick Reference
10. Interacting with Cubes
Implementing ActionsUsing Standard ActionsCreate a URL actionLinking to ReportsCreate a reporting actionAdding DrillthroughCreate a drillthrough actionUsing WritebackWrite-Enabling a DimensionCreate a write-enabled dimensionDynamically Adding Members to a DimensionWrite back a new dimension memberModifying the Cube Structure for WritebackCreate a unit forecast measure groupWriting Values Back to a CubeWrite back valuesChapter 10 Quick Reference
IV. Production Management
11. Implementing Security
Using Role-Based SecurityCreating Security RolesCreate sample users and groupsCreate an Analysis Services role for all usersManaging RolesCopy rolesApplying Security to a DimensionRestricting Access to a DimensionFully restrict a dimensionRestricting Access to Specific Members of a DimensionRestrict the members of a dimensionControlling Visual Totals for a DimensionEnable visual totalsDefining a Default Member for a DimensionCreate a default dimension member for a roleSecuring Data at the Cell LevelPreventing Values in Cells from Being ReadAdd an MDX expression to enable read permissions for specific cube cellsAllowing Users to Write to CellsEnable user writebackSetting Administration SecurityCreating Security Roles for ProcessingCreate a database role with cube processing permissionsChapter 11 Quick Reference
12. Managing Partitions and Database Processing
Managing Very Large DatabasesUnderstanding Partition StrategiesCreating PartitionsCreate partitions based on fact table filtersMerging PartitionsMerge two partitionsWorking with StorageUnderstanding Analysis Services Storage ModesSetting Storage OptionsSet storage options for sample cubesChanging Data in a WarehouseBrowse data after updating the warehouseManaging OLAP ProcessingProcessing a DimensionUpdate a dimensionProcessing a CubeUpdate data in a cube incrementallyConfiguring Proactive CachingSet caching and latency optionsMonitoring Cube ActivityProfiling Analysis Services QueriesUse SQL Server Profiler to monitor queriesUsing the Performance MonitorMonitor a performance counterChapter 12 Quick Reference
13. Managing Deployment
Reviewing Deployment OptionsBuilding a DatabaseUse the Build commandDeploying a DatabaseUse the Analysis Services Deployment WizardProcessing a DatabaseUse SQL Server Management Studio to process a databaseManaging Database Objects ProgrammaticallyWorking with XMLA ScriptsCreate and execute a processing scriptAutomating Database ProcessingCreating a SQL Server Integration Services PackageCreate a packageUsing the Analysis Services Processing TaskCreate an Analysis Services Processing TaskHandling Task FailuresSend an e-mail message if the task failsScheduling a SQL Server Integration Services PackageCreate a package schedulePlanning for Disaster and RecoveryBacking Up an Analysis Services DatabaseBack up a databaseRestoring an Analysis Services DatabaseRestore a databaseChapter 13 Quick Reference
Glossary
About the Authors
Contributing Authors
Index
About the Authors
Copyright

Content preview from Microsoft® SQL Server™ 2005 Analysis Services Step by Step

Chapter 4. Designing Dimensions

After completing this chapter, you will be able to:

Define a data source for an Analysis Services project.
Create a data source view (DSV).
Use the Dimension Wizard to build standard, time, and parent-child dimensions.
Deploy Analysis Services database objects.
Change dimension properties.
Work with special properties of parent-child dimensions.

In Chapter 3, you created dimensions from a template and then used the Schema Generation Wizard to build tables in a Structured Query Language (SQL) Server database to correspond to the dimension design. In this chapter, you’ll use the Dimension Wizard to create dimensions from existing tables and work with the Dimension Designer to review a dimension’s structure, modify its properties, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft® SQL Server® 2008 Analysis Services Step by Step

Publisher Resources

ISBN: 0735621993Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® SQL Server™ 2005 Analysis Services Step by Step

by Reed Jacobsen, Stacia Misner, Hitachi Consulting

Chapter 4. Designing Dimensions

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.