Sunday, January 08, 2006

Managing Analysis Services Partitions with Table-Valued Functions (table UDFs)

Managing Analysis Services Partitions with Table-Valued Functions (table UDFs): "Here's an Adventure Works cube example:

1.) Create the following function in the AdventureWorksDW database:
----------------------
CREATE FUNCTION dbo.udf_FactInternetSales_Date_Range
(@Start_OrderDateKey INT, @End_OrderDateKey INT)
RETURNS table
AS
RETURN
(
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDateKey >= @Start_OrderDateKey
AND OrderDateKey <= @End_OrderDateKey
)
----------------------

2.) Open up BIDS and the Adventure Work cubes and browse to the Partitions tab.

3.) Under the 'Internet Sales' measure group partitions, change the Internet_Sales_2004 source query to:

SELECT * FROM dbo.udf_FactInternetSales_Date_Range(915,1280)

4.) Do the same for the other partitions, except change the input OrderDateKey ranges passed into the UDF."

No comments: