OLAP

出自ProgWiki

參照：『維基百科～OLAP』

目錄 1 維度 2 CUBE 2.1 導出成員 2.2 命名集

維度

• 時間維度用的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])