gmontanaro (9/22/2014)

---

I have a Retail Sales Fact table and a dimension table called DimRebate.

CREATE TABLE [dbo].[FactRetailSales]

([LocationKey] [int] NOT NULL,

[InvoiceDateKey] [int] NOT NULL,

[ItemKey] [int] NOT NULL,

[RebateKey] [int] NOT NULL,

[InvoiceNumber] [int] NOT NULL,

[SalesQuantity] [int] NOT NULL,

[UnitPrice] [decimal](12, 4) NOT NULL,

[ExtendedStoreCostAmount] [decimal](12, 4) NOT NULL,

[ExtendedSalesAmount] [decimal](12, 4) NOT NULL,

[ExtendedGPAmount] [decimal](12, 4) NOT NULL

CREATE TABLE [dbo].[DimRebate]

([RebateKey] [int] NOT NULL,

[RebateId] [int] NOT NULL, --NK

[RebateDescription] [varchar] (100) NOT NULL,

[RebateStartDate] [date] NOT NULL,

[RebateExpirationDate] [date] NOT NULL

When a rebate is in effect, the item that it applies to will get the correlating RebateKey. Once the rebate has expired, the Fact Table rows will get a "No Rebate In Effect" RebateKey.

My question is how would you write a query to show sales of all rebate items...before, during, and after the rebate?

I can think of a way in a Stored Procedure, first creating a subset of Items that have ever had a rebate, then joining to that table to narrow the list down.

Is there a SSAS process that can do this intelligently?

The way my brain works it out, I keep wanting to move toward a Snowflake design, sliding the rebate dimension off of the ItemDimension, but I'm wary of this approach.

Thoughts? Need more clarity?