Datawarehouse question
-
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?
-
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
-
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply