See Also: Main_Page - Database Administration - Analysis Services - MDX with Analysis Services
The WITH clause always precedes the SELECT statement and defines a member or set used within the SELECT. You could also usethe WITH clause to define a cell calculation or try optimizing query performance using the "WITH CACHE" construct, but these two options are seldom used. The WITH clause can contain declarations of multiple sets and multiple members. The order of declarations isn't important as long as the declared member does not reference another member that hasn't already been declared.
If you happen to use the same WITH clause to define members in your queries you could probably benefit from adding calculated members to your cube; similarly if you keep re-using a set definition you should probably add a named set.
The following example defines a simple calculation using the WITH clause, then uses the calculation in the SELECT statement:
WITH member [measures].[internet profit] AS
'[measures].[internet sales amount]-[measures].[internet total product cost]'
SELECT {[measures].[internet profit],
[measures].[internet sales amount],
[measures].[internet total product cost]} ON COLUMNS,
{[product].[category].children} ON ROWS
FROM [adventure works]
Results:
| internet profit | Internet Sales Amount | Internet Total Product Cost | | Accessories | $438,674.57 | $700,759.96 | $262,085.39 | | Bikes | $11,505,796.50 | $28,318,144.65 | $16,812,348.15 | | Clothing | $136,412.58 | $339,772.61 | $203,360 | | Components | (null) | (null) | (null) |
Now suppose you wanted to project profits for the next year's sales. You anticipate the sales amount to grow by 15% but product cost to only increase by 5%; to include the anticipated profit in the output you would rewrite the query as follows:
WITH member [measures].[internet profit] AS
'[measures].[internet sales amount]-
[measures].[internet total product cost]'
member [measures].[anticipated profit] AS
'([measures].[internet sales amount]*1.15 -
[measures].[internet total product cost]*1.05)'
SELECT {[measures].[internet profit],
[measures].[anticipated profit]} ON COLUMNS,
{[product].[category].children} ON ROWS
FROM [adventure works]
Results:
| internet profit | anticipated profit | | Accessories | $438,674.57 | $530,684.29 | | Bikes | $11,505,796.50 | $14,912,900.79 | | Clothing | $136,412.58 | $177,210.47 | | Components | (null) | (null) |
You can use the members declared using the WITH clause to declare other members. For instance if you anticipated current profits to double next year you could change the anticipated profit calculation as follows:
WITH member [measures].[internet profit] AS
'[measures].[internet sales amount]-
[measures].[internet total product cost]'
/* reuse [internet profit] measure */
member [measures].[anticipated profit] AS
'([measures].[internet profit]*2)'
SELECT {[measures].[internet profit],
[measures].[anticipated profit]} ON COLUMNS,
{[product].[category].children} ON ROWS
FROM [adventure works]
The output of the previous two queries included empty members for components since they haven't been sold over the internet. You could use the COALESCEEMPTY function to return zero for those product categories that have not had any internet sales as follows:
WITH member measures.[nonempty internet sales]
AS 'COALESCEEMPTY([measures].[internet sales amount], 0)',
format_string='currency'
member measures.[nonempty internet cost]
AS 'COALESCEEMPTY([measures].[internet total product cost], 0)',
format_string='currency'
member [measures].[internet profit] AS
'[measures].[nonempty internet sales]-
measures.[nonempty internet cost]'
SELECT {[measures].[internet profit],
[measures].[nonempty internet sales],
measures.[nonempty internet cost]} ON COLUMNS,
{[product].[category].children} ON ROWS
FROM [adventure works]
Results:
| internet profit | nonempty internet sales | nonempty internet cost | | Accessories | $438,674.57 | $700,759.96 | $262,085.39 | | Bikes | $11,505,796.50 | $28,318,144.65 | $16,812,348.15 | | Clothing | $136,412.58 | $339,772.61 | $203,360.03 | | Components | $0.00 | $0.00 | $0.00 |
Note the usage of the FORMAT_STRING option to return values as currency. If you don't supply this option for your calculations in this query then "$" sign and commas will not be shown in the output.
Now let's suppose you wanted to define a set of all product categories except "components". Since you know that there weren't any internet sales for components this category isn't interesting. We could define the set as follows:
WITH SET [categories other than components] AS
'{[product].[category].[bikes],
[product].[category].[clothing],
[product].[category].[accessories]}'
This solution would work, but specifying every member that you want to display in the result set can get tedious. In this case we only needed three product categories, but if you wanted to filter out one product out of a thousand typing the rest of the product names would be impractical. Instead you could use the following query:
WITH
member measures.[nonempty internet sales]
AS 'COALESCEEMPTY([measures].[internet sales amount], 0)',
format_string='currency'
member measures.[nonempty internet cost]
AS 'COALESCEEMPTY([measures].[internet total product cost], 0)',
format_string='currency'
member [measures].[internet profit] AS
'[measures].[nonempty internet sales]-
measures.[nonempty internet cost]'
SET [categories other than components] AS
'FILTER({[product].[category].members},
[product].[category].CurrentMember.Name<>"Components")'
SELECT {[measures].[internet profit],
[measures].[nonempty internet sales],
measures.[nonempty internet cost]} ON COLUMNS,
{[categories other than components]} ON ROWS
FROM [adventure works]
Results:
| internet profit | nonempty internet sales | nonempty internet cost | | All Products | $12,080,883.65 | $29,358,677.22 | $17,277,793.58 | | Accessories | $438,674.57 | $700,759.96 | $262,085.39 | | Bikes | $11,505,796.50 | $28,318,144.65 | $16,812,348.15 | | Clothing | $136,412.58 | $339,772.61 | $203,360.03 |
You can also define sets that consist of the output of functions that return sets. For example, you know that CROSSJOIN function returns a set; therefore you could write the following query to examine internet sales by product model name for each country:
WITH SET [models over years] AS
'CROSSJOIN({product.[model name].members},
{[date].[calendar year].members})'
SELECT [models over years] ON ROWS,
{geography.[country].children} ON COLUMNS
FROM [adventure works]
Another good use of the WITH clause is retrieving the values of member properties. For example, if you wanted to retrieve the product line for each member in your product dimension you could use the following query:
WITH member [measures].[product line] AS
'[product].[model name].Properties("product line")'
SELECT {measures.[product line]} ON COLUMNS,
{product.[model name].members} ON ROWS
FROM [adventure works]
Results (abbreviated):
| product line | | All-Purpose Bike Stand | Mountain | | Bike Wash | Accessory | | Cable Lock | Accessory | | Chain | Components | | Classic Vest | Accessory | | Cycling Cap | Accessory | | Fender Set Mountain | Mountain | | Front Brakes | Components | | Front Derailleur | Components | | Full-Finger Gloves | Mountain | | Half-Finger Gloves | Accessory | | Headlights - Dual-Beam | Road | | Headlights Weatherproof | Road | | Hitch Rack - 4-Bike | Accessory | | HL Bottom Bracket | Components |
The ability to bring back member properties allows you to present rich metadata on your reports. For example, the following query shows the color, size, weight, list price, dealer price, subcategory and category of each product along with the respective sales amount in 2004:
WITH member measures.[product color] AS
'[product].[product].Properties("Color")'
member measures.[dealer price] AS
'[product].[product].Properties("dealer price")'
member measures.[size] AS
'[product].[product].Properties("size")'
member measures.[weight] AS
'[product].[product].Properties("weight")'
member measures.[list price] AS
'[product].[product].Properties("list price")'
member [measures].[product subcategory] AS
'product.[product categories].CurrentMember.Parent.Name'
member [measures].[product category] AS
'product.[product categories].CurrentMember.Parent.Parent.Name'
SELECT {
measures.[product subcategory],
measures.[product category],
measures.[size],
measures.[weight],
measures.[product color],
measures.[dealer price],
measures.[list price],
measures.[internet sales amount]} ON COLUMNS,
{product.[product categories].[product name].members} ON ROWS
FROM [adventure works]
WHERE ([date].[cy 2004])
Resutls (abbreviated):
| product subcategory | product category | size | weight | product color | Dealer price | list price | Internet Sales Amount | | Mountain-400-W Silver, 38 | Mountain Bikes | Bikes | 38 | 26.35 | Silver | $461.69 | $769.49 | $70,023.59 | | Mountain-400-W Silver, 40 | Mountain Bikes | Bikes | 40 | 26.77 | Silver | $461.69 | $769.49 | $60,020.22 | | Mountain-400-W Silver, 42 | Mountain Bikes | Bikes | 42 | 27.13 | Silver | $461.69 | $769.49 | $62,328.69 | | Mountain-400-W Silver, 46 | Mountain Bikes | Bikes | 46 | 27.42 | Silver | $461.69 | $769.49 | $66,176.14 | | Mountain-500 Silver, 40 | Mountain Bikes | Bikes | 40 | 27.35 | Silver | $338.99 | $564.99 | $16,949.70 | | Mountain-500 Silver, 42 | Mountain Bikes | Bikes | 42 | 27.77 | Silver | $338.99 | $564.99 | $15,254.73 | | Mountain-500 Silver, 44 | Mountain Bikes | Bikes | 44 | 28.13 | Silver | $338.99 | $564.99 | $12,429.78 | | Mountain-500 Silver, 48 | Mountain Bikes | Bikes | 48 | 28.42 | Silver | $338.99 | $564.99 | $16,384.71 | | Mountain-500 Silver, 52 | Mountain Bikes | Bikes | 52 | 28.68 | Silver | $338.99 | $564.99 | $14,689.74 | | Mountain-500 Black, 40 | Mountain Bikes | Bikes | 40 | 27.35 | Black | $323.99 | $539.99 | $13,499.75 | | Mountain-500 Black, 42 | Mountain Bikes | Bikes | 42 | 27.77 | Black | $323.99 | $539.99 | $14,579.73 | | Mountain-500 Black, 44 | Mountain Bikes | Bikes | 44 | 28.13 | Black | $323.99 | $539.99 | $17,819.67 | | Mountain-500 Black, 48 | Mountain Bikes | Bikes | 48 | 28.42 | Black | $323.99 | $539.99 | $20,519.62 | | Mountain-500 Black, 52 | Mountain Bikes | Bikes | 52 | 28.68 | Black | $323.99 | $539.99 | $15,119.72 | | Road-550-W Yellow, 48 | Road Bikes | Bikes | 48 | 18.68 | Yellow | $672.29 | $1,120.49 | $128,856.35 | | Road-350-W Yellow, 40 | Road Bikes | Bikes | 40 | 15.35 | Yellow | $1,020.59 | $1,700.99 | $267,055.43 | | Road-350-W Yellow, 42 | Road Bikes | Bikes | 42 | 15.77 | Yellow | $1,020.59 | $1,700.99 | $261,952.46 | | Road-350-W Yellow, 44 | Road Bikes | Bikes | 44 | 16.13 | Yellow | $1,020.59 | $1,700.99 | $248,344.54 | | Road-350-W Yellow, 48 | Road Bikes | Bikes | 48 | 16.42 | Yellow | $1,020.59 | $1,700.99 | $261,952.46 | | Road-750 Black, 58 | Road Bikes | Bikes | 58 | 20.79 | Black | $323.99 | $539.99 | $104,218.07 | | Road-750 Black, 44 | Road Bikes | Bikes | 44 | 19.77 | Black | $323.99 | $539.99 | $112,317.92 | | Road-750 Black, 48 | Road Bikes | Bikes | 48 | 20.13 | Black | $323.99 | $539.99 | $109,617.97 |
Note that members you define using the WITH clause can belong to any dimension. This allows the ability to include the declared members on either axis and do some interesting comparisons. For example, the following query declares a member within [date] dimension and compares sales in two years:
WITH member [date].[calendar year].[growth 2003 to 2004] AS
'([date].[cy 2004]-[date].[cy 2003])'
SELECT {[measures].[internet sales amount],
[measures].[internet order count],
[measures].[sales amount],
[measures].[order count],
[measures].[reseller sales amount],
[measures].[reseller order count]} ON ROWS,
{[date].[calendar year].[cy 2003], [date].[calendar year].[cy 2004],
[date].[calendar year].[growth 2003 to 2004]} ON COLUMNS
FROM [adventure works]
Results:
| CY 2003 | CY 2004 | growth 2003 to 2004 | | Internet Sales Amount | $9,791,060.30 | $9,770,900 | ($20,160.56) | | Internet Order Count | 10,919.00 | 13,050 | 2,131 | | Sales Amount | $41,993,729.72 | $25,808,962 | ($16,184,767.39) | | Order Count | 12,440 | 13,944 | 1,504 | | Reseller Sales Amount | $32,202,669.43 | $16,038,062.60 | ($16,164,606.83) | | Reseller Order Count | 1,521 | 894 | -627 |
You could use the WITH clause to define a set that should be cached for the duration of the current query. Doing so is supposed to optimize the performance of the current query at the expense of creating a memory overhead. We recommend using the WITH CACHE construct sparingly after thorough testing and if there is no other way to optimize your queries. Typically you will see only marginal benefit from using WITH CACHE. The syntax for caching sets is simple: WITH CACHE AS '(set1, set2 …, setN). For example, you could rewrite the above query including WITH CACHE statement as follows:
WITH CACHE AS '(measures.members, {[date].[calendar year].children})'
member [date].[calendar year].[growth 2003 to 2004] AS
'([date].[cy 2004]-[date].[cy 2003])'
SELECT {[measures].[internet sales amount],
[measures].[internet order count],
[measures].[sales amount],
[measures].[order count],
[measures].[reseller sales amount],
[measures].[reseller order count]} ON ROWS,
{[date].[calendar year].[cy 2003], [date].[calendar year].[cy 2004],
[date].[calendar year].[growth 2003 to 2004]} ON COLUMNS
FROM [adventure works]
|