What's New in PivotTable Service
PivotTable® Service supports the advanced data mining and analysis techniques that are introduced in Microsoft® SQL Server™ 2000.
This release of PivotTable Service also includes new features that enhance communication with the Analysis server and the management of local cubes.
Client applications can communicate with the Analysis server through Microsoft Internet Information Services (IIS) using HTTP; clients do not need physical proximity to the Analysis server to take advantage of common Internet services such as Domain Name System (DNS). The Analysis server also supports security features such as cube roles, member security, and cell security.
The ability to create and manage local cubes from the client application has been improved. Client applications can now alter the structure of a cube, define default members, and sort by member properties by using the ALTER CUBE statement. The UPDATE CUBE command supports writeback to members higher than those at the leaf level of a hierarchy.
Advanced Data Mining and Analysis
PivotTable Service supports data mining and analysis with the addition of the CREATE MINING MODEL statement and extensions to the CREATE VIRTUAL CUBE statement. Two algorithms are included: Microsoft Decision Trees and Microsoft Clustering.
Data mining models can be created on the server, using Decision Support Objects (DSO), or locally, using the CREATE MINING MODEL statement. The models can then be trained and used to produce predictions based on trends identified in the training data.
The syntax for virtual cubes has also been extended to allow the inclusion of mining models.
For more information, see Data Mining Models, Advanced Data Mining and Analysis, CREATE MINING MODEL Statement, and Decision Support Objects.
Security Enhancements in PivotTable Service
SQL Server 2000 Analysis Services provides three security models with which PivotTable Service interacts.
Cube security
Read and read/write permissions for databases and cubes in a database.
Member security
Secured members are visible only to users who have been granted permission to read them. For example, a user may query a States level that contains the members Washington, Oregon, and California. If that user has at least read permission on the States level and all three of its members, all three states are returned. If the user does not have at least read permission on California, California is not returned in the query. No error is raised, and no placeholder is generated.
Cell security
Queries that involve a secured cell or set of cells return an error code or value, depending on the value of the Secured Cell Value property of the connection string.
For more information, see Security in PivotTable Service, Data Source Property, Secured Cell Value Property, and SSPI Property.
Connection Enhancements in PivotTable Service
The following enhancements have been made to the way that PivotTable Service connects to a data source.
Connecting to a server over the Internet
Client applications can connect to the Analysis server through IIS by using a URL for the server name in the Data Source property of the connection string. For more information, see Connecting Using HTTP.
Using the security services provider interface
You can use the SSPI property to specify a third-party security provider when connecting to a data source using PivotTable Service.
Connecting using a different user role
When connecting to a database, the client developer can specify a different role than the current one using the Roles property. This allows developers and database administrators (DBAs) to test security permission settings for different roles without having to log in again under those roles. The developer or DBA must be a member of the role under which he or she is connecting.
Data Source Property, Connecting Using HTTP, SSPI Property, and Roles Property.
Cube Enhancements
PivotTable Service includes the following enhancements to the client application's interaction with server cubes and local cubes.
Ragged hierarchies
PivotTable Service supports a variety of hierarchy structures in dimensions. Some hierarchies that are based on levels, such as geographical dimensions, may have branches where there is no member for a particular level. A geographical hierarchy that includes Country, State_province, and City levels might include a country that does not have states or provinces. Such hierarchies are known as ragged hierarchies. For example, a geography dimension might include Washington, D.C. Unlike other cities in the United States, this city is not a child member of any state. Its parent is the [USA] member itself.
Unbalanced hierarchies
Some hierarchies are based less on the concept of levels and more on the hierarchical relationship between the dimension members. Organization charts and part manufacturing structures are examples of such hierarchies. These hierarchies, known as unbalanced hierarchies, are created in Analysis Services from a table that has a column of keys that refer to primary keys in the same table. In an organization chart table this column is often called ManagedBy or Manager. The concept of levels is often less significant than the relationship hierarchy in these dimensions. For example, one executive assistant may report directly to a vice president, whereas another may report to a manager.
For more information, see Organization of Multidimensional Data.
Custom rollups and calculated members
You can define custom rollup functions in addition to the standard aggregate functions (Distinct Count, Sum, Min, Max, and Count). For example, you can use a rollup function to define a weekly average. For more information, see CREATE MEMBER Statement.
PivotTable Service supports calculated members for server and local cubes. For more information, see Defining Calculated Members.
Allocations
Allocations are now supported by the inclusion of the UPDATE CUBE statement in data manipulation language (DML). Aggregate members (that is, members that are higher than the leaf level) can have a new value assigned to them. The component members of the aggregate member are updated according to the allocations described by the parameters of the UPDATE CUBE statement.
For more information, see Writing a Value Back to a Cell and UPDATE CUBE Statement.
Altering the structure of a cube
PivotTable Service now supports the ALTER CUBE statement of the data definition language (DDL). You can modify existing cubes by:
- Adding a dimension member.
- Removing a dimension member.
- Moving a dimension member.
- Modifying the properties of a dimension member.
For more information, see ALTER CUBE Statement.
Defining default hierarchies and members
You can define members and hierarchies as the default member or hierarchy for a dimension using the ALTER CUBE statement. For example, you can define the member USA as the default member of the Geography dimension (which only has one hierarchy) in a cube that contains data for the USA only. Alternatively, you can define the default hierarchy of the Time dimension as the Year-Month-Day hierarchy or the Year-Quarter hierarchy. For more information, see CREATE CUBE Statement.
New schema rowsets
The following schema rowsets are now supported.
| Schema rowset | Description |
|---|---|
| MDSCHEMA_FUNCTIONS | Describes the functions that are available to client applications connected to the database |
| MDSCHEMA_PROPERTIES | Describes the properties of members contained within a database |
| MDSCHEMA_SETS | Describes any sets that are currently defined |
| MDSCHEMA_ACTIONS | Describes the actions that may be available to client application |
| MDSCHEMA_CELL_FORMULAS | Describes the calculated cells that may be contained within a database |
| MINING_MODELS | Exposes |
| MINING_COLUMNS | Describes the individual columns of all defined data mining models known to the provider |
| MINING_MODEL_CONTENT | Allows browsing of the content of a data mining model |
| MINING_SERVICES | Provides a description of each data mining algorithm that is supported by that provider |
| MINING_SERVICE_PARAMETERS | Provides a list of parameters that can be supplied when generating a mining model using the CREATE MINING MODEL statement |
| MINING MODEL_CONTENT_PMML | Stores the Predictive Model Markup Language (PMML) standard Extensible Markup Language (XML) representation of the mining model |
For more information, see Schema Rowsets and the OLE DB documentation.
Sorting by member properties
When building a local cube file, you can sort members in a dimension using member properties. For example, assume that a Geography dimension has a member property Latitude of type string. When you build a cube that contains the Geography dimension, you can sort the members of the geography dimension by the member property Latitude instead of by the Name or Key properties. For more information, see CREATE CUBE Statement.
Actions
Each member of a cube can have an action associated with it. Actions are context-sensitive operations that allow a client application to trigger a custom, data-sensitive operation on the members. For example, you can define an action for members of the Customers dimension that retrieves a rowset describing the details for that customer. The client application may allow the user to right-click the customer entry to trigger the display of that rowset. For more information, see Creating Actions and CREATE ACTION Statement.
Drilling through to source data
The source data that make up a cell's value can be obtained by using the DRILLTHROUGH statement. For example, a user who is interested in the cell {"Sales","Pearl Light Beer","Orgeon","1998"} can obtain the rows from the fact table that were used to compute that cell's value. For more information, see DRILLTHROUGH Statement.