SSAS Cube 入门之准备工作

1.安装SQL Server  2017 ，点击

2.安装SSDT(SQL Server Data Tools (SSDT) for Visual Studio)，点击

3.新建数据库、创建表、插入数据

 1.1. 安装时需要安装Analysis Services服务，并且安装过程中，需要选择Multidimensional and Data Mining Mode，如下图。

1.2. 如果已经安装AS服务后，需要检查下：AS服务的服务器模式是否为"Multidimensional"，查看步骤如下：

   1.2.1. 连接AS服务器，右键-》属性-》信息

  1.2.1.如果服务器模式为“Tabular（表格）”，可通过以下操作修改：

　　D:\SQL server\MSAS14.MSSQLSERVER\OLAP\Config，不同的安装方式目录不同，这个Config一开始你是无权访问的，需要修改权限
　　在这个文件夹下面会有一个 **msmdsrv.ini** 的文件，找到**0** 不同数字意义不同
　　有效值为 0（多维）、1 (SharePoint) 或 2（表格）

   详情行进入微软官网：点击      到此就修改完毕了，如果不行，就重启。

3.1.创建表业务表语句

IF OBJECT_ID('Category','U') IS NOT NULL
DROP TABLE Category
GO

CREATE TABLE Category
(
    CategoryID int IDENTITY (1,1) PRIMARY KEY ,
    CategoryName nvarchar(100) not null
)

IF OBJECT_ID('Product','U') IS NOT NULL
DROP TABLE Product
GO

CREATE TABLE Product
(
    ProductId int IDENTITY (1,1) PRIMARY KEY ,
    ProductName nvarchar(100) not null,
    ProductDesc nvarchar(100) not null,
    Price    float,
    CategoryID int not null,
    foreign key(CategoryID) references Category(CategoryID)
)

IF OBJECT_ID('OrderItem','U') IS NOT NULL
DROP TABLE OrderItem
GO

CREATE TABLE OrderItem
(
    ItemId int IDENTITY (1,1) PRIMARY KEY ,
    OrderNo nvarchar(100) not null,
    ProductId int not null,
    OrderTime    DateTime,
    OrderDate nvarchar(100),
    Qty        int,
    TotalAmount float,
    foreign key(ProductId) references Product(ProductId)
)

insert into Category values('书籍')
insert into Category values('水果')
insert into Category values('工具')

insert into Product values('A200034','平凡的世界',35.5,1)
insert into Product values('A200035','孤独',20.2,1)
insert into Product values('A200036','水浒传',34.3,1)
insert into Product values('A200037','西瓜',35.5,2)
insert into Product values('A200038','脐橙',20.2,2)
insert into Product values('A200039','尺子',34.3,3)

insert into OrderItem values('SO20210223001',1,'2021-02-23 15:20:00.000','2021-02-23',2,71)
insert into OrderItem values('SO20210223001',2,'2021-02-23 15:20:00.000','2021-02-23',1,20.2)
insert into OrderItem values('SO20210223001',1,'2021-02-20 10:21:21.000','2021-02-20',1,35.5)
insert into OrderItem values('SO20210223002',3,'2021-02-20 10:21:21.000','2021-02-20',1,34.3)
insert into OrderItem values('SO20210223002',6,'2021-02-20 10:21:21.000','2021-02-20',2,68.6)
insert into OrderItem values('SO20210223003',4,'2021-01-19 10:21:21.000','2021-01-19',1,35.5)

3.2.创建时间表语句

IF OBJECT_ID('DimDate','U') IS NOT NULL
DROP TABLE DimDate
GO

CREATE TABLE DimDate
(
    DateKey  NVARCHAR(20) PRIMARY KEY,
    FullDate DATE NOT NULL,
    [DateName] NVARCHAR(20),
    DayNumberOfWeek TINYINT NOT NULL,
    DayNameOfWeek NVARCHAR(10) NOT NULL,
    DayNumberOfMonth TINYINT NOT NULL,
    DayNumberOfYear SMALLINT NOT NULL,
    IsWeekend BIT NOT NULL,
    IsLeapYear BIT NOT NULL,
    WeekNumberOfYear TINYINT NOT NULL,
    EnglishMonthName NVARCHAR(10) NOT NULL,
    MonthNumberOfYear TINYINT NOT NULL,
    CalendarQuarter TINYINT NOT NULL,
    CalendarSemester TINYINT NOT NULL,
    CalendarYear SMALLINT NOT NULL, 
    FiscalQuarter TINYINT NOT NULL,
    FiscalSemester TINYINT NOT NULL,
    FiscalYear SMALLINT NOT NULL 
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '2020-01-01',
       @EndDate = '2025-12-31'

WHILE (@StartDate <= @EndDate)
BEGIN
    INSERT INTO DimDate 
    (
        DateKey,
        FullDate,
        [DateName],
        DayNumberOfWeek,
        DayNameOfWeek,
        DayNumberOfMonth,
        DayNumberOfYear,
        IsWeekend,
        IsLeapYear,
        WeekNumberOfYear,
        EnglishMonthName, 
        MonthNumberOfYear,
        CalendarQuarter,
        CalendarSemester,
        CalendarYear, 
        FiscalQuarter,
        FiscalSemester,
        FiscalYear 
    )
    SELECT CONVERT(VARCHAR(10), @StartDate,20) AS DateKey,
           CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,
           CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],
           DATEPART(DW,@StartDate) AS DayNumberOfWeek,
           DATENAME(DW,@StartDate) AS DayNameOfWeek,
           DATENAME(DD,@StartDate) AS [DayOfMonth],
           DATENAME(DY,@StartDate) AS [DayOfYear],
           CASE WHEN DATEPART(DW,@StartDate) IN (1,7)  
                    THEN 1
                ELSE 0 
           END AS IsWeekend,
           CASE WHEN ((YEAR(@StartDate) % 4 = 0) AND (YEAR(@StartDate) % 100 != 0 OR YEAR(@StartDate) % 400 = 0))
                    THEN 1
                ELSE 0
           END AS IsLeapYear,
           DATEPART(WW,@StartDate) AS WeekNumberOfYear,
           DATENAME(MM,@StartDate) AS EnglishMonthName,
           DATEPART(MM,@StartDate) AS MonthNumberOfYear,
           DATEPART(QQ,@StartDate) AS CalendarQuarter,
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN 1
                ELSE 2
           END AS CalendarSemester,
           DATEPART(YY,@StartDate) AS CalendarYear, 
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                     THEN DATEPART(QQ,@StartDate) + 2
                ELSE DATEPART(QQ,@StartDate) - 2
           END AS FiscalQuarter,
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN 2
                ELSE 1
           END AS FiscalSemester,
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN DATEPART(YY,@StartDate) 
                ELSE DATEPART(YY,@StartDate) + 1
           END AS FiscalYear
            
    SET @StartDate = @StartDate + 1
END
GO

到此准备工作完成。