FOCUSBI: Real Estate analysis & Snowflake Models

Public number:FOCUSBI
Focus on more business intelligence, data warehousing, database development, crawler knowledge, and Shanghai and Shenzhen stock market data push. Questions or suggestions, please pay attention to the public message to send messages;
If you feel that FOCUSBI is helpful to you, please forward your circle of friends or like [1] at the end of the article.

Business Intelligence Tutorial PDF

Link: https://pan.baidu.com/s/1f9VdZUXztwylkOdFLbcmWw Password: 2r4v

In the rental analysis & Star model, we talked about how to create a star model of the SSAS project scenario, where I do a snowflake model case. The Snowflake model is modified on the basis of the star model.

Snowflake Model

Snowflake model is actually a number of tables to synthesize a dimension, has the function of reducing the data sink surplus, also easy to maintain and attribute extension, here use real estate data example, dimension has room, Wendy, Project, Region, date, in fact Room, Wendy, project, area these data all contain relations, such as the upper level of the room is Wendy, Wendy the upper level is the project, the project the upper level is the area, but the fact data contract, the subscription, the return money is the real estate general three table, is also the Mingyuan ERP core.

Date Dimension

A date dimension is a dimension that is common to any project, as long as the amount involved amounts to a year-quarter-month, year-quarter-month-day, year-week, day, month, day, week, period, and so on to see the data. So how to get a certain day is the year, which quarter, which month, which week, that year of the day, which days, the week? This is done with stored procedures, although SSAS provides smart date dimensions and some changes to the financial dates, but it is after all an American mindset that it is difficult to compound China's national conditions, such as the first day of the week is Sunday, and China is Monday. So how to implement this stored procedure, this stored procedure can be written long and easy to understand if you are familiar with the date processing of SQL, only a few lines can be completed is not easy to read. Here I am using a long SQL statement that is easy to read.

Date Stored Procedure

Setansi_nullson

GO

Setquoted_identifieron

GO

-- =============================================

--AUTHOR:FOCUSBI

--Create date:20180824

--Description: Complete date information table

--=============================================

alterprocedure [dbo]. [Dim_date] @BeginDate nvarchar (8), @EndDate nvarchar (8)

As

BEGIN

DECLARE @DateKey int

DECLARE @dtEndOfDay datetime

Set @dtEndOfDay =convert (Datetime,cast (@BeginDate Asnvarchar), 120)

Set @DateKey =convert (varchar, @dtEndOfDay, 112)

While @dtEndOfDay <convert (Datetime,cast (@EndDate Asnvarchar), 120)

Begin

Select @DateKey as DateKey,

@dtEndOfDayasData,

Year (@dtEndOfDay) Asyear,

Casewhenmonth (@dtEndOfDay) <= 6 Then ' first half '

Else ' second half ' Endas halfayear,

DATEPART (QQ, @dtEndOfDay) as Quarter_num,

Casewhendatepart (QQ, @dtEndOfDay) = 1 Then ' Q1 '

Whendatepart (QQ, @dtEndOfDay) = 2 Then ' Q2 '

Whendatepart (QQ, @dtEndOfDay) = 3 Then ' Q3 '

Else ' Q4 ' Endas quarter_en,

Casewhendatepart (QQ, @dtEndOfDay) = 1 Then ' one quarter '

Whendatepart (QQ, @dtEndOfDay) = 1 Then ' two quarter '

Whendatepart (QQ, @dtEndOfDay) = 1 Then ' three quarter '

Else ' four quarter ' Endas QUARTER_CN,

Month (@dtEndOfDay) as Month_num,

Casewhenmonth (@dtEndOfDay) = 1 Then ' January '

Whenmonth (@dtEndOfDay) = 2 Then ' February '

Whenmonth (@dtEndOfDay) = 3 Then ' march '

Whenmonth (@dtEndOfDay) = 4 Then ' April '

Whenmonth (@dtEndOfDay) = 5 Then ' may '

Whenmonth (@dtEndOfDay) = 6 Then ' June '

Whenmonth (@dtEndOfDay) = 7 Then ' Jul '

Whenmonth (@dtEndOfDay) = 8 Then ' August '

Whenmonth (@dtEndOfDay) = 9 Then ' September '

Whenmonth (@dtEndOfDay) = Ten Then ' October '

Whenmonth (@dtEndOfDay) = one and then ' November '

