Datawarehouse question

Question

Datawarehouse question

SQL_Surfer

SSCrazy Eights

Points: 8318
More actions
April 21, 2021 at 11:58 pm

#3874116

So I have one dimension table and a fact table.
dimension table has surrogatekey, fiscalyear and businessplan attributes.
(Fiscalyear=2021)
fact table has invoice level facts and businessplan surrogate key in there, which works fine.
Now, business has additional requirements. They want to come up new business plan for new fiscal year (2022). This new year data isn’t in invoice line yet. This can be loaded easily in the dimension table, no problem.
But they want to see what would be their sale look like in 2021 if they had adopted 2022 business plan.

Any thoughts on how can we accomplish this?

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

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Any thoughts on how can we accomplish this?

None whatsoever. You haven't provided DDL, sample data in the form of INSERT statements and desired output based on your sample data, nor have you explained how to perform the desired projection or what the metrics of the business plan are composed of. With >8,000 points, I'm rather surprised that you think anyone would be able to provide a useful response based on such a meagre post.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL_Surfer SSCrazy Eights Points: 8318 More actions · Answer 2

I knew that would be coming. Sorry about that. SO, here's the DDL with sample data.

IF OBJECT_ID('TempDB..#PlanTargets','U') IS NOT NULL

DROP TABLE #PlanTargets

IF OBJECT_ID('TempDB..#InvLine','U') IS NOT NULL

DROP TABLE #InvLine

CREATE TABLE #PlanTargets(

[CSegm] [varchar](25) NULL,

[CSubSegm] [varchar](16) NOT NULL,

[ICat] [varchar](28) NOT NULL,

Year1 int not null,

[PlanKey] [bigint] IDENTITY(1,1) NOT NULL

)

GO

CREATE TABLE #InvLine (invlinekey int, invcustomer int, salesamount int, invoicedate date, Plankey int)

insert into #Plantargets values('abc', 'def', 'cde', 2020)

insert into #Plantargets values('xx', 'yy', 'ee', 2020)

insert into #Plantargets values('cde', 'def', 'zz', 2021)

insert into #Plantargets values('ff', 'g', 'e', 2021)

insert into #invline values(1,1,2, '2020-01-01', 1)

insert into #invline values(2,2,4, '2020-01-02', 2)

select * from #Plantargets

select * from #InvLine

If you notice, I have 2021 data in #Plantargets but nothing in #invline for 2021. What business like to see if what would be my sale look like in 2020, had i incorporated 2021 plan in it.

Let me know if this is not clear. For clarity, #PlanTargets here is like a Dim table and #invline is Fact table.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Here are the results of the queries you provided. Now tell us what the logic is to get from here to the results you require, as it is not clear to me. Also provide the results you require, based on this test data.

2021-04-26_10-47-55

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.