《Microsoft SQL Server 2008 Analysis Services Step by Step》学习笔记七：高级维度设计

　　SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
　　导读:本文介绍高级维度设计的基本技巧：创建三种非常规的维度关系：引用维度、事实维度、多对多维度。
　　本文末尾提供两个项目源码：AdventureWorks_BI_Begin4和AdventureWorks_BI_End4,顾名思义，开始和完成。另外，包括数据库文件SSAS2008SBS_Data，请读者自行修改数据源ds的连接串。
　　本文将包括以下内容：
　　■1、创建引用维度（referenced dimension）
　　■2、创建退化维度（degenerate dimension）
　　■3、添加一个事实维度到一个Cube
　　■4、创建多对多维度（many-to-many dimension）
　　
　　通过前面的创建维度学习，我们已经能够处理没有关联关系的维度或与一个度量组有常规关联的维度。当每一个事实表记录与一个维度表记录相关时，该维度与一个度量组有常规关联。
　　Analysis Services也提供灵活的维度关系类型以兼容维度与度量组的其他关联方式。三种特殊的维度关系分别为：
　　（1）引用维度关系。有时候，多个维度可能共享一套通用的属性（被包含在一个雪花型维度的表中）。当事实表不直接与雪花型维度表相关时，雪花型维度与一个度量组有一个引用关系。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pMlYiqpUNxX2Yz8WiHreybt1K_Z5EwHuElG8P5PpKLF0GKJEbtGN_EoxifNWBloHU8Y2N0HD0ozDaSJN5dcqN-NZtpvkrHqju/2011-5-20%2010-00-51.png?psid=1
　　（2）事实维度关系。对任意一个事实记录都是惟一的属性可以被存储在事实表中。当维度被从存储在度量组的事实表中的属性创建时，该维度与度量组有一个事实关联。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pL-EVbZ9zjb-LJon54elImesFLVbRMV58urN_YhWP_-x67Njj_SrkkUJYMO9biTVaKXFCvx9Y7yZOhO4HdjvrYZPwLrOzBuk4/2011-5-20%2010-02-00.png?psid=1
　　（3）多对多维度关系。单个事实记录可能与多个维度记录相关。如下面例子中，维度与度量组在一个多对多的关系。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p1CdF44mOWX8zAxt6v_yPE15oQ9gl1QIa8pX6noHlj-yWO14EsgbOUjJb6ZU82od7nYaBMc8em3OYrumSUz9h4Dm_ytGvW-TV/2011-5-20%209-59-42.png?psid=1
　　更多细节，请参考MSDN：维度关系（http://msdn.microsoft.com/en-us/library/ms175669.aspx）
　　1：创建引用维度（referenced dimension）
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1prb2XpVIhMMWUXZUHGhCHRLzOSQNo_CX5YeqgZFwTg0RhYN2mHUtuMeSq7f-yKX2769jUrdOEwJfWBHcEhNpLqkoK0KwmuU2d/2011-5-19%2016-08-38.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pJnvtmaDzdBQAuVgUKXfL9CgW1-D4NHPn_VxmmWV--rKmEAuHAeKP2vgDCa7A_RQGqt0Nwm_VJyWbrkYepcIumTu52yV1wZ-o/2011-5-19%2016-01-48.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pxVepuj7EBn4U6QJNbY-_puCwN-8lx_kPx0Jv7VUq7dODlzFCJ1P7OEoKGbAacVjF3kyUEFVu9zXNlAmGi9PgpG82SqFr2pAa/2011-5-19%2015-28-51.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pHCdzjxPMyIYr9wAu1QG5zbwSMGfxUOTTVxVhtNcUzbzzxnaDnA7HS7rB0Grmi_R2CQAHLKM5xfYvix-2LWhNrKGF_K6-LOnj/2011-5-19%2016-28-11.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pJ4xYgZQYLZYyoow2hl4CWg4vmrCalIBZ5rc6ATaU5XeSWjabmcvFjVzJhqWbAXvfbX2HKx5cj6-pq24X_1tvY1wFt6GeGoJ8/2011-5-19%2016-18-43.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pzAbNN8sEnBSC-TbU_JtKdMj9r1_EamKKytiltO04Ad_fSWBNSUNs4SFyUU2SrlRJeP-wHyd-e6-FSViOn7HjVE9eUmzZgPVR/2011-5-19%2016-50-04.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pNsq0KYG-33kgbZrksJ_KuSI_fEVjgs7JHR4bDeZ_N7iFtdyfj-2WFX48B2npZuB4s2V_gZI-TzEf4Pe-tWfmOhT-AqDqsb0k/2011-5-19%2017-00-16.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pNsq0KYG-33l2JpW2ai3Ae6cvvhkLWEt3NuyLpVVOTN6IpUSzMjBpZKA0oKmvOFkaamWrbNxKwUfg-1rv7IFZfSO2vGdi8Ku_/2011-5-19%2017-05-21.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pqJSKuJbuBIEWKnzhRNIsnFKCdX8BEj7taWecr3rqPp4hS-0DBVfM_eTorV6tmj3c69J_696za2tx9o0HIfLgkQPnKwfFFM7w/2011-5-19%2017-11-35.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pRFVMJ4f8atGiEVr1r2YNr8IYLsQ1Cc0qcKF0KdmIpy18Gf1iE9bGLI6eD6P_rGAjUn2t_rOkF9zem6Qu5PE4AsGBDci566iA/2011-5-20%206-08-38.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p9XR6wVov_NcXLjOWBOc6iJj18_A-kB6S6_-LjFPomUuyeffH6gIYvMQdKK6jq_rc_eZgknCSNOlB32aef4i_EmocpqJifmgC/2011-5-20%206-12-00.png?psid=1
　　OK!部署项目。注意在Cube Designer界面，点击"Browser”,并"Reconnection”。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p9XR6wVov_NemMDUWirNz0d1G6ok2uR8wU9c57Ks6VPa_me2qwDTffDwHmgboownpJ85-wtPbi9FEyJBll1tCt0oTsf_ZGehV/2011-5-20%206-41-46.png?psid=1
　　注意：Geography维度通过Customer中间维度与Internet Sales度量组相关，在Internet Sales度量组的上下文中，它代表customer geography。对于Reseller Sales度量组，Reseller是中间维度，因此，在它的上下文中，Geography维度代表reseller geography。
　　2-3：创建事实维度
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pfIbVSE0TzZBv7AlN68_cPrkmFSZULd2zbwmEdm_mZKIybnYWtIvji5jxMXoKNfGU1ovko_u6ddGS2SVMOUUTWMibvZSno8ez/2011-5-20%206-49-15.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1peFj7c8xxV51bplpJzbPZbMFmIuq1ca1a7falBtCW_OdNr17r1iZZg8uxBwHbM5CH9Pouh2vm68V9cYWx2tfR54Mmw6HOpnoJ/2011-5-20%206-50-32.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1peFj7c8xxV52K2OkSbleC6lvw0Mt3AdNn6Ge0V3LGhClDr66oW693Mzzu1NrJxSc490ZLGQZaPYEEPY88PxvLZTSSMX4G-Fvw/2011-5-20%206-53-18.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pIhNo41R1uIu8Ck3RQJEDfADhXr5iA-sxHMylUd7Kx4DGIcrqLpXeReors0GKOAZ3ySPTuEmNt-MKSwmVgKZaNmFIgRE7RCWL/2011-5-20%206-59-52.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pC7eMUScy_2IpVxoDweu1ainZw1TYQqEFvKFaD5FotzbfBIB1C73nEqFOxCTo2Lsoe2rn8oQEnKzZ9h49los94rqIIdL4Zdve/2011-5-20%207-06-20.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pC7eMUScy_2LXAcria3NPZNhTtj1aloW6CBiED90FVkK650ieCXHt3zREOBE4aFLtGMtBlDB82ZiPw9rEh9Jdhc-NTGLWeavU/2011-5-20%207-09-06.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pajWpp-07eB1dmCH6MVmOFpI95zCF1RMqABylz-q340cDCwRjWi10mnW-MrKG1WgmlrWTfjR1Xg4JCmTKh-Qh_LksPeF9ylrx/2011-5-20%207-13-19.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p7HN5K4wVXnoxi3aCbZz4TKYD-iYo4menothilsc_WvwTtXnMjEGU8cMO4D_uvmfhByzYhrtsPzM7i2ga1ep_1k8ZixLqqVv7/2011-5-20%207-18-49.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p7HN5K4wVXnrxWBqdh0tS_bYaDNCmvn6y2cMvTBLlSmLiB3z_XzhYYfSsZGOIapTSgT-txQW9RNPQUul4tRTK8pogYiPtsTpm/2011-5-20%207-17-45.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pWat1fzELSLDNxrDgnNTDuiQqpPQAhP8fZlDQ4KjDFm9KMPdbl5GDbt57JsnEp0HJbLKQ9dgtrRjqJ6ryT5-mw4ZX5msNaace/2011-5-20%207-19-40.png?psid=1
　　
　　OK!部署项目。注意在Cube Designer界面，点击"Browser”,并"Reconnection”，在Report面板区右键“Clear results”。
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pQOJKUwfYtUQlfDOdHsYHnD2TtHoTWLZCVZGyy5y7H5nk8sNikVvG9Hhr1pfjvA23wnrkQ3h5-GOxbfw9J3N-ZrYvS-mfD8qb/2011-5-20%207-22-08.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pXSz8P1b_OyHD6E4ELijzwa6yvUVG_CT8pzmZnxrhHzx7o2BcaTmi0AgOxi0Rd6XnwsIuA7gMydEC97iYfTPPWb6b-BVYkZC8/2011-5-20%207-29-38.png?psid=1
　　
　　4：创建多对多（Many-to-Many）维度
　　在Analysis Services中创建一个多对多（Many-to-Many）关系需要两个维度和两个度量组。
　　下面我们举例创建一个Account Manger维度与Reseller Sales度量组的多对多关系。
　　Account Manager度量组和Reseller Orders维度作为中间（intermediate）度量组和中间（intermediate）维度。
　　Reseller Sales度量组和Reseller Orders维度已经存在。因此，我们需要创建Account Manager度量组和Account Manager维度。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pW_I8QD7clHVv4myGJp6uP_okmHDUKg56sgb6lhF5T9ZeoK4iHZmHZb0mSw1qrY_atIp209QaojI7FNyCCZawkkcHAMovSYZh/2011-5-20%207-47-25.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pTRHidf2gQitLfqgGq-FOT23m9E01wwXR75iltnkb4IOD4milbJ66j3xoXR9kAcS_mJ3fPPQ-3XXfCEm7CvL7FABmGLUs884i/2011-5-20%207-42-33.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p2fvCuNhBMxN4HHJqCnv1m7nKlbKaApBa44xQ1JkyiwfV80XhHJ3fM4rW-byAiHFiobcGjlVd3lwM4LkuRh4ooOWEYKuLLpCe/2011-5-20%207-49-16.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p2fvCuNhBMxM4gwqiDhBS9dfsL6yXr4p2-62tjOMfLNeUxwJqNG6yYng5kPV051Qj_NCxFRvURvr2uv19K_EYleWC9LWpHM-a/2011-5-20%207-51-22.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pdjyPO_y7DBtbDb7nd1H6ufJJChiL3_wI-7Cmza42b360Na8a5L6yMBEqzKXWDacAhXjPZ2x0CQV22XK2zP-OJnVwcl1fyp9a/2011-5-20%207-52-22.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p5slFigq4fbcAVQcjl3VmIho7TBDvbLCwLxJToBFk8lJEbgIqatu8Hib7yR8UvSgNTZCYs8OVubzf7boPAx-61ZakXG4WalUA/2011-5-20%207-54-32.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pLBRqwtscM6vDO4Egsk_LdxpG8ArK4re7g1AIF9lAxYV2lRqUeqCZgu8LiVBO3U74HHtNG_8IdboSbSrhXc2jg72ppv2hptcd/2011-5-20%209-22-15.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1plLRFlN_NF_Tzdrs7MQ1XI8DCNCEo4BUBXbs6x6mtdKsXP3XEHF9VDAbqLai0WoOqHEt6tcUlj6BiIIDjoKvCMZC74Bt5uw7X/2011-5-20%209-25-32.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1plLRFlN_NF_R8NivTwAdV4lBPR1NhX1rNq8PORSzazB7EIXNClQhitReiDOotTFP85t3H54pu_7q2soQgS1UrMpTP7fsvl4XF/2011-5-20%209-24-37.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1ph8eWAwy9Puj-0Yy8fjjmKf10zRfWoiYVy6ZieSMLdRxt1TXJX5GGdGTXVW4eUnvjFPwSq4LQRI1nS3bIgFOLOgacHppqOG5P/2011-5-20%209-27-14.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1ph8eWAwy9Puh49-qRRuOth4IigwO07Fj3sbBNMCU0G9v_Mh6yDeWQSYMGfok5n4L0VWDfO4owDy5TTFkrvvarJ2Rf_Fb5KP_A/2011-5-20%209-28-06.png?psid=1
　　OK!部署项目。注意在Cube Designer界面，点击"Browser”,并"Reconnection”
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1pxnjjkYTErAZLqzri7wbw4mAKk6t1cu8HaVRojHJedg9D9g3BTY9qQYhEi49X3536CWKxTuUBMGTWOxuLBE1fQ9uCw78bTitu/2011-5-20%209-32-49.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p85vqwg6bQ9VHXsnRnPvQHplYJaGF_kIAAW8ObtIoLWz81JVD93wn9ROGgWxf5j8Wt4gjsjkVH_BoF2k433n4QoVGfM3tpQ6i/2011-5-20%209-39-41.png?psid=1
　　http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://trccdq.bay.livefilestore.com/y1p85vqwg6bQ9X9hQO2EWaMwpfAv8xokHHLE0H8qxkCkIpC2U9bO66m6G2ab5ZMaB9e_0FQY2Erv6LyR6yXUurUqmpv2t3IhfoI/2011-5-20%209-44-32.png?psid=1

　　
　　源码下载：
　　begin4
　　end4
　　数据库下载
　　参考资源：
　　1、SQL Server Analysis Services 官方教程
　　（http://technet.microsoft.com/zh-cn/library/ms170208.aspx）
　　2、Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques
　　（http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3494E712-C90B-4A4E-AD45-01009C15C665&displaylang=en）