SQL Server Analysis Service
## VS專案與AS物件和結構
|-專案
|-資料源
|-資料源檢視
|-維度(Dimensions)
|-Dimension Structure : Attributes、Hierarchies
|-Attributes Relationships
|-Cube
|-量值群組(measure group)、量值(measure)、維度(Dimensions)
## 建立與開發流程
1.create a new SQL Server Analysis Services project
2.defining one or more data sources
就是一個connection string (連到AdventureWorksDW2019資料庫)，只支援Windows驗證，並且不支援模擬驗證(impersonation)來處理OLAP 物件
3.define a data source view for the project
data source view是一個單一統一的檢視用來資料源所選擇的tables and views的metadata(結構定義/詮釋資料/中介資料/元數據)，將metadata儲存在data source view中，可讓您在開發期間使用metadata，而不需要開啟與任何基礎數據源的連線。
也可將資料表名稱更改為易記名稱
4.define a dimension 維度
Main table 勾選資料表Date，在選取維度屬性 頁面，勾選特定欄位作為維度
將幾個維度的Attribute Type屬性類型從 Regular 改為 Date、Month、Quarter、Year 或 Half Year
5.define an initial SQL Server Analysis Services cube
選擇已經存在的data source view，按下自動建議
(1)measure group table，InternetSales as a measure group table(也就是fact tables)
(2)measure group table - Select Measures page，手動刪除不會使用到的measures欄位
(3)Select Existing Dimensions，確定已選取您稍早建立 Date dimension
(4)Select New Dimensions，選取要建立的新維度。 若要這樣做，請確認已選取 Customer、Geography和 Product 複選框，然後清除 [InternetSales] 複選框。
On the Completing the Wizard page, change the name of the cube to Analysis Services Tutorial. In the Preview pane, you can see the InternetSales measure group and its measures. You can also see the Date, Customer, and Product dimensions.
6.增加維度的屬性
double-click the Customer dimension in the Dimensions node of Solution Explorer
Drag the following columns from the Customer table in the Data Source View pane to the Attributes pane:
BirthDate, MaritalStatus, Gender, EmailAddress...etc
Drag the following columns from the Geography table in the Data Source View pane to the Attributes pane:
City, StateProvinceName ...etc
Double-click the Product dimension in Solution Explorer
Drag the following columns from the Product table in the Data Source View pane to the Attributes pane:
StandardCost, Color, SafetyStockLevel ...etc
7.Review cube and dimension properties in Cube Designer
Intenet Sales量值群組底下的量值可以直接上下拉動，調整順序，順序會影響特定用戶端應用程式如何排序這些量值。
雖然在資料庫層級只建立三個維度 (Date, Customer, Product)，如方案總管所示，SQL Server Analysis Services 教學課程 Cube 中卻有五個 Cube 維度。 Cube 包含的維度比資料庫多，因為 Date 資料庫維度會根據事實數據表中的不同日期相關事實，作為三個不同日期相關 Cube 維度的基礎。 這些日期相關維度也稱為 角色扮演維度。 三個日期相關 Cube 維度可讓用戶依據每個產品銷售相關的三個個別事實來維度 Cube：產品訂單日期 Order Date、訂單履行的到期日 Due Date，以及訂單的出貨日期 Ship Date。 藉由重複使用多個 Cube 維度的單一資料庫維度，SQL Server Analysis Services 可簡化維度管理、使用較少的磁碟空間，並減少整體處理時間。
Dimension Usage tab
可以看到 Internet Sales 量值群組所使用的 Cube 維度。 此外，您也可以定義每個維度與其使用中每個量值群組之間的關聯性類型。
Browser tab
Cube 無法瀏覽，因為它尚未部署到 SQL Server Analysis Services 的實例。
8.deploy the Analysis Services project
In Solution Explorer, right-click the Analysis Services Tutorial project, and then click Deploy
(2)browse Cube
10.修改量值(measures)、屬性(Attributes)和階層(Hierarchies)來改善 Cube 的實用性和友好性
藉由新增階層，以支援各種層級的導覽和匯總、將格式套用至特定量值，以及定義計算和關聯性
量值(measures)
Cube Structure tab of Cube Designer for the SQL Server Analysis Services Tutorial cube
expand the Internet Sales measure group in the Measures pane, right-click Order Quantity, and then click Properties.
In the Properties window, click the FormatString list, and then type #,#
多選同時修改屬性 select multiple measures at the same time
Select the following measures. You can select multiple measures by clicking each while holding down the CTRL key:
Unit Price、Extended Amount、Discount Amount ... 等
In the Properties window, in the FormatString list, select Currency
單選
1.select the measure Unit Price Discount Pct, and then select Percent in the FormatString list.
2.Change the Name property for
Unit Price Discount Pct measure to Unit Price Discount Percentage.
Tax Amt measure to Tax Amount
Dimension
Customer Dimension
In the Attributes pane,
right-click English Country Region Name, and then click Rename to Country-Region
English Education attribute - change to Education
English Occupation attribute - change to Occupation
State Province Name attribute - change to State-Province
create a hierarchy
Drag the Country-Region attribute from the Attributes pane into the Hierarchies pane.
Drag the State-Province attribute from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Country-Region level.
...
In the Hierarchies pane of the Dimension Structure tab, right-click the title bar of the Hierarchy hierarchy, select Rename, and then type Customer Geography.
Data source views
add a named calculation
In the Create Named Calculation dialog box, type FullName in the Column name box, and then type or copy and paste the following CASE statement in the Expression box:
CASE
WHEN MiddleName IS NULL THEN
FirstName + ' ' + LastName
ELSE
FirstName + ' ' + MiddleName + ' ' + LastName
END
Dimension
Customer Dimension
In the Attributes pane of the Dimension Structure tab, click the Customer Key attribute.
Open the Properties window.
In the Name property field, type Full Name.
Click in the NameColumn property field at the bottom, and then click the browse (...) button to open the Name Column dialog box.
Select FullName at the bottom of the Source column list, and then click OK.
Dimensions > Hierarchies
In the Dimensions Structure tab, drag the Full Name attribute from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the City level.
Define display folders
Dimension
Customer Dimension
In the Attributes pane, select the following attributes by holding down the CTRL key while clicking each of them: City、Country-Region、Postal Code、State-Province
In the Properties window, click the AttributeHierarchyDisplayFolder property field at the top (you might need to point to it to see the full name), and then type Location.
In the Hierarchies pane, click Customer Geography, and then in the Properties window on the right, select Location as the value of the DisplayFolder property.
In the Attributes pane, select the following attributes by holding down the CTRL key while clicking each of them: Commute Distance、Education、Gender、House Owner Flag、Marital Status、Number Cars Owned、Number Children At Home、Occupation、Total Children、Yearly Income
In the Properties window, click the AttributeHierarchyDisplayFolder property field at the top, and then type Demographic.
In the Attributes pane, select the following attributes by holding down the CTRL key while clicking each of them: Email Address、Phone
In the Properties window, click the AttributeHierarchyDisplayFolder property field and type Contacts.
Composite key
create a composite key for the City and State-Province attributes.
例如，當您在本教學課程稍後定義屬性關聯性時，City 屬性必須唯一識別 省 屬性。 不過，在不同的州，可能有數個具有相同名稱的城市。 因此，您將建立複合索引鍵，該索引鍵是由 StateProvinceName 和 City 數據行所組成的 City 屬性。
Customer dimension.
1.
In the Attributes pane, click the City attribute.
In the Properties window, click in the KeyColumns field near the bottom, and then click the browse (...) button.
In the Key Columns dialog box, in the Available Columns list, select the column StateProvinceName, and then click the > button.
The City and StateProvinceName columns are now displayed in the Key Columns list. Click OK.
To set the NameColumn property of the City attribute, click the NameColumn field in the Properties window, and then click the browse (...) button.
In the Name Column dialog box, in the Source column list, select City, and then click OK.
2.
In the Attributes pane, click the State-Province attribute.
In the Properties window, click in the KeyColumns field, and then click the browse (...) button.
In the Key Columns dialog box, in the Available Columns list, select the column EnglishCountryRegionName, and then click the > button.
The EnglishCountryRegionName and StateProvinceName columns are now displayed in the Key Columns list. Click OK.
To set the NameColumn property of the State-Province attribute, click the NameColumn field in the Properties window, and then click the browse (...) button.
In the Name Column dialog box, in the Source column list, select StateProvinceName, and then click OK.
3.Customer relationships
define attribute relationships
Attribute Relationships tab
In the diagram, right-click the City attribute, and then click New Attribute Relationship.
In the Create Attribute Relationship dialog box, the Source Attribute is City. Set the Related Attribute to State-Province.
In the Relationship type list, set the relationship type to Rigid.
The relationship type is Rigid because relationships between the members will not change over time. For example, it would be unusual for a city to become part of a different state or province. Click OK.
In the diagram, right-click the State-Province attribute and then select New Attribute Relationship.
In the Create Attribute Relationship dialog box, the Source Attribute is State-Province. Set the Related Attribute to Country-Region.
In the Relationship type list, set the relationship type to Rigid. Click OK.
Product dimension.
使用具名計算(Named Calculation)來為產品線提供更描述性的名稱、在 Product 維度中定義階層(hierarchy)，以及指定階層的 [全部] 成員名稱。 您也會將屬性分組到顯示資料夾
1.add a named calculation
Data source views
add a named calculation
CASE ProductLineWHEN 'M' THEN 'Mountain'WHEN 'R' THEN 'Road'WHEN 'S' THEN 'Accessory'WHEN 'T' THEN 'Touring'ELSE 'Components'END
create a hierarchy
Drag Product Line from the Attributes pane into the Hierarchies pane
Model Name into the <new level> cell in the Hierarchies pane, underneath the Product Line level.
Product Name into the <new level> cell in the Hierarchies pane, underneath the Model Name level.
right-click the title bar of the Hierarchy hierarchy, click Rename, and then type Product Model Lines.
The name of the hierarchy is now Product Model Lines.
In the Attributes pane, select the following attributes by holding down the CTRL key while clicking each of them: Class, Color, Days To Manufacture, Reorder Point ...等
In the AttributeHierarchyDisplayFolder property field in the Properties window, type Stocking.
In the Attributes pane, select the following attributes: Dealer Price, List Price, Standard Cost
In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Financial
In the Attributes pane, select the following attributes: End Date, Start Date, Status
In the AttributeHierarchyDisplayFolder property cell in the Properties window, type History.
Select the Product Model Lines hierarchy in the Hierarchies pane, and then change the AllMemberName property in the Properties window to All Products.
Click an open area of the Hierarchies pane, and then change the AttributeAllMemberName property at the top of the Properties window to All Products.
define attribute relationships
click the Attribute Relationships tab.
In the diagram, right-click the Model Name attribute, and then click New Attribute Relationship.
In the Create Attribute Relationship dialog box, the Source Attribute is Model Name. Set the Related Attribute to Product Line.
In the Relationship type list, leave the relationship type set to Flexible because relationships between the members might change over time.
Multidimensional Modeling (Adventure Works Tutorial)
https://learn.microsoft.com/en-us/analysis-services/multidimensional-tutorial/multidimensional-modeling-adventure-works-tutorial?view=asallproducts-allversions
沒有留言:
張貼留言