OLAP

出自 ProgWiki
前往: 導覽搜尋

參照:『維基百科~OLAP

維度

  • 時間維度用的Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimTime](
	[TimeKey] [int] IDENTITY(1,1) NOT NULL,
	[FullDateAlternateKey] [datetime] NULL,
	[DayNumberOfWeek] [tinyint] NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NULL,
	[SpanishDayNameOfWeek] [nvarchar](10) NULL,
	[FrenchDayNameOfWeek] [nvarchar](10) NULL,
	[DayNumberOfMonth] [tinyint] NULL,
	[DayNumberOfYear] [smallint] NULL,
	[WeekNumberOfYear] [tinyint] NULL,
	[EnglishMonthName] [nvarchar](10) NULL,
	[SpanishMonthName] [nvarchar](10) NULL,
	[FrenchMonthName] [nvarchar](10) NULL,
	[MonthNumberOfYear] [tinyint] NULL,
	[CalendarQuarter] [tinyint] NULL,
	[CalendarYear] [char](4) NULL,
	[CalendarSemester] [tinyint] NULL,
	[FiscalQuarter] [tinyint] NULL,
	[FiscalYear] [char](4) NULL,
	[FiscalSemester] [tinyint] NULL,
 CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED 
(
	[TimeKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED 
(
	[FullDateAlternateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING ON


CUBE

導出成員

  • 毛利
[Measures].[Sales Amount]-[Measures].[Total Product Cost]
[Measures].[售價]-[Measures].[成本]
  • 毛利率
([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount]
防呆版本(避免除號為0)
IIF([Measures].[Sales Amount]=0,null, ([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])
IIF([Measures].[售價]=0,null, ([Measures].[售價]-[Measures].[成本])/[Measures].[售價])


  • 去年同期
(ParallelPeriod([時間].[時間階層].[日曆年],1,[時間].[時間階層].CurrentMember),[Measures].[Unit Price])
  • 年累計
Sum(PeriodsToDate([時間].[時間階層].[日曆年],[時間].[時間階層].CurrentMember),[Measures].[Unit Price])
  • 三期移動平均
Avg({[時間].[時間階層].CurrentMember.Lag(2):[時間].[時間階層].CurrentMember},[Measures].[Unit Price])

命名集

  • 銷售前10名產品
TopCount([產品].[產品階層].[產品名稱].Members,10,[Measures].[Sales Amount])