Else ' December ' End month_en,

Casewhenmonth (@dtEndOfDay) = 1 Then ' Jan '

Whenmonth (@dtEndOfDay) = 2 Then ' Feb '

Whenmonth (@dtEndOfDay) = 3 Then ' Mar '

Whenmonth (@dtEndOfDay) = 4 Then ' APR '

Whenmonth (@dtEndOfDay) = 5 Then ' may '

Whenmonth (@dtEndOfDay) = 6 Then ' June '

Whenmonth (@dtEndOfDay) = 7 Then ' Jul '

Whenmonth (@dtEndOfDay) = 8 Then ' the '

Whenmonth (@dtEndOfDay) = 9 Then ' Sep '

Whenmonth (@dtEndOfDay) = Ten Then ' Oct '

Whenmonth (@dtEndOfDay) = one and then ' Nov '

Else ' Dec ' End MONTH_JX,

Casewhenmonth (@dtEndOfDay) = 1 Then ' January '

Whenmonth (@dtEndOfDay) = 2 Then ' February '

Whenmonth (@dtEndOfDay) = 3 Then ' March '

Whenmonth (@dtEndOfDay) = 4 Then ' April '

Whenmonth (@dtEndOfDay) = 5 Then ' May '

Whenmonth (@dtEndOfDay) = 6 Then ' June '

Whenmonth (@dtEndOfDay) = 7 Then ' July '

Whenmonth (@dtEndOfDay) = 8 Then ' August '

Whenmonth (@dtEndOfDay) = 9 Then ' September '

Whenmonth (@dtEndOfDay) = Ten Then ' October '

Whenmonth (@dtEndOfDay) = one-then ' November '

Else ' December ' End MONTH_CN,

Day (@dtEndOfDay) as [Month_day],

Datename (Dy, @dtEndOfDay) as Year_day,

Datename (wk, @dtEndOfDay) as Year_week,

Casewhenday (@dtEndOfDay) < Ten then ' early '

Whenday (@dtEndOfDay) > Then ' late '

Else ' mid ' end as Tendays,

Datepart (DW, @dtEndOfDay) as Weekday_num,

Datename (DW, @dtEndOfDay) as WEEKDAY_CN,

Casewhendatename (DW, @dtEndOfDay) = ' Monday ' then 1

Whendatename (DW, @dtEndOfDay) = ' Tuesday ' then 2

Whendatename (DW, @dtEndOfDay) = ' Wednesday ' then 3

Whendatename (DW, @dtEndOfDay) = ' Thursday ' then 4

Whendatename (DW, @dtEndOfDay) = ' Friday ' then 5

Whendatename (DW, @dtEndOfDay) = ' Saturday ' then 6

Else 7 Endas WEEKDAY_SHORT_CN,

Casewhendatepart (DW, @dtEndOfDay) = 1 Then ' Monday '

Whendatepart (DW, @dtEndOfDay) = 2 Then ' Tuesday '

Whendatepart (DW, @dtEndOfDay) = 3 Then ' Wednesday '

Whendatepart (DW, @dtEndOfDay) = 4 Then ' Thursday '

Whendatepart (DW, @dtEndOfDay) = 5 Then ' Friday '

Whendatepart (DW, @dtEndOfDay) = 6 Then ' Saturday '

Else ' Sunday ' Endas weekday_en,

Casewhendatepart (DW, @dtEndOfDay) = 1 Then ' Weekend '

Whendatepart (DW, @dtEndOfDay) = 7 Then ' Weekend '

Else ' usual ' endas Weekend

Set @dtEndOfDay = @dtEndOfDay + 1

Set @DateKey =convert (varchar, @dtEndOfDay, 112)

End

END

Implementing a Date Dimension

On the basis of the < star Model _dome > scheme, the name is changed to < star model vs Snowflake model _dome>, and the snowflake model is compared with the star model. Based on the previous steps shown in the Date table Dim_date Import, follow the steps step by step completion of the date dimension, this type must be set to time, if not set to time after the MDX is not bad through the layer structure calculation.

Project Dimension

Import room, Wendy, Project, area four dimension tables establish a good relationship, real estate analysis is a typical snowflake model,

One of the most important steps in creating a project information dimension is that they are hierarchical relationships.

Real Estate multidimensional data sets

FOCUSBI: Real Estate analysis & Snowflake Models