Transcript Document

SQL Server Analysis Services 2005
How to migrate Analysis Services 2000 cubes to Analysis Services 2005,
and how to build Analysis Services 2005 cubes. (45 mins)
Andrew Sadler,
Principal Consultant,
ProClarity UK
17th August 2005
Agenda
•
Overview of project REAL
•
Introduction to UDM
•
Building Up versus Building Down
•
Migration versus Redesign
•
Known Issues
•
Demo
•
―
Migrating Cubes to Microsoft SQL Server 2005
―
Building Cubes using Microsoft SQL Server 2005
Questions
Project REAL
• Microsoft Reference Implementation (Retail)
- Reference Implementation
- End-to-end
- At scale
- Lots of users
• Phase 1 – Migrate packages and cubes
• Phase 2 – Build cubes as best practice for Analysis Services 2005
cubes
• Role of Analysis Services Migration and ProClarity in Project REAL
• For more information on Project REAL:
― http://msdn.microsoft.com/library/default.asp?url=/library/enus/dnsql90/html/SQL05InSrREAL.asp
Unified Dimension Model (UDM)
• Combination of Relational and Analytical worlds
― Attributes and Attribute Hierarchies
― Data Source Views
― Measure Groups
― Perspectives
Attributes and Hierarchies
• Attributes are first-class citizens
• Hierarchies are created from attributes
• Related attributes provide for aggregations
• Hierarchies can be natural or navigational
Data Source Views
• Provide a layer of abstraction
• Can easily leverage multiple data sources
• Multiple fact tables
• Allows you to write the query
• Can create calculated fields
• Used as the basis for the cube not data source
Measure Groups
• Allow measures from multiple fact tables in a single cube with
different levels of granularity and/or dimensionality
• Provide a security mechanism
• Can be grouped in perspectives
Perspectives
• Combination of choices of:
― Attributes
― Hierarchies
― measure groups
― KPIs
― Calc measures/members
• Used to provide context for the users
• Not a security mechanism
Building Up versus Building Down
AS2K Building UP
AS2K5 Building Down
(Adding Context)
Many cubes per DM/DW
Less cubes per DW\DM
Small cubes 6-12 dimensions
Large cubes; with many dimensions;
with many hierarchies
Member properties to help with
reporting issues
Orthogonal attributes provide reporting
flexibility
Virtual cubes to deal with data from one
or more data source
Data source views provide the
extraction layer to present one or more
data sources
Virtual cubes used to handle differing
data granularity of measures
Perspectives and Measure groups are
used to make sense of cube data
Moving cubes from development to
production harder
Mobile cubes (deployment wizard and
scripting)
Migration Wizard - 1
• Does the best it can to recreate the same cube in AS2K5
• Data Source views are automatically created as all tables in use
from underlying data sources
• Cubes are migrated to new cubes not perspectives
• Cubes are not populated
• Virtual Cubes are migrated to cubes, duplicating underlying cubes
and may require some attention
• End result is not “Best Practice” AS2K5 cube
Migration Wizard - 2
• Does not take advantage of most new features:
― Perspectives
― KPIs
― Translations are not created.
• Partitions are magic.
― They appear to work just like I want them to……
• Items not migrated include:
― drill through settings
― remote partitions
― linked cubes
― unary operators
― custom roll ups
• Scripting option
Known Issues
• Additional member properties are added for the next level up in
the hierarchy
― Don’t delete them they are needed for aggregations
• Multiple hierarchies translate to multiple dimensions
• Virtual cubes will duplicate underlying cubes
• Virtual Dimensions convert to hierarchies and data is loaded into
cube
• Virtual cubes do not always pick up associated Data Source Views
(if multiple have been created)
• Almost all columns of dimension tables are added as attributes
• Unary operators and custom rollups are written as MDX
expressions and the column is ignored from 2k5 onwards
SQL Server 2005 – Analysis Services
Have a look …
Questions
Andrew Sadler
Principal Consultant
0208 622 3274
[email protected]
SQL Server Analysis Services 2005
Analysing data in SQL Server Analysis Services 2005
cubes using ProClarity 6.1 (30 mins)
Andrew Sadler,
Principal Consultant,
ProClarity UK
17th August 2005
Agenda
• Understanding the UDM using ProClarity
• Scalability and performance
• Seamless front end migration
• ProClarity on SQL server 2005 demonstration
• Questions
Understanding the UDM
“UDMs will place a lot of data at my fingertips. How do I manage
the amount of data my users see?”
• Advanced Visualisation
― Performance Map
― Decomposition Tree
― Perspective view
• ProClarity “Perspectives” (Dimension Filters)
― ProClarity extends the creation and management of perspectives to
the report author
• KPI Designer and Selector
• Visual KPIs and exception reporting
Scalability and Performance
• Pivot Table Services is ready for the extranet
― “Light” footprint PTS
― Calculations pushed to the server
• Proactive caching allows users to operate low-latency
applications with near-zero management cost
• ProClarity has been optimised to take advantage of improved
performance and scalability in SQL Server 2005
― XML for Analytics provides for lower bandwidth and alleviates
firewall issues enabling higher-performing, manageable extranet
applications
― Server-side query execution improves performance of complex
calculations
― Proactive caching allows users to operate low-latency applications
with near-zero management costs
Seamless Migration
• ProClarity 6.0 was redesigned specifically for SQL Server 2005:
― Dimension tool
― ProClarity perspectives
― KPI goal, status, and trend icons
• Common UI/functionality for both SQL Server 2000 and SQL Server
2005:
― KPIs goal, status, and trend
― Perspectives
― Meta data change tools
― You can use these tools NOW against SQL 2000 or SQL 2005 CTPs
ProClarity on SQL Server 2005
Have a look …
Versions
SQL Server Name
Beta 2
(9.0.823)
April CTP
(9.0.1116.0)
June CTP
(90.1169)
…
ProClarity Name
ProClarity 6.0
(6.0.149.501)
ProClarity 6.1 beta
(6.1.120.602_YukonDev)
ProClarity 6.1 Beta
(6.1.129)+(6.1.134)
Nameless
Resources
• ProClarity 6.1 (aka ‘Yellowstone’) datasheet
― http://www.proclarity.com/files/product_datasheets/yellowstone.
pdf
• Analysis Services 2005 Migration Whitepaper
― http://www.proclarity.com/files/whitepapers/AS2005_Migration_Pr
oClarity.pdf
• Webcast: ‘Practical Tips For a Seamless Migration to SQL Server
2005’
― http://www.dmreview.com/web/reg_proclarity05.cfm
• Request an evaluation copy of ProClarity 6.1 (aka ‘Yellowstone’)
― https://www.proclarity.com/yellowstone/login.asp?
Understanding – ProClarity Worldwide Conference
• September 13th – 16th
• Langham Hotel, London
• Conference
― Speakers
• Microsoft
• Mark Whitehorn
• Case Studies
• Training
― Technical
― Partner
― Insight Exchange
Summary & Conclusion
•
Microsoft Business Intelligence Positioning and Roadmap
•
Migrating cubes to Microsoft SQL Server 2005
•
Building Cubes using Microsoft SQL Server 2005
•
ProClarity running with Microsoft SQL Server 2005
Questions
Andrew Sadler
Principal Consultant
0208 622 3274
[email protected]