1. SQL Server 2005
I. User Experience Enhancements
Microsoft SQL Server 2005 Analysis Services (SSAS) includes the following user experience enhancements and new features.
1) Business Intelligence Development Studio
Analysis Services is fully integrated with the Microsoft Visual Studio 2005 development environment for creating end-to-end business intelligence solutions. You can use BI Development Studio to create and maintain Analysis Services components, such as relational data source, OLAP, and data mining objects. For more information about BI Development Studio features, see New Business Intelligence Development Studio.
1) SQL Server Management Studio
SQL Server Management Studio is a fully integrated management environment for administrators and developers, replacing the functionality of Analysis Manager in earlier versions of Analysis Services. You can use Management Studio to manage instances of Analysis Services, to browse and administer Analysis Services databases and objects, and to develop, execute, and analyze Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and XML for Analysis (XMLA) queries and scripts. For more information about Management Studio features, see New SQL Server Management Studio.
2) Analysis Services Wizards - several
Analysis Services wizards have been redesigned for the development environment provided by BI Development Studio. These wizards let you quickly create or enhance Analysis Services objects such as data source views, dimensions, cubes, and data mining models. Additionally, new wizards have been added to Analysis Services to support new features like deployment. For more information about the new and enhanced wizards in Analysis Services, see New Analysis Services Designers and Wizards (SSAS).
3) Analysis Services Templates
Analysis Services provides templates in BI Development Studio for Analysis Services objects such as cubes, dimensions, Key Performance Indicators (KPIs), calculated members, named sets, calculated cells, actions, Multidimensional Expressions (MDX) statements, and Data Mining Extensions (DMX) statements. Analysis Services also provides templates in SQL Server Management Studio for MDX and DMX queries and statements. Templates are stored in an XML-based format, which lets you easily define and create additional templates.
II. Server Enhancements
Microsoft SQL Server 2005 Analysis Services (SSAS) introduces the following service enhancements and new features.
1) Multi-Instance Support
Up to 50 instances of the Analysis Services service from Microsoft SQL Server 2005 Enterprise Edition can be installed on one computer; up to 16 instances of the Analysis Services service can be installed from other editions of SQL Server 2005. Earlier versions of Analysis Services did not support multiple instances. For more information about instances of Analysis Services, see Managing Instances of Analysis Services.
2) Failover Clustering
Instances of the Analysis Services service now support 8-node failover clusters on 32-bit systems, and 4-node clusters on 64-bit systems. Earlier versions of Analysis Services did not support failover clustering. For more information, see Failover Clustering.
3) XML Support
The Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages, in either an explicitly declared or implicitly allocated session that supports statefulness. Earlier versions of Analysis Services supported XMLA only through the use of the XMLA SDK, which acts as a bridge between XMLA and OLE DB. For more information about XMLA, see XML for Analysis (XMLA).
4) Proactive Caching
The Analysis Services service uses proactive caching to increase the performance of dimensions, partitions, and aggregations. Proactive caching combines the benefits of relational OLAP (ROLAP) and multidimensional OLAP (MOLAP). When proactive caching is enabled, the Analysis Services service uses a background process to cache ROLAP data in MOLAP storage for query purposes; when the relational data changes, the Analysis Services service retrieves the data from ROLAP storage while the MOLAP cache is rebuilt in the background. Analysis Services supports three different types of notification mechanisms, in addition to several settings with which you can fine-tune proactive caching functionality for real-time, low-latency, or traditional access to underlying data. For more information, seeProactive Caching.
5) Scripting Support
Databases and subordinate objects on an instance of Analysis Services can be scripted by using the Analysis Services Scripting Language (ASSL), an XML-based syntax used with XMLA to administer Analysis Services. For more information about ASSL, see Analysis Services Scripting Language (ASSL).
6) Language and Collation Support
The Analysis Services service supports language and collation settings at both the instance level and the database level. You can specify language and collation during installation for an instance of Analysis Services, and also for each database, cube, dimension, and mining structure on the instance. For more information about language and collation support in Analysis Services, see International Considerations for Analysis Services.
Additionally, databases and subordinate objects can support multiple languages through the use of translations. Client applications that specify a language for their sessions can receive data and metadata from a particular object in the specified language or in the default language for that object if the specified language is not available. For more information about translations, see Cube Translations.
7) Processing Support
The Analysis Services service now provides additional flexibility in processing cubes, measure groups, partitions, dimensions, and mining models, including direct support of parallel processing. Objects on an instance of Analysis Services can be processed in parallel in a single batch transaction. For more information about processing support in Analysis Services, see Processing in Analysis Services.
8) Referential Integrity Issues
When processing fact tables, earlier versions of Analysis Services ignored rows that contained an undefined member for a dimension. When rows were ignored, the total members in the cube did not match the expected total for the data warehouse, causing Analysis Services to incorrectly aggregate information. The Analysis Services service can now use settings for each hierarchy in a dimension to determine how to handle referential integrity issues. Such rows can now be associated with a placeholder member, called an Unknown member, in a hierarchy when a fact table row has a null value for a particular hierarchy. For more information about Unknown member support, see Configuring User-Defined Hierarchy Properties.
III. Cube Enhancements
1) Key Performance Indicators
Analysis Services provides customizable business metrics called Key Performance Indicators (KPIs), which consist of relevant attributes and associated calculations that generate industry-standard goals and benchmarks. A KPI collection includes a measure, a goal, display properties, and variances. Companies use KPIs to track performance and improve decision-making. For more information, see Key Performance Indicators.
2) Multiple Fact Tables
Analysis Services now supports multiple fact tables within a single cube through the use of measure groups. Measures from a single fact table are grouped together in a measure group, and a single cube can contain multiple measure groups. Measure groups are also used to associate dimensions with measures, in order to hierarchically organize the measures that the measure group contains. For more information, see Unified Dimensional Model.
3) Business Intelligence Enhancements
Analysis Services provides business intelligence enhancements such as currency conversion, dimension writeback, and account and time intelligence, all of which can be added to cubes. For more information, see Defining Time Intelligence Calculations using the Business Intelligence Wizard and Enhancing a Dimension using the Business Intelligence Wizard.
4) Perspectives
Analysis Services now includes perspectives, which are pre-defined viewable subsets of cube metadata. Similar to a view in SQL Server 2005, a perspective can reduce the perceived complexity of a cube in Analysis Services by letting you define a viewable subset of the cube. The perspective controls how the objects contained by a cube, including measure groups, measures, dimensions, hierarchies, attributes, KPIs, actions, and calculations, are visible to a business intelligence application. You can use perspectives to provide a focused, business-specific or application-specific viewpoint on a cube. For more information, see Perspectives.
5) Semi-Additive Measures
Analysis Services includes more support for aggregating measures across some dimensions and not others. The Analysis Services engine lets you specify the aggregation function to use for each dimension, and then infers the appropriate aggregation function for each measure based on the account type of the dimension. Alternatively, you can specify aggregation functions explicitly for each measure for a particular dimension. Semi-additive measures enable aggregation for an account dimension to be set by account. Standard account types that correspond to particular aggregation methods can be assigned to different accounts. Business users can then set up cubes that reflect a company's account structure, without writing custom rollup formulas. For more information, see Measures and Measure Groups.
IV. Dimension Enhancements
1) Attributes
In earlier versions of Analysis Services, dimensions were based directly on the levels in a hierarchy. In SQL Server 2005 Analysis Services, dimensions are now based on attributes, which correspond to the columns in the tables of a dimension. Each attribute contains the members of a dimension table column, such as Cities or Locales in a Geography dimension, or Days or Months in a Time dimension. This new architecture separates the structural features of a dimension from its navigational features; attributes provide the structure of a dimension and the levels being used for navigation of the dimension. For more information, see Attributes and Attribute Hierarchies.
2) Multiple Hierarchies
Analysis Services now supports multiple hierarchies in a single dimension. Previously, hierarchies for a dimension were in fact separate dimensions, related to the main dimension only by an identifying naming convention. In SQL Server 2005 Analysis Services, however, dimensions are no longer described by their hierarchical structure. Instead, attributes that can easily be assembled into hierarchies are used, and different hierarchical structures can be supported within the same dimension. For more information, see User-Defined Hierarchies.
3) Many-to-Many Dimension Relationships
Analysis Services now supports many-to-many relationships between fact tables and dimension tables by using association tables. Many-to-many dimension relationships expand the dimensional model beyond the classic star schema, and support complex analytics even when dimensions are not directly related to a fact table. For more information, see Dimension Relationships.
4) Reference Dimension Relationships
Analysis Services supports reference dimensions through the use of reference dimension relationships, in which a reference dimension is indirectly coupled to a measure group by another dimension. Using reference dimension relationships, you can associate a reference dimension with a cube without creating a snowflake dimension. You can chain any number of reference dimensions together. For more information, see Dimension Relationships.
5) Fact Dimension Relationships
Analysis Services now supports fact dimensions, also known as degenerate dimensions, through the use of fact dimension relationships. A fact dimension is a dimension whose attributes are drawn from a fact table. For more information, see Dimension Relationships.
6) Role-Playing Dimension Relationships
Analysis Services now supports role-playing dimension relationships, in which multiple relationships between a dimension table and a fact table can be expressed by using a single dimension. In earlier versions of Analysis Services, each relationship between a fact table and a dimension table required a separate dimension. For more information, see Dimension Relationships.
7) Simplified Dimension Types – standard or linked
In Analysis Services, dimensions have been simplified: two dimension types, standard and linked, now replace the four dimension varieties in SQL Server 2000 Analysis Services. A standard dimension is a dimension in the same database as the cube; a linked dimension is in a different database from the cube. Additional dimension characteristics are now supported through dimension relationships, hierarchies, and attributes. For more information, see Dimensions (Analysis Services).
8) Linked Measure Groups and Dimensions
In Analysis Services you can bring together data from different data sources by linking a cube to a measure group in another cube that is stored either in the same database or in a different database on an instance of Analysis Services. You can also link a cube to a dimension in another database. After you create a link, users can query data and metadata in the linked object, just as they do in any similar object that is native to the cube. For more information, see Linked Measure Groups and Linked Dimensions.
9) Member Groups No Longer Required
In SQL Server 2000 Analysis Services, member groups were necessary to accommodate members with more than 64,000 children. Analysis Services now removes this restriction; member groups are no longer necessary.
10) Dimension Size Virtually Unlimited
Analysis Services no longer depends on memory-resident storage of dimensions. Now, dimension data and metadata is loaded into memory only when it is needed, and the Analysis Services engine can easily handle dimensions of virtually unlimited size.
V. Development Enhancements
1) Microsoft .NET Framework Support
Analysis Services is now fully integrated with the Microsoft .NET Framework, which includes full XML and SOAP support, support for languages such as C# and Microsoft Visual Basic in stored procedures, and managed support for server administration and client access. Analysis Services integration with the .NET Framework provides additional flexibility for business needs by using open standards on a secure platform to support business intelligence applications.
2) Stored Procedures
Analysis Services provides more extensibility and programmability in stored procedures, external routines in programming languages such as C#, C++, or Visual Basic that you can use to extend Analysis Services functionality. Stored procedures provide the benefits of cross-language integration and exception handling, versioning, and deployment support. In Analysis Services, you can use stored procedures to add programming modules to a cube or a database, to extend the functionality provided by the Analysis Services engine. Stored procedures accept input parameters, and can access, create, and return any Analysis Services object. They can also return a status value to a calling procedure or a batch execution to indicate success or failure. Stored procedures replace user-defined functions in earlier versions of Analysis Services.
3) Multidimensional Expressions Enhancements
Analysis Services enhances the Multidimensional Expressions (MDX) language by adding support for scripting, scope and context control, and enhanced subcube manipulation. MDX provides additional intrinsic functions, seamless integration of user-defined functions, enhanced data definition language (DDL) statements, and new data manipulation language (DML) statements. For more information about the MDX language, see Multidimensional Expressions (MDX) Reference.
4) XML for Analysis
In Microsoft SQL Server 2000 Analysis Services, the XML for Analysis (XMLA) 1.0 specification was supported only through the use of the Microsoft XML for Analysis Provider, an OLE DB provider that translated XMLA requests when communicating with an Analysis server computer. In SQL Server 2005 Analysis Services, the Analysis Services engine supports the XMLA 1.1 specification directly, without the need for an additional OLE DB provider. Because XMLA requests can be sent by a client application directly to the Analysis Services engine, you can develop client applications that have less overhead and greater performance. For more information about Analysis Services support for XMLA, see XML for Analysis (XMLA).
5) Analysis Services Scripting Language
Analysis Services now includes the Analysis Services Scripting Language (ASSL), an XML-based syntax used with XMLA to administer Analysis Services and SQL Server Management Studio, to administer an instance of Analysis Services, and to create, delete, or modify objects on an instance of Analysis Services without the need for an object model or an OLE DB provider. For more information about ASSL, see Analysis Services Scripting Language (ASSL).
6) Analysis Management Objects
Analysis Management Objects (AMO) provides a .NET Framework object model that client applications can use to administer an instance of Analysis Services. AMO replaces the Decision Support Objects (DSO) object model in earlier versions of Analysis Services, although DSO is still supported for compatibility purposes. AMO uses XMLA and ASSL when communicating with an instance of Analysis Services, and the generated scripts can be captured for storage or for source control purposes. For more information, see Analysis Management Objects (AMO).
7) ADOMD.NET
Analysis Services introduces ADOMD.NET, a set of .NET Framework classes that can be used in stored procedures and by client applications to access and work with Analysis Services objects and data. While ADOMD.NET functionality was available for SQL Server 2000 Analysis Services as part of the ADOMD.NET SDK, ADOMD.NET is now fully integrated into Analysis Services. For more information, see ADOMD.NET.
VI.
Management Enhancements
1) Deployment Engine
Analysis Services now includes an engine for use in deploying Analysis Services projects and solutions. Earlier versions of Analysis Services worked directly with the data and metadata on an instance of Analysis Services. During the development process, you can use the deployment engine to deploy new projects to a test instance of Analysis Services; when your development is complete, you can then use the engine to deploy the projects to a production instance of Analysis Services. You can use the deployment engine to synchronize metadata between a new or updated Analysis Services project in Business Intelligence Development Studio and an existing instance of Analysis Services or to overwrite metadata from one instance to another. You can also use the deployment engine at the command prompt to provide additional flexibility when you deploy Analysis Services projects and solutions. For more information about Analysis Services deployment, see Deploying an Analysis Services Project.
2) Security
Analysis Services provides increased security features, including better control over access and manipulation of data on an instance of Analysis Services, encryption for information stored in Analysis Services databases, and better monitoring tools for observing client access and requests to Analysis Services.
3) SQL Server Profiler Integration
Analysis Services now supports SQL Server Profiler for monitoring and capturing any events that are generated by an instance of Analysis Services. With SQL Server Profiler you can display data about captured events to the screen, or you can capture and save data about each event to a file or SQL Server table for future analysis or playback. For example, you can use SQL Server Profiler to monitor a production environment to see which Multidimensional Expressions (MDX) queries are executing too slowly and are hampering performance. For more information about SQL Server Profiler, see Introducing SQL Server Profiler.
VII. Data Mining Enhancements
1) Microsoft Naive Bayes Algorithm
The Microsoft Naive Bayes algorithm is a classification algorithm that is quick to build and that works well for predictive modeling. This algorithm is a good option for exploring the data between input columns and predictable columns, and for discovering the relationships between these columns. For more information, seeMicrosoft Naive Bayes Algorithm.
2) Microsoft Association Algorithm
The Microsoft Association algorithm builds rules that describe which items are most likely to appear together in a transaction. You can use the rules to predict the presence of an item based on the presence of other items in a transaction. For more information, see Microsoft Association Algorithm.
3) Microsoft Sequence Clustering Algorithm
The Microsoft Sequence Clustering algorithm, a combination of sequence analysis and clustering, identifies clusters of similarly ordered events in a sequence. You can use the clusters to predict the likely ordering of events in a sequence based on known characteristics. For more information, see Microsoft Sequence Clustering Algorithm.
4) Microsoft Time Series Algorithm
The Microsoft Time Series algorithm uses a linear regression decision tree approach to analyze time-related data, such as monthly sales data or yearly profits. You can use the patterns that the algorithm discovers to predict values for future time steps. For more information, see Microsoft Time Series Algorithm.
5) Microsoft Neural Network Algorithm
The Microsoft Neural Network algorithm creates classification and regression mining models by constructing a multilayer perceptron network of neurons, providing support for nonlinear models that are too complex to derive by using other algorithms. For more information, see Microsoft Neural Network Algorithm (SSAS).
6) Microsoft Logistic Regression Algorithm
The Microsoft Logistic Regression algorithm provides logistic regression support for more business flexibility. For more information, see Microsoft Logistic Regression Algorithm.
7) Microsoft Decision Trees Algorithm Enhancements
You can now use the Microsoft Decision Trees algorithm with a continuous attribute, such as time, as a predictable column. For more information, see Microsoft Decision Trees Algorithm.
8) Microsoft Linear Regression Algorithm
The Microsoft Linear Regression algorithm provides linear regression support for more business flexibility. For more information, see Microsoft Linear Regression Algorithm.
9) Mining
Model Wizard
The Data Mining Wizard defines a mining structure and mining model for an Analysis Services project. You can use the wizard to create new mining structures based on either relational or multidimensional data that can be modified later by using Data Mining Designer. For more information, see Data Mining Wizard.
10) Data
Mining Designer
You can use Data Mining Designer in Business Intelligence Development Studio to modify the mining structure and any mining models that you defined in the Data Mining Wizard. You can also use Data Mining Designer to create additional mining models based on the mining structure, to browse existing mining models by using viewers, to compare mining models, and to build predictions based on the mining models. For more information, see Data Mining Designer.
11) SQL Server Integration Services Support
Several tasks have been added to Microsoft SQL Server 2005 Integration Services (SSIS) that can be used to create a complete data mining solution. By using Integration Services transformations, you can modify data before you create a mining model, create and process mining models, and run prediction queries against existing data mining models.
2. SQL SERVER 2008
I. Aggregation Design Improvements
Analysis Services includes the following improvements to designing aggregations:
1) New Aggregation designer.
A new Aggregation designer makes it easier to browse and modify aggregation designs. Aggregation designs are now shown grouped by measure group. A new view for manual aggregation design is now available for advanced users.
2) Simplified and enhanced Aggregation Design and Usage-Based Optimization Wizards.
These updated wizards let you modify the storage settings for aggregations in one or more partitions at a time and more easily set aggregation usage settings. The Usage-Based Optimization Wizard now also lets you append new aggregations to an existing aggregation.
3) New AMO Warnings.
These new warning messages alert users when they depart from aggregation design best practices.
II. Cube Design Improvements
The cube wizard has been simplified and enhanced. The improvements help you create better cubes in fewer steps.
III. Dimension Design Improvements
Analysis Services includes the following improvements to designing dimensions:
1) New Attribute Relationship designer.
The dimension editor has a new Attribute Relationship designer that makes it easier to browse and modify attribute relationships.
2) New AMO Warnings.
These new warning messages alert users when they depart from design best practices or make logical errors in database design.
3) Simplified and enhanced Dimension Wizard.
This latest version of the wizard auto-detects parent-child hierarchies, provides safer default error configuration, and supports specification of member properties.
4) New Key Columns dialog box.
This new dialog box makes editing key columns easier.
5) Key column support in the Properties Panel.
Key columns can now be edited in the Properties panel.
6) Updated Dimension Structure tab.
This tab now works with the new Attribute Relationship designer and is simpler to use, which makes modifying attributes and hierarchies easier.
IV. Backup and Restore Improvements
The backup and restore functionality in Analysis Services has a new storage structure and enhanced performance in all backup and restore scenarios.
1) Improved Storage Structure
The new storage structure provides a more robust repository for the archived database. By using the new storage structure, there is no practical limit to the size of the database file, nor is there a limit to the number of files that a database can have.
2) Improved Performance
The new backup and restore functionality achieves increased performance. Tests on different sized databases and with various numbers of files have shown significant performance improvements. To obtain actual values that are based on your specific needs, we encourage you to perform your own testing against your own database.
V. Analysis Services Personalization Extensions
Analysis Services personalization extensions enable developers to create new Analysis Services objects and functionality and to provide these objects and functionality dynamically in the context of the user session. Developers do not have to create detailed specifications about where or how to find the extended functionality. Instead, developers can share these new objects and functionality immediately with both end users and other developers.
For a sample that illustrates how to create user-based personalizations, see Readme for Analysis Services Personalization Extensions Sample.
VI. New Samples Location
Books Online no longer includes SQL Server sample databases and sample applications. These sample databases and sample applications are now available on the SQL Server Samples Web site. This Web site makes it easier for users to find these samples, and provides additional new samples that are related to Microsoft SQL Server and Business Intelligence. On the SQL Server Samples Web site, you can do the following:
· Browse through samples contributed by developers, users, and the Microsoft Most Valuable Professional (MVP) community.
· Download both sample databases and code projects.
· View or participate in a discussion area where you can report issues and ask questions about the samples for each technology area.
3. SQL SERVER 2008R2
I. What's New (Analysis Services - Data Mining) – NO CHANGE
II. What's New (Analysis Services - Multidimensional Database)
1) Integration of Analysis Services with SharePoint
SQL Server 2008 R2 offers new business intelligence capabilities that provide flexible yet powerful self-service analytics, complementing traditional OLAP and data mining solutions. Self-service business intelligence is comprised of the following products:
1. PowerPivot for Sharepoint
A new version of Analysis Services that can be hosted within a Sharepoint farm. The instance that is hosted in Sharepoint is a highly modified version of Analysis Services with a new in-memory storage method that loads data on-demand and handles usage and resource demands without tuning. The hosted instance also includes a midtier web service that controls data refresh, data access, and monitoring.
2. PowerPivot for Excel
A client add-in that can be installed with Excel 2010. This add-in provides tools for building multidimensional data sets in Excel. The add-in combines a compressed data store, provided by an in-memory instance of Analysis Services, with existing Excel data visualization tools such as PivotTables and PivotCharts.
3. DAX
PowerPivot for Excel includes a new expression language, Data Analysis Expressions, that lets you easily create sophisticated calculations, use time intelligence, and perform lookups.
For
more information, see PowerPivot
for SharePoint.
1) New Samples Location and PowerPivot Samples
Books Online no longer includes SQL Server sample databases and sample applications. These sample databases and sample applications are now available on the SQL Server Samples Web site. This Web site makes it easier for users to find these samples, and provides additional new samples that are related to Microsoft SQL Server and Business Intelligence.
4. SQL SERVER 2012 and SP1
I. SQL Server 2012
1) Server Instance and Server Monitoring
1. Server Modes for Analysis Services Instances: Multidimensional, Tabular, and SharePoint
This release adds a server mode concept to an Analysis Services installation. An instance is always installed in one of three modes that determines the memory management and storage engines used to query and process data. Server modes include Multidimensional and Data Mining, SharePoint, and Tabular. For more information, see Determine the Server Mode of an Analysis Services Instance
2. xVelocity In-Memory Analytics Engine (VertiPaq) for Tabular Model Databases
xVelocity in-memory analytics
engine (VertiPaq) is an Analysis Services engine that
services tabular model databases. The xVelocity
engine uses in-memory storage and performs calculations that aggregate and
manipulate data at the time it is requested. In contrast with the previous
release, where the xVelocity engine was only
available (as VertiPaq engine) via PowerPivot for
SharePoint, you can now use the xVelocity engine on a
standalone Analysis Services instance with no dependency on SharePoint.
To use the xVelocity
engine, you install Analysis Services in a new Tabular server mode that lets
you run tabular model databases on that instance.
3. Schema Rowsets for Analysis Services in Tabular Mode
New schema rowsets and DMV query interfaces have been added in this release to support the xVelocity engine and tabular models. You can use DMVs to discover tabular model objects and their properties. DMVs contain information that you can use to plan ahead and deduce the performance characteristics of objects you have created. For example, you can use the DISCOVER_CALC_DEPENDENCY rowset to trace dependencies in columns, measures, and formulas, making it easier to troubleshoot complex formulas. Other rowsets help you find the columns used in perspectives, or determine data types and storage characteristics. For more information about new rowsets that support tabular mode, see Tabular Model Data Access. To learn more about DMV query interfaces, see Use Dynamic Management Views (DMVs) to Monitor Analysis Services.
4. Event Tracing Infrastructure
The event tracing infrastructure is extended to support the new SQL Server Extended Events Framework. For more information, see Use SQL Server Extended Events (XEvents) to Monitor Analysis Services.
2) Tabular Modeling
1. Tabular Projects in SQL Server Data Tools
The Tabular Model Designer is now integrated
with SQL Server Data Tools (SSDT). For more information about authoring tabular
projects by using SQL Server Data Tools (SSDT), see Tabular
Model Solutions (SSAS Tabular)
Also included with this release is the
Tabular Modeling Adventure Works Tutorial. This tutorial guides BI software
professionals through creating a new tabular model project in SQL Server Data
Tools (SSDT), importing data from the AdventureWorksDW2012 sample database,
adding relationships, calculations, perspectives, roles, and hierarchies, and
then deploying the model. For more information, see Tabular
Modeling (Adventure Works Tutorial).
2. Tabular Database Administration in SQL Server Management Studio
This release adds administrative support for tabular model databases that you deploy to a standalone Analysis Services server. You can configure role-based security, use backup and restore commands, attach and detach commands, create partitions, and set server properties. For more information, see Tabular Model Databases (SSAS Tabular).
3. Tabular Model Designer Diagram View
This release introduces the Tabular Model Designer Diagram View. The diagram view displays tables, with relationships between them, in a graphical format. Columns, measures, hierarchies, and KPIs can be filtered, and you can choose to view the model using a defined perspective. For more information, see Tabular Model Designer (SSAS Tabular).
4. Partitions in Tabular Models
Partitions divide tables into logical partition objects. Each partition can then be processed independent of other partitions. Partitions can be defined for a project during model authoring in SQL Server Data Tools (SSDT), or for deployed models by using SQL Server Management Studio. For more information, see Partitions (SSAS Tabular).
5. Security Roles in Tabular Models
Roles define member permissions for a model. Each role contains members, by Windows username or by Windows group, and permissions (read, process, administrator). Members of the role can perform actions on the model as defined by the role permission. Roles defined with read permissions can also provide additional security at the row-level by using row-level filters. For more information, see Roles (SSAS Tabular).
6. Row Level Security in Tabular Models
Row Level Security restricts data access by user identity, using filters at the row level. Filters are implemented through roles. For more information see Roles (SSAS Tabular).
7. Key Performance Indicators in Tabular Models
Key Performance Indicators (KPIs) are used to gauge performance of a value, defined by a Base measure, against a Target value, also defined by a measure or by an absolute value. For more information, see KPIs (SSAS Tabular).
8. Hierarchies in Tabular Models
Hierarchies are metadata that define relationships between two or more columns in a table. Hierarchies can appear separate from other columns in a reporting client field list, making them easier for client users to navigate and include in a report. For more information, see Hierarchies (SSAS Tabular).
9. Large Tables in Tabular Models
This release removes the limit of 2 billion rows per table. Tables have no limit in the number of rows they contain. Note that this limit is lifted for tables but not columns. Each column is limited to a maximum of 2 billion distinct values.
10. Images in Tabular Models
Images and similar data types, all under Binary Large Objects (BLOB) data types, are now supported in tabular projects. When creating a tabular project that includes images, the source column needs to be of binary or large binary data type.
11. DirectQuery Mode in Tabular Model Databases
DirectQuery mode is a deployment option for tabular projects that lets users and reporting clients retrieve data directly from a SQL Server data source. By using DirectQuery mode, you avoid the maintenance overhead of using cached data for a model, and leverage the sophisticated query processing of the underlying database system. This feature also lets you create models and build reports for large data sets that cannot reside in memory. DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, seeDirectQuery Mode (SSAS Tabular).
12. Memory Paging in Tabular Models
Memory paging allows models to be larger than the physical memory of the server. Memory paging is enabled through the VertiPaqPagingPolicy server property. For more information, see Memory Properties.
13. DAX Functions in this Release
This release introduces new statistical
functions, table functions, search functions, Row Level Security functions and
more. More than Thirty new functions have been added in all.
DAX is used in both PowerPivot workbooks and
tabular projects that you build in SQL Server Data Tools. For more information
about which functions are new, see What’s New (PowerPivot for Excel). To review the DAX
reference documentation in Books Online, see DAX
Function Reference.
3) Multidimensional Modeling
1. Removal of the 4 Gigabyte Limit on String Storage for MOLAP Engine
This release introduces a new option that removes a physical constraint on string store file size. In previous releases, a string storage file could grow to a maximum of 4 gigabytes, after which the following error would occur, indicating that the maximum file limit was reached: “File system error: a FileStore error from WriteFile occurred”. This release removes the maximum file size limit, allowing files to grow as needed. Note that upper limits still apply, but they are based on the number of strings rather than the size of the physical file. For more information, see Configure String Storage for Dimensions and Partitions.
2. Resource Usage Reporting for Multidimensional Databases
You can now collect metrics about resource usage at the command level when processing queries. Resource Usage is a new event class that can also be used as an additional column on the Command End event or Query End event. In the TextData column for this event, you can capture the number of reads or writes, reads or writes as measured in kilobytes, CPU time in milliseconds, rows scanned, and rows returned. For more information, see Query Processing Events Data Columns.
3. Trace Events for Lock Usage and Contention in Multidimensional Databases
This release includes new events to help you troubleshoot lock-related query or processing problems. Locks Acquired, Locks Released, and Locks Waiting are new trace events that complement existing lock events, Deadlockand LockTimeOut. For more information, see Lock Events Data Columns and the chapter on locking in the SQL Server 2008 R2 Analysis Services Operations Guide.
4. DistinctCount Performance Improvement in ROLAP Processing
Queries that include
DistinctCount in a ROLAP process can run faster,
assuming certain criteria are met. Performance is improved because more of the
operation has been offloaded to the relational database engine, where COUNT(DISTINCT column) is used to return unique non-null
values, eliminating the need for more expensive ordering operations that
degrade query performance.
This optimization is
off by default because the query results produced by the new and older
algorithms are not always identical. Transact-SQL counting can vary for NULL
values and different collations. If you want to use the optimization, set the OLAP\ProcessPlan\EnableRolapDistinctCountOnDataSource property
to 1.
This optimization
works only if the following restrictions are met:
·
There
is one partition per measure group. Multiple partitions make it impossible to correctly
execute the query and merge the results from different partitions.
·
There
are no arbitrary shapes in the queries, and no queries below the grain. If
Analysis Services cannot formulate a SQL query that is reasonably efficient, it
will fall back to the default query pattern.
·
The
relational database engine must be SQL Server 2005 or later, Parallel Data
Warehouse, or Teradata.
4) PowerPivot for Excel
1. PowerPivot for Excel (SQL Server 2012)
This release introduces a SQL Server 2012 version of PowerPivot for Excel. You can use this version of the add-in to author and publish PowerPivot workbooks from Excel 2010 to SQL Server 2012 PowerPivot for SharePoint.
2. DAX Functions in this Release
This release introduces new statistical
functions, table functions, search functions, Row Level Security functions and
more. More than Thirty new functions have been added in all.
DAX is used in both PowerPivot workbooks and
tabular projects that you build in SQL Server Data Tools. For more information
about which functions are new, see What’s New (PowerPivot for Excel). To review the DAX
reference documentation in Books Online, see DAX
Function Reference.
5) PowerPivot for SharePoint
1. PowerPivot Configuration Tool
A new tool is available to configure an installation of PowerPivot for SharePoint. The tool scans the system to determine whether SharePoint or PowerPivot software is already configured, and provides necessary actions used to deploy an operational server. This tool replaces the New Server installation option in SQL Server Setup that was previously used to install and configure software. In this release, installation and configuration are decoupled, with all configuration steps occurring post-installation using the PowerPivot Configuration Tool, PowerShell, or Central Administration. For more information, see PowerPivot Configuration Tools.
2. PowerShell for PowerPivot for SharePoint
This release introduces PowerShell cmdlets for configuring PowerPivot for SharePoint. Using a combination of SharePoint PowerShell cmdlets and the new PowerPivot cmdlets, you can fully configure a PowerPivot for SharePoint installation through PowerShell script. For more information, see PowerPivot Reference for SharePoint PowerShell.
3. BI Semantic Model Connection Files in SharePoint
You can access a tabular model from within SharePoint using a new type of connection object called a BI semantic connection. A BI semantic connection provides an HTTP endpoint to Analysis Services databases that are deployed on a standalone server, or to an Excel workbook containing PowerPivot data in a SharePoint farm that includes PowerPivot for SharePoint. A BI semantic connection file is a SharePoint content item that specifies the server location of a model database, similar to how Office Data Connection (.odc) files store connection information to external data. Within a SharePoint environment, a BI semantic connection is a supported data source for Power View reports that you design and use in SharePoint. You can also use a BI semantic connection as a data source for PivotTables in Excel. For more information, see PowerPivot BI Semantic Model Connection (.bism).
4. PowerPivot for SharePoint Configuration Settings and Server Health Rules
This release improves the administration and tuning of a PowerPivot for SharePoint deployment by adding more configuration settings and health rules that help you detect and fix problems before they occur. New configuration properties give you more control over disk space consumption, caching, and data refresh activity. For data refresh, you can specify whether to deactivate data refresh for workbooks that no one is using or that repeatedly fail to refresh.
5. PowerPivot Workbook Auto-upgrade to Enable Data Refresh
You can configure an automatic upgrade feature on a PowerPivot for SharePoint instance that upgrades SQL Server 2008 R2 workbooks to the latest version, thereby enabling data refresh for those workbooks on a SQL Server 2012 PowerPivot for SharePoint server. For more information, see Upgrade PowerPivot for Excel.
6) Programmability
1. DAX Functions in this Release
This release introduces new statistical
functions, table functions, search functions, Row Level Security functions and
more. More than Thirty new functions have been added in all.
DAX is used in both PowerPivot workbooks and
tabular projects that you build in SQL Server Data Tools. For more information
about which functions are new, see What’s New (PowerPivot for Excel). To review the DAX
reference documentation in Books Online, see DAX
Function Reference.
2. PowerShell for AMO
This release introduces PowerShell cmdlets for AMO. This feature gives you command-line connectivity, navigation, and discovery of Analysis Services databases that run on a Multidimensional or Tabular mode server. For more information, see Analysis Services PowerShell.
3. PowerShell for PowerPivot for SharePoint
This release introduces PowerShell cmdlets for configuring PowerPivot for SharePoint. Using a combination of SharePoint PowerShell cmdlets and the new PowerPivot cmdlets, you can fully configure a PowerPivot for SharePoint installation through PowerShell script. For more information, see PowerPivot Reference for SharePoint PowerShell.
4. AMO and XMLA Extensions to Support Tabular Modeling
Both multidimensional models and tabular
models are based on a unified BI semantic modeling schema (BISM), which is a superset
of the schema provided in previous releases as the Unified Dimensional Model
(UDM). Therefore, you can work with both types of models by using common APIs
(AMO and XMLA), and connect to servers and instances running in either tabular
or multidimensional mode by using both ADOMD.NET and OLEDB.
In addition to extending AMO and XMLA to
support tabular models, this release includes new properties, methods, and
objects to support the requirements, including properties for enhanced
reporting and aggregation in reports.
For more information, see Tabular
Model Programming.
You can also download the AMO samples for tabular modeling from CodePlex.
5. CSDL Extensions to Support Tabular Modeling
The Conceptual Schema Definition Language (CSDL) is used to represent a tabular model in response to a query from a client that can consume the schema and use that representation to create visualizations, such as Power View. The bi:extensions to CSDL provided in this release include entities, relationships and data types for tabular models, as well as properties to enhance visualization and model navigation in reports. To learn more about CSDL, see CSDL Annotations for Business Intelligence (CSDLBI).
7) Design Tools
1. SQL Server Data Tools (SSDT) integration with Visual Studio
Tabular projects and Multidimensional projects are created in SQL Server Data Tools. This release updates SQL Server Data Tools to run in the Visual Studio shell. The shell includes several IDE enhancements including improved readability and support for multiple monitors.
II. SSAS 2021SP1
1) PowerPivot in Excel:
PowerPivot in Microsoft Excel 2013 supports
deeper integration with Excel and your data exploration workflows. For more
information on what is new in PowerPivot, see What’s new in PowerPivot in Excel
2013(http://office.microsoft.com/en-us/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx?CTT=1).
2) PowerPivot for SharePoint:
A new architecture for SQL Server 2012
SP1 PowerPivot that supports a PowerPivot server outside a SharePoint 2013
farm. The new architecture leverages Excel Services for querying, loading,
refreshing, and saving data. The PowerPivot server can still be installed on a
server that also hosts SharePoint servers but it is not required. The new
architecture is available when you deploy a new PowerPivot server with the
slipstream version of SP1. The new architecture and is not available when you
deploy the patch version of SP1 onto an existing PowerPivot. For more
information, see the following:
·
The
“SQL Server 2012 SP1 Full Installation” section of Overview of SQL Server Servicing
Installation.
·
Install SQL Server BI Features with
SharePoint 2013 (SQL Server 2012 SP1) .
3) spPowerpivot.msi:
A Windows Installer package (spPowerpivot.msi)
that enhances the PowerPivot for SharePoint experience with additional features
such as PowerPivot Gallery, schedule data refresh, and management dashboard.
The .msi deploys Analysis Services client libraries,
PowerPivot for SharePoint 2013 Configuration, and copies PowerPivot for
SharePoint 2013 installation files to SharePoint servers. For more information
see Install or Uninstall the PowerPivot
for SharePoint Add-in.
4) Version Compatibility for Tabular models
SQL Server 2012 SP1 introduces new features
for Analysis Services running in Tabular mode, including optimized storage for
measures and KPIs, extended data categorizations, extended characters,
hierarchy annotation, and improved support when importing from Data Market data
feeds. In some cases, Tabular model projects being deployed may not be
compatible with an Analysis Services deployment server instance. With SP1
applied, you can specify Compatibility Level when creating new
Tabular model projects, when upgrading existing Tabular model projects, when
upgrading existing deployed Tabular model databases, or when importing
PowerPivot workbooks. For more information, see Compatibility Level (SSAS Tabular SP1).
5) Import from PowerPivot in Excel 2013
You can now import PowerPivot in Excel 2013
workbooks into new Tabular model projects created in SQL Server Data Tools or
directly in SQL Server Management Tools.
5. SQL SERVER 2014
I. Updates to Design Tool installation
SQL Server Data Tools for Business
Intelligence (SSDT-BI), previously known as Business Intelligence Development
Studio (BIDS), is used to create Analysis Services models, Reporting Services
reports, and Integration Services packages. You can download SSDT-BI from the
following locations:
·
Download SSDT-BI for Visual
Studio 2013
·
Download SSDT-BI for Visual
Studio 2012
If you have a prior version of SSDT-BI or
BIDS installed on your computer, the newer version is installed side-by-side
the previous version. It's common to run newer and older versions of the design
tools on a single workstation so that you can modify projects and solutions
tied to specific versions of the server.
|
|
|
There are several download sites for the Visual Studio 2012 and Visual Studio 2013 versions of SSDT. Most do not include the BI project templates. Using the links above will get you the correct version. You’ll know that you have the correct version of SSDT-BI if you see the Business Intelligence project templates folder. This folder contains the project templates for Analysis Services, Reporting Services and Integration Services. Depending on how you installed SSDT-BI, you might also see an additional project template for SQL Server databases. |
II. Features recently added: Power View for Multidimensional Models
The ability to create Power View reports
against multidimensional models was first introduced in SQL Server 2012 Service
Pack 1 Cumulative Update 4. Power View for Multidimensional Models
functionality is now included as part of SQL Server 2014.
Power View Report for a
Multidimensional Model
This functionality helps organizations
maximize existing BI investments by enabling multidimensional models (also
known as OLAP cubes) to be used with the latest client reporting tools.
Depending on the types of data in the multidimensional model, users can easily
create a variety of dynamic visualizations from tables and matrices, to bubble
charts and geographical maps. Multidimensional models now also support queries
using Data Analysis Expressions (DAX).
Power View for Multidimensional Models
requires the built-in Power View reporting capability in SQL Server
2014Reporting Services (in SharePoint mode). Other versions of Power View, specifically
the Power View Add-in in Excel 2013, do not support multidimensional models.
To learn more, see Power View for
Multidimensional Models.
6. SQL SERVER 2016
I. Parallel processing for multiple table partitions in Tabular models
[CTP 2.0]
SQL Server 2016 Analysis Services (SSAS)
includes new parallel processing functionality for tables with two or more partitions,
increasing processing performance. There are no configuration settings for this
feature. For more information about configuring partitions and processing
tables, see Tabular
Model Partitions (SSAS Tabular).
II. Add Computer Accounts as Administrators in Management Studio
[CTP 2.0]
Analysis Services administrators can now use
SQL Server Management Studio to configure computer accounts to be members of
the Analysis Services administrators group. In the Select Users or Groups dialog, set the Locations for the computers
domain and then add the Computers object type. For
more information, see Configure
Analysis Services Server Administrators.
III. New DBCC command to check for database corruption
[CTP 2.0]
The new DBCC XMLA command checks for
physical and logical data corruption in both tabular and multidimensional
databases, offering fast and efficient diagnosis of the more common forms of
data corruption. For more information, see Database
Consistency Checker (DBCC) for Analysis Services tabular and multidimensional
databases.
IV. Easier setup for Analysis Services Power Pivot Mode
[CTP 1.0]
Installation of Power Pivot for SharePoint
is simplified. You select Analysis Services from the standard Instance Feature list and then from a
single setup page you select the Analysis Services server mode and configure
administrators.
For more information, see Install
Power Pivot for SharePoint 2013.
V. New DAX Functions for Tabular models
[CTP 1.0]
SQL Server 2016 Analysis Services (SSAS)
includes the following new DAX functions.
|
Function |
Description |
|
ADDMISSINGITEMS |
Adds combinations of items from multiple columns to a table if they do not already exist. The determination of which item combinations to add is based on referencing source columns which contain all the possible values for the columns. |
|
CALENDAR |
Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates. |
|
CALENDARAUTO |
Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. |
|
CONCATENATEX |
Concatenates the result of an expression evaluated for each row in a table. |
|
DATEDIFF |
Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns. |
|
GEOMEAN |
Returns the geometric mean of the numbers in a column. |
|
GEOMEANX |
Returns the geometric mean of an expression evaluated for each row in a table. |
|
GROUPBY |
Returns the binary data returned from a URL. |
|
ISEMPTY |
Checks if a table is empty. |
|
ISONORAFTER |
A boolean function that emulates the behavior of a ‘Start At’ clause and returns true for a row that meets all of the condition parameters. |
|
MEDIAN |
Returns the median of numbers in a column. |
|
MEDIANX |
Returns the median number of an expression evaluated for each row in a table. |
|
NATURALINNERJOIN |
Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned. |
|
NATURALLEFTOUTERJOIN |
Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned. |
|
PERCENTILE.EXC |
Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive. |
|
PERCENTILE.INC |
Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. |
|
PERCENTILEX.EXC |
Returns the percentile number of an expression evaluated for each row in a table. |
|
PERCENTILEX.INC |
Returns the percentile number of an expression evaluated for each row in a table. |
|
PRODUCT |
Returns the product of the numbers in a column. |
|
PRODUCTX |
Returns the product of an expression evaluated for each row in a table. |
|
SUBSTITUTEWITHINDEX |
Returns a table which represents a left semijoin of the two tables supplied as arguments. The semijoin is performed by using common columns, determined by common column names and common data type. The columns being joined on are replaced with a single column in the returned table which is of type integer and contains an index. The index is a reference into the right join table given a specified sort order. |
|
Columns in the right/second table supplied which do not exist in the left/first table supplied are not included in the returned table and are not used to join on. |
|
|
The index starts at 0 (0-based) and is incremented by one for each additional row in the right/second join table supplied. The index is based on the sort order specified for the right/second join table. |
|
|
SUMMARIZECOLUMNS |
Returns a summary table over a set of groups. |
|
UNION |
Creates a union (join) table from a pair of tables. |
|
XIRR |
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. |
|
XNPV |
Returns the present value for a schedule of cash flows that is not necessarily periodic. |
The following existing functions received
updates for this release.
|
Function |
Description |
|
TOPn |
Now allows TRUE/FALSE/ASC/DESC to specify sorting direction. |
VI. New elements in MS-CSDLBI 2.0 schema
[CTP 1.0]
The following elements have been added to
the TProperty complex type defined
in the [MS-CSDLBI] 2.0 schema:
|
Element |
Definition |
|
DefaultValue |
A property that specifies the value used when evaluating the query. The DefaultValue property is optional, but it is automatically selected if the values from the member cannot be aggregated. |
|
Statistics |
A set of statistics from the underlying data that is associated with the column. These statistics are defined by the TPropertyStatistics complex type and are provided only if they are not computationally expensive to generate, as described in section 2.1.13.5 of the Conceptual Schema Definition File Format with Business Intelligence Annotations document. |
