Using Data Definition Language with SQL Server 2000 Analysis Services Client Applications
Updated : February 14, 2004
Dennis Kennedy
May 2001
Abstract: This paper describes how to use the expanded data definition language (DDL) functionality and simplified syntax included with Microsoft® SQL Server™ 2000 Analysis Services Service Pack 1 (SP1) in client applications.
On This Page
Introduction
Data Definition Language Concepts
Statement and Function Reference
Conclusion
Finding More Information
Appendix A: Syntax Comparison
Introduction
Microsoft® SQL Server™ 2000 Analysis Services Service Pack 1 (SP1) greatly expands the capabilities of client applications to provide local OLAP and data mining functionality, while simplifying the data definition language (DDL) syntax client applications need to incorporate such functionality.
To provide this functionality, Microsoft has changed the DDL for PivotTable® Service substantially, focusing on the CREATE CUBE and CREATE SESSION CUBE statements. The CREATE GLOBAL CUBE statement, introduced in SP1, allows client applications to create local cubes from cubes located on an Analysis server. The CREATE GLOBAL CUBE statement uses a much simpler syntax than the CREATE CUBE statement. The functionality provided by the additions to the CREATE SESSION CUBE statement expand the concept and use of derived dimensions.
Also, the use of visual totals is now more flexible because of the addition of the DROP VISUAL TOTALS statement and an enhanced version of the VisualTotals MDX function.
This white paper has two parts. The first part describes the basic concepts you should be familiar with when using the DDL for Analysis Services, such as slicing and derived dimensions, and provides detailed examples demonstrating each concept using the new and enhanced DDL syntax. The second part describes the changes and additions to the DDL for Analysis Services, with each statement described in a separate section that also includes usage considerations and examples. Also included is an appendix that contains examples of CREATE CUBE and CREATE GLOBAL CUBE syntax, which demonstrate the differences between the two statements. All examples in this white paper use the FoodMart 2000 sample database (FoodMart 2000.mdb) that is provided with Analysis Services.
Audience
This white paper targets users, such as application developers and database administrators, who are interested in developing client applications that work with Analysis Services. This document assumes that you have:
An understanding of online analytical processing (OLAP) and data mining concepts.
An understanding of SQL DDL statements.
A working knowledge of Multidimensional Expressions (MDX).
For more information about Analysis Services and MDX, see SQL Server 2000 Books Online.
Syntax Conventions
To illustrate how the statements function, various syntax elements within the statements are highlighted. All MDX functions and keywords are in uppercase, while all other elements, such as member and set names, are in a case appropriate to the element.
To better illustrate the use of MDX functions and keywords within the statement examples, the statement is indented to make it easier to read.
MDX syntax is not always consistent; within the examples, certain elements may be highlighted to make potentially difficult MDX syntax clearer and easier to understand.
Data Definition Language Concepts
In general, the DDL statements that Analysis Services uses do not function the same way as typical relational database DDL statements. For example, while SQL Server 2000 uses DDL to create objects on an instance of SQL Server, you cannot create server-based objects using the DDL for Analysis Services. The DDL for Analysis Services allows client applications to create and manage local cubes and data mining models, facilitating disconnected access to OLAP and data mining information.
Because many client applications have a business- or application-specific view of OLAP and data mining data, the DDL statements allow you to construct such views by using slicing and by implementing derived dimensions.
Client applications should take advantage of both features to provide a robust disconnected solution. For example, you can slice the Sales cube in FoodMart 2000 along the [State Province] level of the Customers dimension, allowing a small subsection of data to be loaded as a local cube onto the client laptops of the sales representative for each state. Then your client application can construct session cubes to further slice the local cube for analysis by product family, or it can construct a derived dimension that groups customers according to business-defined sales regions within each state. All of this analysis can be performed without a connection to an Analysis server. Once the local cube is generated, it can be queried and session cubes can be constructed from the local cube without network connectivity.
The original CREATE CUBE statement, while powerful and flexible, is also complex and difficult to use, especially when you create local cubes that are based on existing cubes located on an Analysis server. The new CREATE GLOBAL CUBE statement simplifies this process by constructing and executing CREATE CUBE and INSERT INTO statements in the background. The CREATE CUBE and INSERT INTO statements are still supported and you can still use them to construct local cubes; however, the CREATE GLOBAL CUBE statement makes the process of creating local cubes from existing cubes much easier.
Slicing
Slicing is a method used to extract a specific subset of data and meta data from a cube by specifying one or more members from one or more dimensions. If a dimension is not specified as part of the slice, all of its members are automatically included. You can slice a cube along dimensions, levels, members, and measures.
The following figure illustrates a cube with three dimensions, Route, Source, and Time, and two measures, Packages and Last.
.gif "Figure 1")
Figure 1
You can slice this cube along all three dimensions and along each measure. The following set demonstrates a slice with a single specific member in an MDX query.
{[Time].[2nd half].[3rd quarter]}
This slice is represented by the shaded cells in the following figure.
.gif "Figure 2")
Figure 2
Because the slice specifies only one member from one dimension, Time, the slice contains all of the members from all of the other dimensions that intersect with the specified member. The previous figure illustrates this one-member slice.
You can also slice cubes along multiple dimensions. The following set defines a slice along two dimensions, with a level specified for the Time dimension and a member specified for the Route dimension.
{[Time].[2nd half], [Route].[nonground].[air]}
The following figure represents this slice, with selected cells shaded.
.gif "Figure 3")
Figure 3
Because the Source dimension was not specified, all of the members from Source that intersect with both the [Time].[2nd half] level and the [Route].[nonground].[air] member are included, in addition to all of the measures.
Slicing on source dimensions is supported only by the CREATE GLOBAL CUBE statement. However, you can create a derived dimension from a source dimension and slice the derived dimension using the CREATE SESSION CUBE statement.
Derived Dimensions
A derived dimension is a dimension that is not based directly on a fact table; instead, it is derived from either another dimension or a data mining model. Except for the [All] level, all members of a derived dimension are based on custom rollup formulas. You can create derived dimensions as needed by using the CREATE SESSION CUBE statement; derived dimensions cannot be persisted. Derived dimensions are supported only through the use of the CREATE SESSION CUBE statement.
Derived dimensions provide the following special features for client applications:
Grouping. A derived dimension can provide a method of dynamically reorganizing members from a source dimension into custom member groups. A derived dimension that is based on another dimension is also called a grouping dimension, and session cubes that use grouping dimensions are also called grouping cubes.
Data mining. A derived dimension can be based on the output of a data mining model and included in a session cube created by the client application. A derived dimension uses the same capabilities as a grouping dimension, but the custom member groups are determined by the prediction column chosen from the data mining model and specified in the CREATE SESSION CUBE statement. A derived dimension based on a data mining model is also called a data mining dimension.
Grouping and data mining dimensions are explained in more detail in the following sections.
Grouping Dimensions
The concept of member groups was originally used to extend the number of members a dimension could contain. The previous version of Analysis Services, Microsoft SQL Server OLAP Services, limited each parent member in a dimension to 64,000 child members. The limit is based on the number of addressable members per parent. In Analysis Services, member groups provide the capability for a member to logically belong to another member by creating a hidden layer of members. The limitation of 64,000 members per parent still exists, but by using member groups, the limit is now extended to 64,000 times 64,000 members, because of this hidden layer, or approximately 4 billion members per parent. Member groups are created in a level that is added immediately above the level that contains the children of the member groups. When end users browse a level that contains member groups, they see the names and cell values of the member groups. To end users, member groups look like ordinary members. However, member groups are automatically generated and named by the Analysis server; the user is not directly involved in the process.
The new version of the CREATE SESSION CUBE statement allows the creation of user-defined member groups. Using this feature, client applications, such as Microsoft Excel, can allow users to select a set of members and create new member groups specifically for the purpose of analysis. Such user-defined member groups are not persisted, although users can save the CREATE SESSION CUBE statement and execute it as needed.
Grouping can be used in a variety of business scenarios. For example, you can create a local cube from the Sales cube in FoodMart 2000 and slice it so that the Customers dimension contains only members that are children of the member [Customers].[All Customers].[USA].[CA], or all customers who reside in California, organized by city. As part of a sales or marketing program, certain cities belong to specific sales or survey regions. The Customers dimension does not reflect this information as a level in the dimension, but you need to group the dimension members by region for analysis purposes. Also, you may need to move cities from one region to another to perform "what if" analyses on product sales by region. While this is difficult to do in a persisted cube, the CREATE SESSION CUBE syntax simplifies the task by allowing you to create a session cube with a derived dimension that is based on the Customers dimension. An additional level is added to the derived dimension, which is used as a grouping level. Member groups can then be created within this grouping level, and members are then assigned to these member groups. Excel already supports this feature by allowing drag-and-drop creation of grouping levels. To change the membership of custom member groups, you can drop and recreate the session cube, altering member groups and assignments as needed.
The following CREATE GLOBAL CUBE and CREATE SESSION CUBE statements demonstrate slicing and grouping. The CREATE GLOBAL CUBE statement slices the cube so that the slice includes only those members that are located in California, and the CREATE SESSION CUBE statement creates a new session cube, based on the local cube, that adds four groups based on salesperson assignment. The global cube is used because of slicing restrictions on session cubes. For more information about slicing restrictions on the CREATE SESSION CUBE statement, see "CREATE SESSION CUBE Statement."
CREATE GLOBAL CUBE SalesCA STORAGE 'C:\SalesCA.cub' FROM [Sales]
(
MEASURE [Sales].[Unit Sales],
MEASURE [Sales].[Store Cost],
MEASURE [Sales].[Store Sales],
MEASURE [Sales].[Sales Count],
MEASURE [Sales].[Store Sales Net],
DIMENSION [Sales].[Customers]
(
LEVEL [State Province],
LEVEL [City],
MEMBER [Customers].[All Customers].[USA].[CA]
),
DIMENSION [Sales].[Education Level],
DIMENSION [Sales].[Gender],
DIMENSION [Sales].[Marital Status],
DIMENSION [Sales].[Product],
DIMENSION [Sales].[Promotion Media],
DIMENSION [Sales].[Promotions],
DIMENSION [Sales].[Store],
DIMENSION [Sales].[Store Size in SQFT],
DIMENSION [Sales].[Store Type],
DIMENSION [Sales].[Time],
DIMENSION [Sales].[Yearly Income]
)
CREATE SESSION CUBE [SalesAssignmentsCA] FROM [SalesCA]
(
MEASURE [SalesCA].[Unit Sales],
DIMENSION [SalesCA].[Time],
DIMENSION [SalesCA].[Customers] HIDDEN AS [HiddenCustomers],
DIMENSION [Customers] NOT_RELATED_TO_FACTS FROM [HiddenCustomers]
(
LEVEL [State Province],
LEVEL [SalesPerson] GROUPING,
LEVEL [City],
GROUP [SalesPerson].[SalesPerson A]
(
MEMBER [HiddenCustomers].[CA].[Altadena],
MEMBER [HiddenCustomers].[CA].[Beverly Hills],
MEMBER [HiddenCustomers].[CA].[Bellflower],
MEMBER [HiddenCustomers].[CA].[Daly City],
MEMBER [HiddenCustomers].[CA].[Fremont],
MEMBER [HiddenCustomers].[CA].[Concord]
),
GROUP [SalesPerson].[SalesPerson B]
(
MEMBER [HiddenCustomers].[CA].[Colma],
MEMBER [HiddenCustomers].[CA].[Burlingame],
MEMBER [HiddenCustomers].[CA].[Chula Vista],
MEMBER [HiddenCustomers].[CA].[Long Beach],
MEMBER [HiddenCustomers].[CA].[Pomona]
),
GROUP [SalesPerson].[SalesPerson C]
(
MEMBER [HiddenCustomers].[CA].[Oakland],
MEMBER [HiddenCustomers].[CA].[Los Angeles]
),
GROUP [SalesPerson].[SalesPerson D]
(
MEMBER [HiddenCustomers].[CA].[San Gabriel],
MEMBER [HiddenCustomers].[CA].[Santa Cruz],
MEMBER [HiddenCustomers].[CA].[Spring Valley]
)
)
)
In the previous example, the Customers dimension is hidden and renamed to HiddenCustomers to prevent confusion. Then a grouping dimension named Customers is created, which is based on the HiddenCustomers source dimension, to replace the Customers dimension. The GROUPING keyword is then used to create a grouping level, named SalesPerson, in the Customers grouping dimension. Four member groups are created in the SalesPerson grouping level by using the GROUP keyword. Finally, each member group is assigned members from the City level of the source dimension.
Because not all of the members in the City level are added to a member group in the SalesPerson grouping level, PivotTable Service automatically creates a member group in which all of the nongrouped members are placed. Member groups cannot contain a mix of members and member groups; they can contain only members or only member groups. The name of the automatically generated member group is based on the name of the parent member of the dimension to which the grouping dimension is related; in this case, it is the [CA] member from the [HiddenCustomers] dimension.
The following table shows the hierarchy of the [Customers] grouping dimension.
State Province |
SalesPerson |
City |
|---|---|---|
CA |
[HiddenCustomers].[CA] |
Arcadia |
|
|
Berkeley |
|
|
Burbank |
|
|
... |
|
SalesPerson A |
Altadena |
|
|
Bellflower |
|
|
Beverly Hills |
|
|
Concord |
|
|
Daly City |
|
|
Fremont |
|
SalesPerson B |
Burlingame |
|
|
Chula Vista |
|
|
Colma |
|
|
Long Beach |
|
|
Pomona |
|
SalesPerson C |
Los Angeles |
|
|
Oakland |
|
SalesPerson D |
San Gabriel |
|
|
Santa Cruz |
|
|
Spring Valley |
You can create nested levels of organization by creating member groups that contain other member groups. Grouping provides flexibility for analysis within client applications by giving users the capability to reorganize data as needed for correlation and cross-tabulation purposes.
When you use grouping dimensions, these rules must be followed:
Member groups must have exactly one parent.
In the previous example, the [SalesPerson A] member group has [Customers].[All Customers].[CA] as its parent member, because all of the members assigned to the member group have the same parent.
If you try to add the [HiddenCustomers].[WA].[Redmond] member to the list of members assigned to the [SalesPerson A] member group, an error is raised. This occurs because the [SalesPerson A] member group, to include this member, must belong to both the [Customers].[All Customers].[CA] and [Customers].[All Customers].[WA] members in the level immediately preceding the [Region] grouping level.
Member groups can either contain members or member groups, but not both.
For more information about the CREATE SESSION CUBE statement, see "CREATE SESSION CUBE Statement."
Data Mining Dimensions
The concept of data mining dimensions was introduced with the original CREATE SESSION CUBE statement. This section explores the usage of data mining dimensions as a method for data mining analysis by client applications.
Data mining dimensions are similar to grouping dimensions in that custom member groups are used to organize information. Data mining dimensions create member groups based on a predictable column defined in a data mining model. The information contained in the cube referenced by the CREATE SESSION CUBE statement is analyzed using the data mining model, and the data mining dimension is then constructed based on the nodes stored in the data mining model for a given predictable column.
For example, you can use the Customer Pattern Discovery data mining model, a decision tree data mining model from FoodMart 2000, to view the information contained in the Sales cube organized by yearly income. The following CREATE SESSION CUBE statement to create a session cube that contains a derived dimension named [Customer Pattern Dimension]. This dimension is based entirely on custom rollups and custom member groups that are generated by the information provided by the data mining model.
CREATE SESSION CUBE [Customer Pattern Cube] FROM [Sales]
(
MEASURE [Sales].[Store Sales Net],
DIMENSION [Sales].[Customers],
DIMENSION [Sales].[Store],
DIMENSION [Customer Pattern Dimension]
NOT_RELATED_TO_FACTS
FROM [Customer Pattern Discovery]
COLUMN [Customers.Name.Member Card]
)
The [Customer Pattern Dimension] dimension uses the predictable column [Customers.Name.Member Card] to organize measures according to the nodes of the decision tree used in the data mining model.
The dimension structure based on the previous query resembles the structure shown in the following table.
Note The prefix "Customers.Name." is omitted from the entries in the columns Level 02 and Level 03.
Level 01 |
Level 02 |
Level 03 |
|---|---|---|
All |
Yearly Income = $10K - $30K |
|
|
Yearly Income = $110K - $130K |
|
|
Yearly Income = $130K - $150K |
|
|
Yearly Income = $150K + |
Marital Status = M |
|
|
Marital Status = S |
|
Yearly Income = $30K - $50K |
Marital Status = M |
|
|
Marital Status = S |
|
Yearly Income = $50K - $70K |
Marital Status = M |
|
|
Marital Status = S |
|
Yearly Income = $70K - $90K |
Marital Status = M |
|
|
Marital Status = S |
|
Yearly Income = $90K - $110K |
Marital Status = M |
|
|
Marital Status = S |
The level names are generated automatically by the system; the member names are created from the node path information stored in the data mining model.
Data mining dimensions are not a substitute for prediction queries; instead, data mining dimensions allow reorganization of multidimensional data based on the statistical data stored by the data mining model. For example, you can drill down on members to determine which members are located in a specific cluster from a data mining model based on a clustering algorithm, while viewing aggregation data for the entire cluster.
For more information about data mining with the CREATE SESSION CUBE statement, see "CREATE SESSION CUBE Statement."
Statement and Function Reference
This section details only those MDX statements and functions that have been added or changed for SP1. For information about other DDL statements, see SQL Server 2000 Books Online.
CREATE GLOBAL CUBE Statement
A global cube is a subset of a single cube, that is, a cube slice from a cube persisted either on an Analysis server or in a different local cube file. The global cube is persisted on the client in a local cube file and is accessible without a connection to the Analysis server. Originally, the functionality of the CREATE GLOBAL CUBE statement was represented by the much more complex CREATE CUBE statement, but with the release of SP1 the statement has been simplified. Most of the features included with the original CREATE CUBE statement were designed based on the premise that users would create independent local cube structures, which may be based on a cube located on an Analysis server and populated with the INSERT INTO statement.
The original CREATE CUBE and INSERT INTO syntax is still supported to maintain backward compatibility with existing client applications and to supply additional flexibility, but it is considerably more complex than the new CREATE GLOBAL CUBE syntax. The original syntax requires that the cube be constructed in the connection string, and that two statements, CREATE CUBE and INSERT INTO, are used to construct and populate the local cube. The CREATE CUBE statement requires users to be intimately familiar with the structure of the source cube. However, PivotTable Service uses the CREATE GLOBAL CUBE statement to generate appropriate CREATE CUBE and INSERT INTO statements automatically by reading the necessary settings and flags directly from the structure of the source cube. This allows users who have only a basic familiarity with the structure of the source cube to create local cubes.
The CREATE GLOBAL CUBE statement requires that a local cube be created from an existing cube located on an Analysis server. The statement not only defines the structure of the local cube, but it also populates the local cube with data from the source cube. Many of the settings and flags available in the original CREATE CUBE statement are no longer used because the local cube draws that information from the structure of the source cube.
To compare the differences between the original CREATE CUBE statement and the new CREATE GLOBAL CUBE statement, see Appendix A, which contains two examples representing the same local cube. The first example uses the original CREATE CUBE syntax; the second sample uses the new CREATE GLOBAL CUBE syntax.
The following syntax structure, in BNF format, describes the enhanced CREATE GLOBAL CUBE statement:
BNF
<create cube> ::= CREATE GLOBAL CUBE <cube name> STORAGE '<file>' FROM <cube name> (<param list>) <param list> ::= <param>[, <param list>] <param> ::= <measures list> | <dim list> <measures list> ::= <measure def>[, <measures list>] <dim list> ::= <dim def>[, <dim list>] <measure def> ::= MEASURE <cube name>.<measure name> [<visibility qualifier>] [AS <measure name>] <visibility qualifier> ::= HIDDEN <dim def> ::= DIMENSION <cube name>.<dimension name> [<visibility qualifier>] [AS <dimension name>] [<dim content def>] <dim content def> ::= (<level list>[, <member slice list>] [, <default member>]) <level list> ::= <level def>[, <level list>] <member slice list> ::= <member def>[, <member slice list>] <default member> ::= DEFAULT_MEMBER AS '<MDX expression>' <level def> ::= LEVEL <level name> [<visibility qualifier] [AS <level name>] [<level content def>] <member def> ::= MEMBER <member name> <level content def> ::= (<property list>) | NO_PROPERTIES <property list> ::= <property def>[, <property list>] <property def> ::= PROPERTY <property name>
Usage Considerations
When using the CREATE GLOBAL CUBE statement, consider the following:
All commands, such as calculated measures or actions, are automatically copied to the local cube. If the command contains an MDX expression that references the parent cube explicitly, the local cube cannot execute that command. To prevent this problem, use the CURRENTCUBE keyword when defining MDX expressions for commands. The CURRENTCUBE keyword uses the current cube context when referencing a cube within an MDX expression.
Note The commands in the cubes included with FoodMart 2000 contain explicit references to parent cubes. This behavior does not occur when you create new commands; to remove these references from existing commands in FoodMart 2000, drop and recreate affected calculated members, calculated measures, calculated cells, actions, and named sets, as needed. For example, to create local cubes from the Sales cube in FoodMart 2000 correctly, you must drop and recreate the calculated members [Profit] and [Sales Average] and the [New Action] action.
You can store more than one cube in a single local cube file. This is an excellent way to group local cubes according to business purpose. But because the DROP CUBE command cannot be used to drop persisted local cubes, the only way to drop such cubes is to delete the local cube file. If you plan to drop and re-create persisted local cubes regularly, you should place each cube in its own local cube file.
You cannot construct a global cube from another global cube and save it in the same local cube file. For example, you can create a global cube named [SalesLocal1] and save it in a file named C:\SalesLocal.cub. However, you will receive an error if you connect to the local cube file and create a second global cube named [SalesLocal2, and attempt to save it to the same file. You can, however, create the second global cube and save it to a different local cube file.
Global cubes do not support distinct count measures. Because cubes that include distinct count measures are nonadditive, the CREATE GLOBAL CUBE statement cannot support the creation or use of distinct count measures. For more information about distinct count measures, see "Using Aggregate Functions" in SQL Server 2000 Books Online.
Global cubes do not support parent-child dimensions that contain unary operators.
Examples
The following examples demonstrate some of the features of the CREATE GLOBAL CUBE statement, including slicing and hiding.
Creating a Basic Global Cube
The following example creates a local version of the Warehouse cube from FoodMart 2000 , [WarehouseLocal]. The [WarehouseLocal] cube is stored in a local cube file, C:\WarehouseLocal.cub, and contains both meta data and data for the [WarehouseLocal] cube. This example does not include slicing or grouping; the statement simply recreates the entire Warehouse cube on the client.
CREATE GLOBAL CUBE [WarehouseLocal]
STORAGE 'C:\WarehouseLocal.cub'
FROM [Warehouse]
(
MEASURE [Warehouse].[Store Invoice],
MEASURE [Warehouse].[Supply Time],
MEASURE [Warehouse].[Warehouse Cost],
MEASURE [Warehouse].[Warehouse Sales],
MEASURE [Warehouse].[Units Shipped],
MEASURE [Warehouse].[Units Ordered],
MEASURE [Warehouse].[Warehouse Profit],
DIMENSION [Warehouse].[Store],
DIMENSION [Warehouse].[Store Size in SQFT],
DIMENSION [Warehouse].[Store Type],
DIMENSION [Warehouse].[Time],
DIMENSION [Warehouse].[Warehouse]
)
Slicing Global Cubes
You can slice global cubes in a number of ways, depending on your business needs. The following example demonstrates simple slicing on the Warehouse cube from FoodMart 2000. The example slices the Time dimension vertically by the [1998] member of the Year level and horizontally by the Year and Month levels.
CREATE GLOBAL CUBE [Warehouse1998Local]
STORAGE 'C:\WarehouseLocal.cub'
FROM [Warehouse]
(
MEASURE [Warehouse].[Store Invoice],
MEASURE [Warehouse].[Supply Time],
MEASURE [Warehouse].[Warehouse Cost],
MEASURE [Warehouse].[Warehouse Sales],
MEASURE [Warehouse].[Units Shipped],
MEASURE [Warehouse].[Units Ordered],
MEASURE [Warehouse].[Warehouse Profit],
DIMENSION [Warehouse].[Store],
DIMENSION [Warehouse].[Store Size in SQFT],
DIMENSION [Warehouse].[Store Type],
DIMENSION [Warehouse].[Time]
(
LEVEL [Year],
LEVEL [Month],
MEMBER [Time].[1998]
),
DIMENSION [Warehouse].[Warehouse]
)
The previous example creates a cube that contains all of the information relevant to the year 1998, with granularity set to the Month level of the Time dimension.
The following example is a bit more complex because it slices the cube along two dimensions and alters the granularity of another dimension by slicing the cube both horizontally and vertically:
CREATE GLOBAL CUBE [Warehouse1998USALocal]
STORAGE 'C:\Warehouse1998USALocal.cub'
FROM [Warehouse]
(
MEASURE [Warehouse].[Units Shipped],
MEASURE [Warehouse].[Units Ordered],
DIMENSION [Warehouse].[Store],
DIMENSION [Warehouse].[Time]
(
LEVEL [Year],
LEVEL [Month],
MEMBER [Time].[1998]
),
DIMENSION [Warehouse].[Warehouse]
(
LEVEL [Country],
MEMBER [Warehouse].[All Warehouses].[USA]
)
)
The previous example changed the structure of the Time dimension by removing the [Quarter] level entirely and using only the [Time].[1998] member and its descendants. The example also sliced the Warehouse level, selecting only the [Country] level and using only the [Warehouse].[All Warehouses].[USA] member. The cube created by this example is highly focused, comparing the volume of shipped unit with the volume of ordered units for 1998 in the USA, organized by the Time, Warehouse, and Store dimensions.
Slicing can greatly simplify complex server-based cubes, allowing client applications to provide a narrower focus on business requirements without sacrificing the ability to obtain more data as needed.
Hiding and Renaming in Global Cubes
You can hide measures, dimensions, and levels by using the HIDDEN visibility qualifier flag in the CREATE GLOBAL CUBE statement. Using the HIDDEN flag prevents the object from being implicitly included in calculations when the global cube is queried. You can reference the object explicitly, however, causing it to be considered when an MDX statement is processed. This feature is often used with the CREATE SESSION CUBE statement to hide dimensions on which derived dimensions are based, but you can also use hiding and renaming with the CREATE GLOBAL CUBE statement for a variety of purposes. One use of hiding in local cubes is to provide users with the capability to optionally consider data when performing "what if" analyses.
You can also rename dimensions, levels, and measures by using the AS keyword when defining them in a CREATE GLOBAL CUBE statement.
The following CREATE GLOBAL CUBE statement renames the [Store Sales] and [Store Sales Net] measures, and hides the [Unit Sales] measure and the entire Time dimension when creating the local cube:
CREATE GLOBAL CUBE [SalesComparison]
STORAGE 'C:\SalesComparison.cub'
FROM Sales
(
MEASURE [Sales].[Unit Sales] HIDDEN,
MEASURE [Sales].[Store Sales] AS [Sales - Gross],
MEASURE [Sales].[Store Sales Net] AS [Sales - Net],
DIMENSION [Sales].[Customers],
DIMENSION [Sales].[Store],
DIMENSION [Sales].[Time] HIDDEN
)
The [Store Sales] measure is renamed [Sales - Gross], the [Store Sales Net] measure is renamed [Sales - Net], and the [Unit Sales] measure and the entire [Time] dimension is hidden. If the following MDX query is executed, no information regarding unit sales is displayed, because the hidden [Unit Sales] measure is not explicitly referenced:
SELECT
Measures.MEMBERS ON COLUMNS,
Store.[All Stores].CHILDREN ON ROWS
FROM
SalesComparison
If you explicitly include the [Unit Sales] measure, you can still obtain information about unit sales. The measure still exists in the cube but is not considered because it is not explicitly referenced.
SELECT
{Measures.MEMBERS, Measures.[Unit Sales]} ON COLUMNS,
Store.[All Stores].CHILDREN ON ROWS
FROM
SalesComparison
One limitation with hidden dimensions and levels is that you cannot include a hidden dimension or level in another global cube. For example, if you create a global cube based on the SalesComparison cube generated earlier in this section, and you attempt to include the Time dimension, the attempt will fail even if you explicitly reference the Time dimension. You can, however, explicitly reference hidden measures. For example, you can create a global cube based on the SalesComparison cube that includes the [Unit Sales] hidden measure.
CREATE SESSION CUBE Statement
Unlike a global cube, a session cube is not persisted. Because of this, the behavior of the session cube is different from that of a global cube. A session cube has restricted slicing capabilities but compensates for this restriction with the ability to use derived dimensions. For more information about derived dimensions, see "Derived Dimensions."
You can also use the CREATE SESSION CUBE statement to create virtual cubes with session scope by specifying the cubes and cube structures to be included in the session cube. Virtual cubes created with the CREATE SESSION CUBE statement can use all of the features associated with session cubes.
You can remove session cubes from memory by using the DROP CUBE statement. For more information about the DROP CUBE statement, see "DROP CUBE Statement" in SQL Server 2000 Books Online.
The following syntax structure, in BNF format, describes the enhanced CREATE SESSION CUBE statement:
BNF
<create cube> ::= CREATE SESSION CUBE <cube name> FROM <cube list> (<param list>) <cube list> ::= <cube name> [, <cube list>] <param list> ::= <param>[, <param list>] <param> ::= <measures list> | <dim list> <measures list> ::= <measure def>[, <measures list>] <dim list> ::= <source dim def> | <derived dim def> [, <derived dim list>] <measure def> ::= MEASURE <cube name>.<measure name> [<visibility qualifier>] [AS <measure name>] <visibility qualifier> ::= HIDDEN <source dim def> ::= DIMENSION <cube name>.<dimension name> [<visibility qualifier>] [AS <dimension name>] [<source dim content def>] <derived dim def> ::= DIMENSION <dimension name> <dim flags> [<visibility qualifier>] [AS <dimension name>] [FROM <dim from clause>] [<derived dim content def>] <dim flags> ::= NOT_RELATED_TO_FACTS <dim from clause> ::= <DM dim from clause> | <reg dim from clause> <DM dim from clause> ::= <DM model name> COLUMN <column name> <reg dim from clause> ::= <dimension name> <source dim content def> ::= (<source level list>[, <grouping list>] [, <member slice list>][, <default member>]) <derived dim content def> ::= (<derived level list>[, <grouping list>] [, <member slice list>][, <default member>]) <source level list> ::= <source level def>[, <source level list>] <derived level list> ::= <derived level def>[, <derived level list>] <grouping list> ::= <grouping entity>[, <grouping list>] <member slice list> ::= <member def>[, <member slice list>] <default member> ::= DEFAULT_MEMBER AS '<MDX expression>' <source level def> ::= LEVEL <level name> [AS <level name>] [<level content def>] <derived level def> ::= LEVEL <level name> [<level type>] [<level content def>] <grouping entity> ::= GROUP <group level name>.<group name> (<group mixed list>) <member def> ::= MEMBER <member name> <group mixed list> ::= <group mixed element>[, <group mixed list>] <group mixed element> ::= <grouping entity> | <member def> <level type> ::= GROUPING <level content def> ::= (<property list>) | NO_PROPERTIES <property list> ::= <property def>[, <property list>] <property def> ::= PROPERTY <property name>
Usage Considerations
The CREATE SESSION CUBE statement has limited slicing capabilities. For more information about these limitations, see "Slicing" and "Slicing Session Cubes."
Examples
The following examples demonstrate some of the features of the CREATE GLOBAL CUBE statement, including slicing, hiding, and the construction of grouping and data mining dimensions.
Creating a Basic Session Cube
The following example creates a session cube based on the Sales cube from FoodMart 2000:
CREATE SESSION CUBE [SalesLocal] FROM Sales
(
MEASURE [Sales].[Unit Sales],
MEASURE [Sales].[Store Sales],
MEASURE [Sales].[Store Sales Net],
DIMENSION [Sales].[Customers],
DIMENSION [Sales].[Store],
DIMENSION [Sales].[Time]
)
In the previous example, only the unit, gross, and net sales organized by the Customer, Store, and Time dimensions were required. The CREATE SESSION CUBE statement selected only those structures in the Sales cube that met the business-specific view of the client application.
Creating Virtual Session Cubes
You can create virtual cubes with the CREATE SESSION CUBE statement. As with other cubes, you must specify the structures to include from all cubes that are involved.
The following example creates a virtual session cube based on the Sales and HR cubes from FoodMart 2000.
CREATE SESSION CUBE [Sales and HR] FROM [Sales], [HR]
(
MEASURE [Sales].[Unit Sales],
MEASURE [HR].[Number of Employees],
DIMENSION [Sales].[Store],
DIMENSION [Sales].[Time],
DIMENSION [HR].[Department]
)
With the virtual session cube created in the previous example, you can now compare information across cubes very quickly, as with the following statement:
WITH MEMBER [Measures].[Sales Per Employee] AS
'Measures.[Unit Sales] / Measures.[Number of Employees]',
FORMAT_STRING = 'Currency'
SELECT
[Measures].ALLMEMBERS ON COLUMNS,
[Store].[CA].CHILDREN ON ROWS
FROM
[Sales and HR]
This simple query determines the average unit sales figure per employee by dividing the [Unit Sales] measure from the [Sales] cube by the [Number of Employees] measure from the [HR] cube. Virtual session cubes can provide much more sophisticated information, but this example demonstrates how you can use virtual session cubes as a fast and versatile tool for ad hoc analysis.
Hiding and Renaming in Session Cubes
As with the CREATE GLOBAL CUBE statement, you can hide measures, dimensions, and levels. Using the HIDDEN flag prevents the object from being implicitly included in calculations when the global cube is queried. The object can be explicitly referenced, however, causing it to be considered when processing an MDX statement. You should include hidden structures in CREATE SESSION CUBE statements only when necessary because these structures use memory.
You can also rename dimensions, levels, and measures by using the AS keyword when defining them in a CREATE SESSION CUBE statement.
The following CREATE SESSION CUBE statement hides the [Unit Sales] measure and the entire Time dimension when creating the local cube:
CREATE SESSION CUBE [SalesLocal] FROM Sales
(
MEASURE [Sales].[Unit Sales] HIDDEN,
MEASURE [Sales].[Store Sales] AS [Sales - Gross],
MEASURE [Sales].[Store Sales Net] AS [Sales - Net],
DIMENSION [Sales].[Customers],
DIMENSION [Sales].[Store],
DIMENSION [Sales].[Time] HIDDEN
)
Hiding dimensions and levels using the CREATE SESSION CUBE has the same effect that it has with the CREATE GLOBAL CUBE statement. Hidden dimensions and levels in a session cube cannot be used to create another session cube. Hidden measures, however, can be used in a new CREATE SESSION CUBE statement.
Slicing Session Cubes
You can slice session cubes in a number of ways, depending on business your need, but the slicing method is slightly different than that used by the CREATE GLOBAL CUBE statement. Session cubes are more restricted in terms of slicing behavior; you must create a grouping dimension in order to slice along a given dimension in a session cube. The following limitations apply when you slice dimensions using a grouping dimension:
You cannot slice a dimension vertically. In other words, you cannot specify a member on which to slice the dimension.
You can slice a dimension horizontally, but you must start with the top level and create a contiguous horizontal slice. In other words, if your dimension contains the levels A, B, C, and D, in that order, the only valid slices that can you can create are:
A
A,B
A,B,C
A,B,C,D
If you attempt to slice a dimension horizontally using a noncontiguous set of levels, the contiguous levels are renamed; within the statement, levels are assigned by ordinal position and not by name. For example, if you sliced the dimension using the levels A and C, you still get the members in levels A and B. The level B level is renamed C. Another example of this behavior is shown later in this section.
The following example demonstrates simple slicing on the Sales cube from FoodMart 2000 by horizontally slicing the Customers dimension to include only the (All), Country, and State Province levels. The slicing is accomplished by constructing a grouping dimension based on the Customers dimension from the Sales cube, and then slicing the grouping dimension horizontally.
CREATE SESSION CUBE [SalesAssignments] FROM [Sales]
(
MEASURE [Sales].[Unit Sales],
DIMENSION [Sales].[Time],
DIMENSION [Sales].[Customers] HIDDEN AS [HiddenCustomers],
DIMENSION [Customers] NOT_RELATED_TO_FACTS FROM [HiddenCustomers]
(
LEVEL [(All)],
LEVEL [Country],
LEVEL [State Province]
)
)
By comparison, the following statement removes the [Country] level from the horizontal slice described in the previous example:
CREATE SESSION CUBE [SalesAssignments] FROM [Sales]
(
MEASURE [Sales].[Unit Sales],
DIMENSION [Sales].[Time],
DIMENSION [Sales].[Customers] HIDDEN AS [HiddenCustomers],
DIMENSION [Customers] NOT_RELATED_TO_FACTS FROM [HiddenCustomers]
(
LEVEL [(All)],
LEVEL [State Province]
)
)
Based on FoodMart 2000, you would expect to see the members from the [State Province] level of the Customers dimension in the Sales cube if you execute the following query:
SELECT
Customers.[State Province].MEMBERS ON COLUMNS,
Time.MEMBERS ON ROWS
FROM
SalesAssignments
Instead, the statement returns the members of the [Country] level. The CREATE SESSION CUBE statement renamed the second level in the slice, which happens to be the [Country] level, to [State Province], because the [Country] level was skipped in the list of levels used to build the horizontal slice.
For more information on grouping dimensions, see "Grouping Dimensions."
Using Grouping Dimensions in Session Cubes
Grouping dimensions can be used to reorganize the structure of a dimension. Such reorganization can include limited slicing, but the primary use of a grouping dimension is to provide access to user-defined member groups. For more information about grouping dimensions, see "Grouping Dimensions."
Within the CREATE SESSION CUBE statement, the GROUPING keyword constructs a grouping level, while the GROUP keyword constructs a member group.
For examples and a more detailed discussion about grouping dimensions, see "Grouping Dimensions."
Using Data Mining Dimensions in Session Cubes
Data mining dimensions are a special type of grouping dimension that are based on the information represented by a data mining model. The node organization of the data mining model is used to construct a grouping dimension, in which each level in the data mining dimension represents each node level within the data mining model, while each member group represents each node at a given node level.
For examples and a more detailed discussion on data mining dimensions, see "Data Mining Dimensions."
ALTER CUBE Statement
For SP1, the ALTER CUBE statement has been enhanced through the addition of the DROP VISUAL TOTALS statement as a supported syntax extension. For more information on the DROP VISUAL TOTALS statement, see "DROP VISUAL TOTALS Statement." The DROP VISUAL TOTALS functionality for the ALTER CUBE statement is identical to that of the DROP VISUAL TOTALS statement. No other changes have been made to the ALTER CUBE statement. For more information on using the ALTER CUBE statement, see SQL Server 2000 Books Online.
The following syntax structure, in BNF format, describes the enhanced ALTER CUBE statement:
BNF
<alter_cube> ::= ALTER CUBE <cube name> <alter_statement>
<alter_stmt> ::= <create_member>|
<drop_member>|
<move_member>|
<update_member>|
<update_dim>|
<drop_vt>
<create_member> ::= CREATE DIMENSION MEMBER
<parent_member_unique_name>.<member_name>
[AS '<MDX_expression>'], KEY='<key_value>'
[<property_list>]
<drop_member> ::= DROP DIMENSION MEMBER
<member_unique_name> [WITH DESCENDANTS]
<move_member> ::= MOVE DIMENSION MEMBER
<member_unique_name>
[, SKIPPED_LEVELS = '<value>']
[WITH DESCENDANTS]
UNDER <member_unique_name>
<update_member> ::= UPDATE DIMENSION MEMBER
<member_unique_name>
{AS '<MDX_expression>' | <property_name> = '<value>'}
[, <property_list>]
<update_dim> ::= UPDATE DIMENSION <dimension_name>,
DEFAULT_MEMBER = '<MDX_expression>'
<drop_vt> ::= DROP VISUAL TOTALS [ON '<MDX_set_expression>']
<property_list> ::= <property_name> = '<value>'[, <property_list>]
<parent_unique name> ::= <member_unique_name>
<member_unique_name> ::= <dimension_name>.&[[]<key>[]]
Examples
The following statement demonstrates the use of the new DROP VISUAL TOTALS syntax in an ALTER CUBE statement:
ALTER CUBE [SalesLocal] DROP VISUAL TOTALS
VisualTotals Function
In Analysis Services the concept of visual totals was originally supported through two mechanisms: Visual Totals mode and the VisualTotals MDX function. These mechanisms are distinguished by the use of state.
Visual Totals mode applies a proprietary algorithm on a query, after the resolution of axes, to determine which members on each axis are viewable. Then the algorithm aggregates totals for cells based only on the viewable members on each axis. For each cell with an aggregated total, that is, visual total, the information is stored in a separate area and referenced from the original cell. The pointer for each cell that has a visual total now points to the area in memory that contains the visual total instead of pointing the area in memory that contains the original total. The user, when referencing the cell, receives the visual total instead of the original total. Because of the referencing behavior of Visual Totals mode, this mechanism retains state and is applied on a dimension-by-dimension basis.
The original VisualTotals MDX function is stateless. Instead of retaining state, it substitutes the visual total for the original total through the use of calculated members. This mechanism makes it easier to apply visual total functionality selectively, including the application of visual totals across dimensions, but at the expense of several features. For example, you cannot refer to member properties and unique member names that are changed as a result of the function, and using the VisualTotals function increases the possibility of skewed results that are based on multiple representations of the same cell in the same set.
To resolve the differences between the two mechanisms, SP1 includes a stateful version of the VisualTotals function, represented by the following syntax:
VisualTotals(«Set»)
The syntax of this version of the VisualTotals function totals the values of the child members specified in «Set» only. Child members not specified in «Set» are not included in the result and, unlike the stateless version of the VisualTotals function, the «Pattern» parameter is no longer necessary. The same mechanism used by the Visual Totals mode supplies such aliasing, so renaming is no longer needed. For more information about the syntax and usage of the stateless version of the VisualTotals function, see "VisualTotals" in SQL Server 2000 Books Online.
To support such selective use of the Visual Totals mode mechanism fully, the DROP VISUAL TOTALS statement was created. For more information about the DROP VISUAL TOTALS statement, see "DROP VISUAL TOTALS Statement."
DROP VISUAL TOTALS Statement
The DROP VISUAL TOTALS statement supports the changes introduced with the expanded VisualTotals MDX function. Given a cube and an optional MDX expression, the DROP VISUAL TOTALS statement can selectively remove the references to visual totals from cells and clear the memory used by visual totals. If the optional MDX set expression is omitted, the visual totals for the entire cube are cleared.
The following syntax structure, in BNF format, describes the enhanced DROP VISUAL TOTALS statement:
BNF
<drop vt> ::= DROP VISUAL TOTALS FOR <cube name> [ON '<MDX set expression>']
Usage Considerations
Support for the DROP VISUAL TOTALS statement is also included in the ALTER CUBE statement. For more information about the ALTER CUBE statement, see "ALTER CUBE Statement."
Examples
The following statement demonstrates the new DROP VISUAL TOTALS syntax:
DROP VISUAL TOTALS FROM [SalesLocal] ON '{[Store].[All Stores]}'
The previous statement drops the visual totals in the [SalesLocal] cube, but only for the set {[Store].[All Stores]}. All other visual totals in the [SalesLocal] cube are unaffected.
Conclusion
The new CREATE GLOBAL CUBE statement and the expanded CREATE SESSION CUBE statement enhance but do not replace the functionality of the existing DDL for Analysis Services. You can create local cubes from existing cubes and use slicing more quickly and easily with the CREATE GLOBAL CUBE statement. The CREATE SESSION CUBE statement increases the flexibility of session cubes by including support for derived dimensions. In combination with a new stateful version of the VisualTotals MDX function, the addition of the DROP VISUAL TOTALS statement and modification of the ALTER CUBE statement allow easier and safer analysis based on the visibility of members.
Finding More Information
SQL Server Books Online contains more information about Analysis Services. For additional information, see these resources:
The Microsoft SQL Server Web site at https://www.microsoft.com/sql.
The Microsoft SQL Server Developer Center at https://msdn2.microsoft.com/sqlserver/default.aspx.
SQL Server Magazine at https://www.sqlmag.com.
The microsoft.public.sqlserver.programming and microsoft.public.sqlserver.datawarehouse newsgroups at news://news.microsoft.com.
The Microsoft Official Curriculum courses on SQL Server. For current course information, see https://www.microsoft.com/trainingandservices.
Appendix A: Syntax Comparison
This appendix contains two DDL statements that reproduce the Sales cube from FoodMart 2000 as a persisted local cube. The first example uses the original CREATE CUBE syntax; the second example uses the new CREATE GLOBAL CUBE syntax. These examples demonstrate the differences in the syntax of the CREATE CUBE and CREATE GLOBAL CUBE statements.
CREATE CUBE Syntax
The CREATE CUBE syntax, while extremely versatile, can be a complex method of recreating cubes that are located on an Analysis server. The following example illustrates the syntax of the CREATE CUBE statement:
PROVIDER=MSOLAP.2;
DATA SOURCE="C:\SalesNew.cub";
SOURCE_PROVIDER=;
SOURCE_DSN="Provider=MSOLAP.2;Location=LocalHost;
Initial Catalog=FoodMart 2000;Connect Timeout=30";
CREATECUBE=CREATE CUBE [LocalSales] (
COMMAND
(
CREATE MEMBER CURRENTCUBE.Measures.[Sales Average]
AS '[Measures].[Store Sales]/[Measures].[Sales Count]',
FORMAT_STRING = 'Standard'
),
COMMAND
(
CREATE MEMBER CURRENTCUBE.Measures.[Profit]
AS '[Measures].[Store Sales]-[Measures].[Store Cost]'
),
COMMAND
(
CREATE ACTION CURRENTCUBE.[Test Action]
FOR CUBE
AS '"SELECT [Customers].MEMBERS ON COLUMNS FROM Sales"',
TYPE ='STATEMENT'
),
DIMENSION [Customers],
LEVEL [All Customers] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Country] OPTIONS( SORTBYNAME ),
LEVEL [State Province] OPTIONS( SORTBYNAME ),
LEVEL [City] OPTIONS( SORTBYNAME ),
LEVEL [Name] OPTIONS( SORTBYNAME ),
PROPERTY [Gender] CAPTION [Gender],
PROPERTY [Marital Status] CAPTION [Marital Status],
PROPERTY [Education] CAPTION [Education],
PROPERTY [Yearly Income] CAPTION [Yearly Income],
PROPERTY [Member Card] CAPTION [Member Card],
DIMENSION [Education Level],
LEVEL [All Education Level] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Education Level] OPTIONS( SORTBYNAME ),
DIMENSION [Gender],
LEVEL [All Gender] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Gender] OPTIONS( SORTBYNAME ),
DIMENSION [Marital Status],
LEVEL [All Marital Status] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Marital Status] OPTIONS( SORTBYNAME ),
DIMENSION [Product],
LEVEL [All Products] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Product Family] OPTIONS( SORTBYNAME ),
LEVEL [Product Department] OPTIONS( SORTBYNAME ),
LEVEL [Product Category] OPTIONS( SORTBYNAME ),
LEVEL [Product Subcategory] OPTIONS( SORTBYNAME ),
LEVEL [Brand Name] OPTIONS( SORTBYNAME ),
LEVEL [Product Name] OPTIONS( SORTBYNAME ),
DIMENSION [Promotion Media],
LEVEL [All Media] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Media Type] OPTIONS( SORTBYNAME ),
DIMENSION [Promotions],
LEVEL [All Promotions] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Promotion Name] OPTIONS( SORTBYNAME ),
DIMENSION [Store],
LEVEL [All Stores] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Store Country] OPTIONS( SORTBYNAME ),
LEVEL [Store State] OPTIONS( SORTBYNAME ),
LEVEL [Store City] OPTIONS( SORTBYNAME ),
LEVEL [Store Name] OPTIONS( SORTBYNAME ),
PROPERTY [Store Manager] CAPTION [Store Manager],
PROPERTY [Store Sqft] CAPTION [Store Sqft],
PROPERTY [Store Type] CAPTION [Store Type],
DIMENSION [Store Size in SQFT],
LEVEL [All Store Size in SQFT] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Store Sqft] OPTIONS( SORTBYNAME ),
DIMENSION [Store Type],
LEVEL [All Store Type] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Store Type] OPTIONS( SORTBYNAME ),
DIMENSION [Time],
LEVEL [Year] OPTIONS( SORTBYNAME ),
LEVEL [Quarter] OPTIONS( SORTBYNAME ),
LEVEL [Month] OPTIONS( SORTBYNAME ),
DIMENSION [Yearly Income],
LEVEL [All Yearly Income] TYPE ALL OPTIONS( SORTBYNAME ),
LEVEL [Yearly Income] OPTIONS( SORTBYNAME ),
MEASURE [Unit Sales] FUNCTION SUM FORMAT 'Standard' TYPE DBTYPE_R8,
MEASURE [Store Cost] FUNCTION SUM FORMAT 'Standard' TYPE DBTYPE_R8,
MEASURE [Store Sales] FUNCTION SUM FORMAT 'Currency' TYPE DBTYPE_R8,
MEASURE [Sales Count] FUNCTION SUM FORMAT '#' TYPE DBTYPE_I8,
MEASURE [Store Sales Net] FUNCTION SUM FORMAT 'Standard' TYPE DBTYPE_R8
);
INSERTINTO=INSERT INTO [LocalSales]
(
[Customers].[Country].KEY,
[Customers].[Country].NAME,
[Customers].[State Province].KEY,
[Customers].[State Province].NAME,
[Customers].[City].KEY,
[Customers].[City].NAME,
[Customers].[Name].KEY,
[Customers].[Name].NAME,
[Customers].[Name].[Gender],
[Customers].[Name].[Marital Status],
[Customers].[Name].[Education],
[Customers].[Name].[Yearly Income],
[Customers].[Name].[Member Card],
[Education Level].[Education Level].KEY,
[Education Level].[Education Level].NAME,
[Gender].[Gender].KEY,
[Gender].[Gender].NAME,
[Marital Status].[Marital Status].KEY,
[Marital Status].[Marital Status].NAME,
[Product].[Product Family].KEY,
[Product].[Product Family].NAME,
[Product].[Product Department].KEY,
[Product].[Product Department].NAME,
[Product].[Product Category].KEY,
[Product].[Product Category].NAME,
[Product].[Product Subcategory].KEY,
[Product].[Product Subcategory].NAME,
[Product].[Brand Name].KEY,
[Product].[Brand Name].NAME,
[Product].[Product Name].KEY,
[Product].[Product Name].NAME,
[Promotion Media].[Media Type].KEY,
[Promotion Media].[Media Type].NAME,
[Promotions].[Promotion Name].KEY,
[Promotions].[Promotion Name].NAME,
[Store].[Store Country].KEY,
[Store].[Store Country].NAME,
[Store].[Store State].KEY,
[Store].[Store State].NAME,
[Store].[Store City].KEY,
[Store].[Store City].NAME,
[Store].[Store Name].KEY,
[Store].[Store Name].NAME,
[Store].[Store Name].[Store Manager],
[Store].[Store Name].[Store Sqft],
[Store].[Store Name].[Store Type],
[Store Size in SQFT].[Store Sqft].KEY,
[Store Size in SQFT].[Store Sqft].NAME,
[Store Type].[Store Type].KEY,
[Store Type].[Store Type].NAME,
[Time].[Year].KEY,
[Time].[Year].NAME,
[Time].[Quarter].KEY,
[Time].[Quarter].NAME,
[Time].[Month].KEY,
[Time].[Month].NAME,
[Yearly Income].[Yearly Income].KEY,
[Yearly Income].[Yearly Income].NAME,
[Measures].[Unit Sales],
[Measures].[Store Cost],
[Measures].[Store Sales],
[Measures].[Sales Count],
[Measures].[Store Sales Net]
)
SELECT
[Sales].[Customers:Country!KEY],
[Sales].[Customers:Country!NAME],
[Sales].[Customers:State Province!KEY],
[Sales].[Customers:State Province!NAME],
[Sales].[Customers:City!KEY],
[Sales].[Customers:City!NAME],
[Sales].[Customers:Name!KEY],
[Sales].[Customers:Name!NAME],
[Sales].[Customers:Name!Gender],
[Sales].[Customers:Name!Marital Status],
[Sales].[Customers:Name!Education],
[Sales].[Customers:Name!Yearly Income],
[Sales].[Customers:Name!Member Card],
[Sales].[Education Level:Education Level!KEY],
[Sales].[Education Level:Education Level!NAME],
[Sales].[Gender:Gender!KEY],
[Sales].[Gender:Gender!NAME],
[Sales].[Marital Status:Marital Status!KEY],
[Sales].[Marital Status:Marital Status!NAME],
[Sales].[Product:Product Family!KEY],
[Sales].[Product:Product Family!NAME],
[Sales].[Product:Product Department!KEY],
[Sales].[Product:Product Department!NAME],
[Sales].[Product:Product Category!KEY],
[Sales].[Product:Product Category!NAME],
[Sales].[Product:Product Subcategory!KEY],
[Sales].[Product:Product Subcategory!NAME],
[Sales].[Product:Brand Name!KEY],
[Sales].[Product:Brand Name!NAME],
[Sales].[Product:Product Name!KEY],
[Sales].[Product:Product Name!NAME],
[Sales].[Promotion Media:Media Type!KEY],
[Sales].[Promotion Media:Media Type!NAME],
[Sales].[Promotions:Promotion Name!KEY],
[Sales].[Promotions:Promotion Name!NAME],
[Sales].[Store:Store Country!KEY],
[Sales].[Store:Store Country!NAME],
[Sales].[Store:Store State!KEY],
[Sales].[Store:Store State!NAME],
[Sales].[Store:Store City!KEY],
[Sales].[Store:Store City!NAME],
[Sales].[Store:Store Name!KEY],
[Sales].[Store:Store Name!NAME],
[Sales].[Store:Store Name!Store Manager],
[Sales].[Store:Store Name!Store Sqft],
[Sales].[Store:Store Name!Store Type],
[Sales].[Store Size in SQFT:Store Sqft!KEY],
[Sales].[Store Size in SQFT:Store Sqft!NAME],
[Sales].[Store Type:Store Type!KEY],
[Sales].[Store Type:Store Type!NAME],
[Sales].[Time:Year!KEY],
[Sales].[Time:Year!NAME],
[Sales].[Time:Quarter!KEY],
[Sales].[Time:Quarter!NAME],
[Sales].[Time:Month!KEY],
[Sales].[Time:Month!NAME],
[Sales].[Yearly Income:Yearly Income!KEY],
[Sales].[Yearly Income:Yearly Income!NAME],
[Sales].[Measures:Unit Sales],
[Sales].[Measures:Store Cost],
[Sales].[Measures:Store Sales],
[Sales].[Measures:Sales Count],
[Sales].[Measures:Store Sales Net]
FROM
[Sales];
CREATE GLOBAL CUBE Syntax
The CREATE GLOBAL CUBE syntax greatly simplifies the process of constructing a local cube from a cube located on an Analysis server. The following example illustrates the syntax of the CREATE GLOBAL CUBE statement:
CREATE GLOBAL CUBE SalesLocal STORAGE 'C:\SalesLocal.cub' FROM [Sales] ( MEASURE [Sales].[Unit Sales], MEASURE [Sales].[Store Cost], MEASURE [Sales].[Store Sales], MEASURE [Sales].[Sales Count], MEASURE [Sales].[Store Sales Net], DIMENSION [Sales].[Customers], DIMENSION [Sales].[Education Level], DIMENSION [Sales].[Gender], DIMENSION [Sales].[Marital Status], DIMENSION [Sales].[Product], DIMENSION [Sales].[Promotion Media], DIMENSION [Sales].[Promotions], DIMENSION [Sales].[Store], DIMENSION [Sales].[Store Size in SQFT], DIMENSION [Sales].[Store Type], DIMENSION [Sales].[Time], DIMENSION [Sales].[Yearly Income] )