iT邦幫忙

0

請問在SSAS上如何創建cube?(visual studio)

因為工作的關係想說來研究一下SSAS怎麼用
就先試試看建一個簡單的時間維度能不能run
後來出現錯誤 如下:
https://ithelp.ithome.com.tw/upload/images/20191225/20123840l8eHujFW3b.png

感覺是時間維度沒有設定好

後來發現我時間維度裡是沒有資料的
不知道跟這個有沒有關係
但我也是依網路上文章中步驟照做
文章都是用維度精靈很快下一步就建好時間維度表

建立方法我是在資料來源中產生時間資料表
https://ithelp.ithome.com.tw/upload/images/20191225/201238406VprhUHMN9.png

以下是關聯性與cube

https://ithelp.ithome.com.tw/upload/images/20191225/201238408fGSI7dR0i.png
https://ithelp.ithome.com.tw/upload/images/20191225/20123840DGK5i4lTgH.png

求各位大大幫忙解惑~

1 個回答

0
twyes
iT邦新手 4 級 ‧ 2019-12-26 14:07:41

推薦你去看一本書
https://www.tenlong.com.tw/products/9789866348044

這本書已經絕版,去圖書館借,裡面有很多範例,照著做
建立Cube不難

我看你的錯誤訊息應該是 日期主Key 重複
建議你 不要用 在資料來源中產生時間資料表 , 這個不好用
可以用下面這個語法建一個時間Table (D_Date)


CREATE TABLE [dbo].[D_DATE](
	[ID_Date] VARCHAR(8) NOT NULL,
	[Date] [date] NOT NULL,
	[Year] VARCHAR(6) NOT NULL,
	[MonthNumber] varchar(2) NOT NULL,
	[Month] [varchar](10) NOT NULL,
	[DayOfMonth] varchar(2) NOT NULL,
	[DayOfWeekNumber] [smallint] NOT NULL,
	[DayOfWeek] [varchar](10) NOT NULL,
	[WorkingDay] [bit] NULL ,
	PRIMARY KEY (ID_DATE)
) ON [PRIMARY]

GO



DECLARE @StartYear AS INT = 2019 ; 
DECLARE @EndYear AS INT = 2020 ;

WITH Years 
    AS ( SELECT YYYY = @StartYear 
    UNION ALL 
    SELECT YYYY + 1 
    FROM Years 
    WHERE YYYY < @EndYear 
    ), 
Months 
    AS ( SELECT MM = 1 
    UNION ALL 
    SELECT MM + 1 
    FROM Months 
    WHERE MM < 12 
    ), 
Days 
    AS ( SELECT DD = 1 
    UNION ALL 
    SELECT DD + 1 
    FROM Days 
    WHERE DD < 31 
    ), 
DatesRaw 
    AS ( SELECT YYYY = YYYY, 
    MM = MM, 
    DD = DD, 
    ID_Date = YYYY * 10000 + MM * 100 + DD, 
    Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 
    THEN CAST(CAST(YYYY * 10000 + MM * 100 
    + DD AS VARCHAR) AS DATE) 
    ELSE NULL 
    END 
    FROM Years 
    CROSS JOIN Months 
    CROSS JOIN Days 
    WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 
    ) 
insert into D_DATE
SELECT d.ID_Date, 
    d.Date, 
    [Year] = YEAR(d.Date), 
    MonthNumber = MONTH(d.Date), 
    [Month] = DATENAME(MONTH, d.Date), 
    DayOfMonth = DAY(d.Date), 
    DayOfWeekNumber = DATEPART(dw, d.Date), 
    [DayOfWeek] = DATENAME(dw, d.Date), 
    WorkingDay = CAST(CASE DATEPART(dw, d.Date) 
    WHEN 1 THEN 0 -- Sunday 
    WHEN 7 THEN 0 -- Saturday 
    ELSE 1 -- Might lookup for a holidays table here 
    END AS BIT   ) 

FROM DatesRaw d 
ORDER BY d.Date




update D_DATE
set MonthNumber=substring(ID_Date,5,2)

update D_DATE
set DayOfMonth=substring(ID_Date,7,2)

go
Alter table D_DATE
add [Day] varchar(10)
go

update D_DATE
set Day= DayOfMonth + '日'


update [dbo].[D_DATE]
set Month=[MonthNumber]+'月'


 UPDATE [dbo].[D_DATE]
 SET YEAR=substring(ID_DATE,1,4)+'年'

go
Alter table D_DATE
add YearNumber varchar(6)
go


update D_DATE
SET YearNumber=substring(ID_DATE,1,4)

select * from D_date

zyxa9527 iT邦新手 5 級 ‧ 2019-12-27 09:33:37 檢舉

好哦 謝謝~ 我再研究看看

我要發表回答

立即登入回答