Promotion Dimension Querying

Question

Promotion Dimension Querying

gmontanaro

SSC-Addicted

Points: 478
More actions
September 22, 2014 at 10:12 am

#299610

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

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?

Is your DimRebate a type 1 dimension -- that is your rebate key gets updated/overwritten in the FactRetailSales table when the rebate expires, or is DimRebate a slowly changing dimension (type 2) where you store your rebate change history. I'm guessing the later.

Then you'd just query any distinct records that have a description other than "No Rebate in Effect".

HTH,

Rob

Lempster SSCoach Points: 15651 More actions · Answer 2

robert.gerald.taylor (9/23/2014)
Then you'd just query any distinct records that have a description other than "No Rebate in Effect".
HTH,
Rob

I think the OP would need to include 'No Rebate in Effect' too because they want to see sales of the rebate item(s) before, during and after the rebate period. Clearly, the DateKey (InvoiceDateKey in this case) is important and could be used a a filter.

Regards

Lempster

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 3

Can you provide a concrete examp of this? I'm unsure why you don't simply add to the rows of the fact. I assume there's also a product dimension that could be crossed to find out which were purchased with what rebate and which were purchased without one.