Pro SQL Server 2008 Analysis Services- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:31

0
47
lượt xem
7
download

Pro SQL Server 2008 Analysis Services- P10

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'pro sql server 2008 analysis services- p10', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Pro SQL Server 2008 Analysis Services- P10

  1. APPENDIX A Setting Up Adventure Works Through most of this book, I work with a demonstration data set called AdventureWorks. It’s a multidimensional data warehouse and Analysis Services project based on a fictional bicycle company, which has both retail and Internet sales. I really enjoy working with this data set. It’s true that it’s a bicycle store, and as I’ve often commented, “just about wholly unrelated to anything anyone does.” However, although it may not be related to the business of various SSAS users, it is something that just about everyone can understand. If you’ve downloaded sample databases from Microsoft before, you’ve gotten them from Microsoft.com. A few years ago, Microsoft moved all the samples to www.codeplex.com, their open source community, so they would be covered by the open source licensing there. The sample databases are located at the following site: www.codeplex.com/MSFTDBProdSamples Click the Downloads link in the top-right corner. Then download SQL2008.AdventureWorks_All_Databases—either x64 or x86, depending on the architecture you’re running. (There’s a .zip file that has the same contents.) Then run the MSI on your SQL Server (Figure A-1). Note CodePlex uses a Silverlight applet for the download prompt. So if you try to download from a locked-down server, the link won’t do anything. To enable it, add *.codeplex.com to your trusted sites. Alternatively, download the file from a desktop PC and copy it to the server. 431
  2. APPENDIX A SETTING UP ADVENTURE WORKS Figure A-1. Installing the samples The wizard prompts you for the location to install the files (Figure A-2). The default installation location is C:\Program Files\Microsoft SQL Server\100\. The Program Menu Shortcuts option will add shortcuts to the Start menu. The Create AdventureWorks DBs option will generate the databases in the server you select in the next step. Note You must have Full Text Search and FileStream installed on the server to create the databases, or the wizard will error out. 432
  3. APPENDIX A SETTING UP ADVENTURE WORKS Figure A-2. Selecting options and the location to install the sample files The final step is to select the SQL Server instance to install the databases into (Figure A-3). Figure A-3. Selecting the database instance 433
  4. APPENDIX A SETTING UP ADVENTURE WORKS If you don’t use the wizard, you can always install the databases yourself with the script files installed. To do this, open the Tools\Samples\AdventureWorks 2008 Data Warehouse folder from where you installed it. (If you installed the shortcuts with the MSI, there will be a shortcut to the folder in the Start Menu.) Note If you use the wizard to install the databases, do not run the scripts afterward. The first thing the script will do is drop the existing database, and if you haven’t set up the script properly, it will error out immediately afterward. It’s a very confusing state to be left in, because it will look like the script created all the databases but one, when in fact the databases were already created and the script dropped that one. Open the instawdwdb.sql file. If you have the SQL client tools installed, double-clicking the file should open SQL Server Management Studio. If you don’t have the SQL client tools installed, you can copy the scripts to a PC where the client tools are available, or you can use the SQLCMD command-line parameter to run the scripts. If you installed the files into the default location, you should be able to run this query file, and it will create the database and populate it. After the database is created, verify that it’s in place on the server. To build the cubes necessary for reviewing some of the examples in the book, you’ll need to open the OLAP projects and build the cubes (these were not automatically created by the wizard). Open Tools\Samples\AdventureWorks Analysis Services Project. You’ll see two folders, enterprise and standard. Choose the folder that matches the edition of SQL Server Analysis Services you have installed. In that folder, you’ll find an Adventure Works.sln file; double-click that to open the solution in BIDS. After you have the solution open in BIDS, you’ll need to make two changes. First, in the Solution Explorer, double-click the AdventureWorks data source, and enter the server name for where you installed the AdventureWorks DW database. Next, right-click on the solution (the topmost Adventure Works in bold), and select Properties. Click Deployment in the list on the left. Find the Server entry under Target and change that to the server name you want to deploy the cubes to when you process them. Click OK. Deploy the project, process the cubes (see Chapter 8), and you’re all set! 434
  5. APPENDIX B Data-Mining Resources Books Data Mining with SQL Server 2005, by ZhaoHui Tang and Jamie MacLennan (Wiley, 2005) Data Warehousing, Data Mining, & OLAP, by Alex Berson and Larry Dubov (McGraw-Hill, 2007) Delivering Business Intelligence with Microsoft SQL Server 2005, by Brian Larson (McGraw-Hill, 2006) Foundations of SQL Server 2005 Business Intelligence, by Lynn Langit (Apress, 2007) Web Sites SQLServerDataMining.com: www.sqlserverdatamining.com Microsoft SQL Server 2008 Data Mining page: www.microsoft.com/sqlserver/2008/en/us/data-mining.aspx Microsoft SQL Server forums: http://social.msdn.microsoft.com/Forums/en-US/sqldatamining/threads SQL Server Books Online: http://msdn.microsoft.com/en-us/library/ms130214.aspx About.com: “Data Mining: An Introduction”: http://databases.about.com/od/datamining/a/datamining.htm Analysis Services Resource Hub: www.ssas-info.com SQL Server TechCenter: http://technet.microsoft.com/en-us/sqlserver/default.aspx Sample Databases for Microsoft SQL Server 2008: Samples Refresh 4: http://msftdbprodsamples.codeplex.com/releases/view/37109 435
  6. Index administration A PowerShell, 92–95 SQL Server Management Studio, 86–92 Access property, data sources, 350 AdventureWorks cube Accessory Buyers marketing campaign, 277 BIDS, 60 adding Naïve Bayes mining model to, 305 calculated measures, 189 creating Accessory Buyers mining structure, creating, 190–193 304–305 dimension usage table, 181 creating data source view, 281 looking at, 125 creating views in AdventureWorks, 278– MDX and SQL, 219 281 partition folders, 57–58 Data Mining Extensions, 303–310 processing, 333–335 Data Mining Model Designer, 289–303 processing Analysis Services objects from predicting accessory buyers, 308–310 BIDS, 211, 212 preparing data warehouse, 278–281 using Excel writeback, 315 processing, DMX, 305–306 AdventureWorks project viewing mining model, 306–308 Accessory Buyers campaign, 277 AccessoryCampaign DSV Account dimension, 138, 139 creating, 281 creating Accessory Buyers views in, 278–281 creating data-mining model, 283 creating cubes, 173–178 account attributes, assigning, 254 creating Date dimension, 161 Account dimension, 137, 138 creating dimension, 127–134 AdventureWorks project, 138, 139 sample databases, 431 browsing, 139 setting up, 431–434 unary operators, 256 specifying dimension storage modes, 142– account intelligence, 254–255 148 account business intelligence, 139 AdventureWorks schema, 120 choosing Standard/Enterprise SSAS, 45 AdventureWorks solution unary operators, 255 creating data-mining model, 282 Action Designer, 268, 269 DSV from, 9, 10 action type, 269 Affected Objects section, Change Settings dialog, actions, 34–35, 251, 265–270 336 drill-through actions, 265, 266 aggregate functions, MDX, 241–245 executing as part of process, SSIS, 345– Avg function, 241–242 348 TopCount function, 242–244 general actions, 268, 269 Aggregation Design Wizard invoking via Excel, 266, 267 creating aggregations, 361 reporting, 267, 268 Review Aggregation Usage screen, 362 Active Directory security, 348 Set Aggregation Options screen, 363, 364 Add Business Intelligence Wizard Specify Object Counts screen, 362, 363 Dimension menu, 139 Aggregation Designer, 71, 72, 361 additive measure, aggregations, 32 437
  7. INDEX aggregation, OLAP cubes, 89–90 setting Unary Operator on parent attribute, AMO design warnings, 135–136 139 automating cube processing, 339 types of, 31–32 BIDS, 60 AggregationPrefix property, measure groups, 185 processing, 333–335 aggregations data source views, 88–89 additive measure, 32 data sources, 87–88 Analysis Services, 359–367 connections from OLAP to Excel, 375, 389 attributes, 226 data-mining structures, 90 CALCULATE statement, 186 DBA tasks, 333–348 combining numerical data, 183 design, 352–359 creating, 360–365 dimensions, 90, 353 cubes, 27 example showing power of, 188 LazyAggregations setting, 140 executing actions as part of process, SSIS, measure groups, 185 345–348 measures, 183 facts, 183 nonadditive measures, 32, 183 hardware requirements, 41–42 performance management, SSAS, 359–367 importing database into new project, 86 preparing for use, 365–367 interacting with SSAS, 75 processing Analysis Services objects, 204 key performance indicator, 11 semiadditive measures, 32, 183 logical architecture, 49–51 algorithms, data mining see data mining algorithms managing, 87–90 All member, 221 named calculations, 110 AllowedBrowsingFolders property, 56 partitions, 359 AMO (Analysis Management Objects), 340–343 performance management, 352–371 PowerShell with SSAS, 94 Performance Monitor, 337–339 processing Analysis Services objects from, PerformancePoint Server, 423 215 PowerShell, 343 AMO Automation deployment method, 198 pre-calculation caching, 359 AMO design warnings, 72, 135–136 preparing aggregations for use, 365 Dismiss Warning dialog, 135 processing architecture, 336–337 mismatching types, 138 processing objects, 204, 205 viewing status of warnings in database, 136 from AMO, 215 Warnings tab, 136 from BIDS, 208–214 ampersand, MDX, 222 from SSMS, 214 Analysis Management Objects see AMO from XMLA, 215 Analysis Services processing options, 335–336 see also SSAS Profiler trace, 337 actions, 251 relating measures and dimensions, 178, 179– aggregations, 359–367 181 Analysis Management Objects, 340–343 roles, 90 attribute relationship design, 353 scalability, 367–369 authentication, 348–349 security, 348–352 authorization, 348, 349–352 selecting connection to, 347 autoexists feature, 351 setting Unary Operator on parent attribute, building attribute relationship in dimension, 139 151 SQL Server 2005, 8 CALCULATE statement, 186 SQL Server Analysis Services (SSAS), 8 combining numerical data, 183 SQL Server Profiler, 337 connecting to database, 85 SQL Server Reporting Services, 401 creating project, 82–84 synchronizing SSAS databases, 202–204 438
  8. INDEX task configuration, 346 AttributeHierarchyDisplayFolder property, 156 unified dimensional model, 46–49 AttributeHierarchyEnabled property, 156 user-friendly interface for, 373 AttributeHierarchyVisible property, 156 using DMX development environment, 303 attributes using Excel writeback, 319 aggregating, 226 VertiPaq mode, 311, 320 aggregation design, 362 Windows Integrated Authentication, 348 attribute relationships, 150–156 Analysis Services data table creating dimension with, 152–156 building Excel report, 122 creating from data source view, 149 Analysis Services databases creating tool tip with dimension attribute, 21 deploying projects, 195 design, SSAS, 353–355 Analysis Services objects dimension types, 138 processing with XMLA queries, 340 dimensions, 21 scheduling processing of, 343–345 dimensions, SSAS, 148–156 SQL Server Agent, 344–345 editing full collection of properties for, 81 Analysis Services Processing Task MDX notation, 222 scheduling OLAP maintenance, 215, mismatching types, 138 216 OrderBy property, 156 Analysis Services Project OrderByAttribute property, 156 creating data sources, 102 parent-child attribute in dimension, 157 Analysis Services Server properties, 156 connecting SSMS to, 366 Select Dimension Attributes page, 129, 130 Analyze tab setting, 129 pivot charts, Excel 2007, 388 showing all attribute values for dimension, Ancestor function, MDX, 239 150 ancestors and descendants, 240 attribute-store processing job, 336 APL, history of OLAP, 7 authentication architecture Analysis Services, 348–349 BI architecture, 47 credentials, 101 processing, Analysis Services, 336–337 user authenticating against Web application, architecture, SSAS, 46–59 349 logical architecture, 49–51 Windows Integrated Authentication, 348 physical architecture, 52–54 authorization storage, 54–59 Analysis Services, 348, 349–352 AS keyword, MDX queries, 237 double-hop problem, 348 asdatabase file, SSAS, 202 roles, 349–352 Assembly objects autoexists feature, Analysis Services, 351 SSAS logical architecture, 51 Average function, tuples, 228 assets Avg function, MDX, 241–242 account intelligence, 254, 255 axes, MDX queries, 233 Associated Measure Group, Cube Designer, 191 Axis Properties calculated measures, 261 configuring axis in SSRS 2008, 412 association predictions algorithm, 277 Attribute Designer, BIDS, 354 attribute ordering, BI, 257 B attribute relationship design, Analysis Services, 353 Backup deployment method, 198 attribute relationship designer, Dimension Editor, balance, account intelligence, 255 70, 71 balanced hierarchy, 25 attribute relationships, dimensions, 151, 155 BI (business intelligence), 251–258 snowflake schema, 150, 151 account intelligence, 254–255 star schema, 150, 151 439
  9. INDEX Add Business Intelligence Wizard, 139 BIDS 2005 attribute ordering, 257 Dimension Wizard, 69 Business Intelligence Wizard, 251, 252 storage of measures and measure groups, 71 currency conversion, 257–258 BIDS 2008 custom member formulas, 256 Aggregation Designer, 71, 72 dimension intelligence, 255 AMO design warnings, 72, 135–136 staging database solution, 48 as stand-alone install, 76 time intelligence, 252–254 cube specific tabs, 77 traditional BI architecture, 47 Dimension Wizard, 70 unary operators, 255–256 panes, 76, 77 BIDS (Business Intelligence Development Studio), solution tabs, 77 75–86 Usage-Based Optimization Wizard, 72 AdventureWorks cube, 60 BIDS browser, 12 Aggregation Designer in, 361 viewing measures, 192 Attribute Designer, 354 Bill of Materials dimension, 137 background, 8 Binding type browser, 12, 192 using partitions, 357 creating Analysis Services Project, 82–84 BIOS data, PowerShell getting, 93 creating cubes, 170–178 block computing, optimization creating data sources, 102 SQL Server 2008, 68 creating dimension, 127 bottom-up design, data warehouses, 37 creating DSV, 107–109 Browser tab, dimension designer, 131, 132, 134 cube browser, 13 specifying dimension storage modes, 142, cube design wizard, 63 147 cube structures in SSAS, 59 browsers data sources in Solution Explorer in, 98 BIDS browser, 12, 192 database solutions, 82–86 dimension browser, 139 deploying projects to development server Build tab properties from, 195 project properties dialog, 197 dimension design, 69 business intelligence see BI KPI designer in, 12 Business Intelligence Development Studio see BIDS measure groups, 182, 184 Business Intelligence Wizard, 251, 252 measures, 182 ByAttribute/ByTable methods Measures pane, 182 processing dimensions, 353 methods for creating time tables, 160 New Project Wizard, 8 opening SSAS database, 84–85 C as part of project, 85–86 caching panes, 76–79 pre-calculation, Analysis Services, 359 Partitions tab, 356, 357 ProactiveCaching property, dimensions, 141 Perspective Designer, 271 CALCULATE statement, 186 processing Analysis Services objects from, processing Analysis Services objects, 207, 208 208–214 calculated dimension members Properties pane, 80–82 calculated measures, 185 setting up Adventure Works, 434 calculated measures, 33–34, 185–193, 251 Solution Explorer, 79–80 calculated dimension members, 185 SSIS designer in, 346 Calculations tab, Cube Designer, 259–262 tablix, 404 creating, 190–193 user-friendly interfaces for OLAP solutions, query-scoped calculated measure, 236 373–429 session-scoped calculated measure, 236 Visual Studio, 75–76 440
  10. INDEX calculated members, creating, 186 configsettings file, SSAS, 202 Calculation Tools section, Cube Designer, 187, 262 configuration files for SSAS solution, 201 calculations Configuration Manager button, Properties dialog, designing calculations in cubes, 186 196 named calculations, DSV, 110–112 configuration properties time calculations, 252 Specify Configuration Properties screen, 200, Calculations page, Cube Designer, 186, 192 201 Calculation Tools section, 187 Connect to Database dialog creating calculated measure, 190 opening SSAS database, 84, 85 Script Organizer section, 186 Connection Manager Calculations tab, Cube Designer, 258–262 creating data sources, 103 calculated measures, 259–262 Connection Properties dialog, Excel, 377, 378 named sets, 262 connection types, Report Builder 2.0, 419 calendars connections Date dimension, 160 data source connections from OLAP to Excel, setting up fiscal calendar, 163 374–378 Time dimension, 159 containers, for dimension members, 148 CASE statement Convert to Partition action, writeback, 313 calculated measures, 189 CoordinatorExecutionMode property, 336 catalogs, SSAS, 56, 57 count measure, OLAP engines, 27 CDC (Change Data Capture), 68 counter groups, MSAS 2008, 338 Cell Data tab, roles, 351 Create AdventureWorks DBs option, 432 Cell Styles gallery Create an empty cube option, Cube Wizard, 170 pivot tables, Excel 2007, 383 Create Data Mining Structure dialog, 283 Change Settings button, Process Cube dialog, 335 CREATE MEMBER statement, MDX, 236 Change Settings dialog Create Testing Set dialog, 287, 288 processing Analysis Services objects from credentials, 101 BIDS, 211 double-hop problem, 348 Processing options tab, 335 crosstab result set, SQL, 220 Channel dimension, 137 cub folder, 57 charts cube browser see also pivot charts BIDS, 13 SQL Server Reporting Services, 410–418 SSMS, 90 Children function, MDX, 234, 240 Cube class, 51 classification predictions algorithm, 276, 277 Cube Creation Wizard, 8 Clear Writeback Data action, 313 cube design wizard, BIDS, 63 Client Initiated option, notifications Cube Designer storage properties, dimensions, 142 actions, 265–270 Clustering algorithm, 276 calculated measures, 259–262 Codd, E.F., 7 Calculations page, 186 CodePlex Calculations tab, 258–262 setting up Adventure Works, 431 Dimension Usage tab, 314 Color Expressions section, Cube Designer enabling/disabling Excel writeback, 313 creating calculated measure, 191 key performance indicators, 263–265 Column Content Specify dialog named sets, 262 creating data-mining model, 287 perspectives, 270–272 composite key, 169 translations, 272–273 conditional formatting for values Cube Editor, Dimensions pane, 81 pivot tables, Excel 2007, 383, 384 opening Dimension Editor from, 82 Config.ini file Cube menu changing location, 54 creating calculated member, 186 441
  11. INDEX cube specific tabs, BIDS 2008, 77 from pivot tables to dimensional processing, cube structures, SSAS, 59–65 4 cube structure, 63–64 hierarchies, 25 data source view (DSV), 61–62 identifiers (members), 16 data sources, 61 linking data and parameters, 16 dimensions, 64 managing Analysis Services, 89–90 mining structures, 65 measure groups, 63, 184–185 Cube Wizard selecting measure group tables, 170–171, Create an empty cube option, 170 174, 175 creating cubes, 170 measures, 17, 18, 27–31, 63, 182–184 Generate tables in the data source option, creating skeleton dimensions and 170 measures, 170 Select Creation Method page, 174 relating measures and dimensions, 179– Select Existing Dimensions page, 173, 176 181 Select Measure Group Tables page, 170, 171, selecting measures, 171–172, 175, 176 174, 175 metadata locations, 55 Select Measures page, 171–172, 175, 176 partition folders, 57–58 Select New Dimensions page, 176, 177 performance management, SSAS, 352–371 Use existing tables option, 170, 171, 173, 174 Process dialog, 208 Welcome page, 173, 174 processing CubeDimension objects, SSAS, 51 Analysis Services, 206, 207, 333–335 CubePermission collection, SSAS, 51 automating, 339 cube-processing job, 337 reprocessing, 340 cubes, 15–18 scheduling, 343 aggregations, 27, 31–32, 359–367 relational databases and, 18 availability when processing, 208 schemas, 18–20 calculated measures, 33–34, 185–193 security, Analysis Services, 348–352 creating, 170–178 selecting members of dimensions on cube, creating empty cube, 170 30, 31 creating skeleton dimensions and selecting, Data Connection Wizard, 376 measures, 170 selection of various members, 31 selecting dimensions, 173, 176 setting up Adventure Works, 434 selecting measure group tables, 170–171, time dimensions, 161 174, 175 tuples, 227 selecting measures, 171–172, 175, 176 tuples, MDX, 223 using existing tables, 170–172, 174 viewing details of SSAS cube in SSMS, 9 cube with dimensions and members, 29 writeback, 32–33 data source view (DSV), 97–98 Cubes folder, Solution Explorer, 170 DBA tasks, Analysis Services, 333–348 Cubes tab, roles, 350 designing calculations in cubes, 186 currency conversion, BI, 257–258 designing for performance, 352–359 Currency dimension, 137, 138, 140 attributes design, 353–355 CurrentMember function, MDX, 235, 245 dimensions, 353 custom member formulas, BI, 256 partitions, 356–359 Customer dimension, 137 dimensions, 16, 18, 20–27, 63, 118 creating skeleton dimensions and measures, 170 D relating measures and dimensions, 179– Dashboard Designer, PerformancePoint Server, 181 424, 425 selecting dimensions, 173, 176 dashboard, MOSS 2007, 420, 421 folders for, 57 442
  12. INDEX data Maximum number of connections, 100 files in data sets, SSAS, 58 Query timeout, 100 multidimensional data, 3 service account, 100 Specify Data Type dialog, 287 Windows username and password, 100 Data Connection wizard Data Source Properties dialog data source connections from OLAP to Excel, creating chart in SSRS 2008, 414 375, 376, 389 Data Source References section, Data Source defining connection file, 376, 377 Designer, 99 selecting cubes, 376 Data Source Template dialog data connections building scorecard in PPS 2007, 426 building scorecard in PPS 2007, 427 data source view see DSV data mining, 13–14, 275–277, 289–310 Data Source View wizard, 107 creating data-mining model, 282–289 data source views examples of uses of, 275, 276 managing Analysis Services, 88–89 in Excel leveraging SSAS, 11 Data Source wizard, 102, 104 Prediction Query Builder, 299 data sources, 98–104 reasons for, 275 Access property, 350 resources (books and web sites), 435 building reports with tablix, 406 Save Data Mining Query Result dialog, 303 connections from OLAP to Excel, 374–378 SSAS 2005, 10 creating, 102–104 data mining algorithms, 276–277 cube structures, SSAS, 61 association predictions, 277 Generate tables in data source option, Cube classification predictions, 276, 277 Wizard, 170 Microsoft Clustering, 276 KPI lists, MOSS 2007, 422 Microsoft Decision Trees, 277 managing Analysis Services, 87–88 Microsoft Naïve Bayes, 276 providers for, 99 segmentation, 276 selecting source server and database, 203 Data Mining Extensions see DMX Solution Explorer, BIDS, 98 Data Mining Model Designer, 289–303 Specify Source Information page, 128, 129 Mining Accuracy Chart view, 289, 297–299 SSAS, 98 Mining Model Prediction view, 289, 299–303 Data Sources tab, roles, 350 Mining Model Viewer view, 289, 292–297 data storage, OLAP, 38–39 Mining Models view, 289, 291–292 Dimension Storage Settings dialog, 141 Mining Structure view, 289, 290–291 specifying dimension storage modes, 142– Process Mining Structure dialog, 290, 291 148 data mining objects, SSAS, 51 storage properties, dimensions, 141 data mining relationships data structures associating dimensions with measures, 64 parent-child structure, 157 relationship types, 181 recursive data structures, 157 Data Mining wizard, 282–289 Data tab Data set action type, 269 data source connections from OLAP to Excel, data sets 374 files, SSAS, 58 data warehouses, 37–38 sparse data set, 4 bottom-up design, 37 data size, scalability, 367 Management Data Warehouse, 65–67 Data Source Designer preparing, Accessory Buyers campaign, 278– credentials, 101 281 Data Source References section, 99 top-down design, 37 General tab, 99, 100 data warehousing, 4–5 Impersonation Information tab, 100 DataAggregation property, measure groups, 185 Isolation level, 100 Database class, SSAS, 51 443
  13. INDEX database object model, SSAS, 51 Defer Layout Update Database property, 197 Pivot Table task pane, 380 Database Selection wizard Define Relationship window, 180 perspectives, 272 Dependency Network, using Database Setup wizard Mining Model Viewer view, 296–297 Adventure Works, 433 deploying projects, 195–198 database solutions, BIDS, 82–86 Adventure Works, 434 creating Analysis Services Project, 82–84 Deployment tab properties, 197 opening SSAS database, 84–85 deployment methods, databases, 198 as part of project, 85–86 Deployment Mode property, 197 DatabasePermission objects, SSAS, 51 deployment script, 202 databases Deployment Server Edition property, 197 Create AdventureWorks DBs option, 432 Deployment Server Version property, 197 deployment methods, 198 Deployment Wizard, SSAS, 198–201 sample databases, 431 files to run deployment script, 202 databases, SSAS see SSAS databases installing, 199 DataDir property, SSAS Specify Configuration Properties screen, 200, creating folder for database/catalog, 56 201 root for SSAS storage, 54 Specify Options for Partitions and Roles data-mining structures screen, 199, 200 managing Analysis Services, 90 Welcome screen, 199 Dataset Properties dialog deploymentoptions file, SSAS, 202 creating chart in SSRS 2008, 415 deploymenttargets file, SSAS, 202 DataType property, measures, 183 descendants and ancestors, 240 Date dimensions, 160 design creating, 161–166 Action Designer, 268, 269 hierarchies built in, 26 Aggregation Designer, 72 members on, 29 AMO design warnings, 72, 135–136 structure of, 161 Analysis Services, 352–359 date table, data source view Attribute Designer, BIDS, 354 Time dimension, 160 Dashboard Designer, 424, 425 DateTime value, Time dimension, 159 Data Mining Model Designer, 289–303 DBA tasks, Analysis Services, 333–348 data warehouses, 37 Analysis Management Objects, 340–343 dimension design, 69–71 automating cube processing, 339 DSV designer, 105–109 executing actions as part of process, 345–348 KPI Designer, 263 Performance Monitor, 337–339 named query designer, 112 PowerShell, 343 performance management, SSAS, 352– processing architecture, 336–337 359 processing cubes, 333–335 attributes design, 353–355 processing options, 335–336 dimensions, 353 processing with XMLA queries, 340 partitions, 356–359 scheduling processing of objects, 343–345 Perspective Designer, 270, 271 SQL Server Agent, 344–345 PowerPivot Designer, 321, 322 SQL Server Profiler, 337 Translation Designer, 272 Debugging tab properties, 197 Design Aggregations option, 361 decision tree, exploring design ribbon Mining Model Viewer view, 292–295 pivot tables, Excel 2007, 382 Decision Trees algorithm, 277 Design tab default members, dimensions, 226 pivot charts, Excel 2007, 388 DefaultMember property, 156 det folder, 57 444
  14. INDEX development dimension types, 137–140 BIDS, 75–86 Account type, 137, 138 development server attributes, 138 changing name from localhost, 195 Bill of Materials type, 137 deploying projects from BIDS to, 195 Channel type, 137 Diagram Organizer, DSV designer, 105 Currency type, 137, 138, 140 dim folder, 59 Customer type, 137 dimension attribute, time intelligence, 253 Geography type, 137 dimension browser, 139 mismatching types, 138 specifying dimension storage modes, 143 Organization type, 137 Dimension collection, SSAS, 51 Product type, 137 Dimension Data tab, roles, 352 Promotion type, 137 dimension design, 69–71 Quantitative type, 137 dimension designer, 130 Rates type, 137 Browser tab, 131, 132, 134 Scenario type, 137 Dimension Structure tab, 133 Time type, 137, 138, 159–166 Dimension Editor Utility type, 138 attribute relationship designer, 70, 71 Dimension Usage tab editing full collection of properties, 81 Cube Designer, 314 dimension intelligence, 255 looking at AdventureWorks cube, 125 Dimension key errors tab, Change Settings dialog relating measures and dimensions, 179 processing Analysis Services objects from dimension usage table BIDS, 211, 212 AdventureWorks cube, 181 Dimension menu Dimension Wizard Add Business Intelligence Wizard, 139 BIDS 2005, 69 selecting Member Properties from, 150 BIDS 2008, 70 dimension properties, 136–148 creating dimensions, 127 accessing, 136 Date dimension, 161 attribute properties, 156 with attribute relationships, 152 ErrorConfiguration, 140 dimensional processing MdxMissingMemberMode, 140 from pivot tables to, 2–4 ProactiveCaching, 141 dimensionality, 228 processing, 140 dimension-processing job, 336 storage, 141–142 dimensions, 20–27, 64 StorageMode, 141 account business intelligence, 139 Type, 137, 140 account intelligence, 254 UnknownMember, 148 aggregation design, 362 WriteEnabled, 148 Analysis Services, 353 dimension security, 351 analyzing requirements, 117, 118 Dimension Storage Settings dialog, 141 as strings, 18 storage modes, 144, 145 associating with measures, 63 Dimension Structure tab, dimension designer, attributes, 21 133 creating creating dimension with attribute Date dimension, 161–166 relationships, 155 with attribute relationships, 152–156 specifying dimension storage modes, 143, creating cubes, 177 147 selecting dimensions, 173, 176 dimension table, OLAP, 18 creating skeleton dimensions and measures, dimension tables 170 snowflake schema, 20 cube structures, SSAS, 64 star schema, 20 cube with dimensions and members, 29 445
  15. INDEX dimensions (cont.) DisplayFolder, 184 cubes, 16, 64, 118 DistinctCount measure, aggregations, 183 Date dimensions, 160 DMVs (dynamic management views), 68 defining usage, 179–181 DMX (Data Mining Extensions), 303–310 editing full collection of properties for, 81 adding Naïve Bayes mining model to files in data sets, SSAS, 58 Accessory Buyers campaign, 305 hierarchies, 23–26 creating Accessory Buyers mining structure, built in date dimension, 26 304–305 dimension in BIDS, 64 predicting accessory buyers, 308–310 managing Analysis Services, 90 processing Accessory Buyers campaign, 305– Master Data Services (MDS), 313 306 MDX notation, 222 queries, 90 MDX query using for columns and rows, 232 using DMX development environment, 303– multisnowflake schema, 120 304 Nonempty function, MDX, 238 viewing Accessory Buyers mining model, parent-child dimensions, 157–159 306–308 pivot table nesting, 380 double-hop problem, 101, 348 processing ByAttribute, 353 downloads processing ByTable, 353 Adventure Works, 431 ProcessingMode, 140 drill-through actions, 265, 266 ProcessingPriority, 140 Mining Model Viewer view, 295–296 relating measures and, 178, 179–181 roles, 350 relationship types, 180–181 DSV (data source view), 97–98, 105–116 relationships with measures, 324 AdventureWorks, 9, 10 scalability, 367 Analysis Services, 9 Scenario dimension, writeback, 314 creating, 107–109 Select Existing Dimensions page, Cube creating AccessoryCampaign DSV, 281 Wizard, 173 creating attribute from, 149 selecting members from, 221 cube structures, SSAS, 61–62 selecting members on cube, 30, 31 named calculations, 110–112 showing all attribute values for, 150 named queries, 112–116 slowly changing dimensions, 21–23 self-referential table, 138 snowflake schema, 119 tables, 108, 169 specifying storage modes, 142–148 unified dimensional model, 48 SSAS, 127–148 DSV designer, 105–109 attributes, 148–156 Diagram Organizer, 105 creating dimensions, 127–134 Find Table button, 106 performance management, 353 finding tables, 106 Process dialog, 214 replacing tables, 106–109 processing Analysis Services objects, 206 table browser, 106 star schema, 119, 120 using DSV table navigator, 106 advantage of simple schemas, 126 dynamic management views (DMVs), 68 storage file extensions, 59 dynamic named sets, 262 time dimensions, 26–27, 159–166 tree functions, MDX, 239 tuples, MDX, 225, 226 E Dimensions pane, Cube Editor, 81 EDW (enterprise data warehouse) Dimensions tab, roles, 351 data mining algorithms, 275 Disable Writeback action, 313 Enterprise Edition, SSAS, 45–46 discover method, XMLA, 35 enterprise folder, Adventure Works, 434 Dismiss Warning dialog, AMO, 135 446
  16. INDEX entity relationship designer, PowerPivot, 323 Express Server, history of OLAP, 7 Error List pane, AMO, 135 Expression window, Cube Designer, 187 ErrorConfiguration property calculated measures, 261 dimensions, 140 creating, 190, 191 measure groups, 185 expressions, MDX, 187 errors Invalid login for user (null) error, 101 Essbase, history of OLAP, 7 F EstimatedRows property, measure groups, 185 fact relationships ETL package associating dimensions with measures, 63 scheduling OLAP maintenance, 215 relationship types, 180 Excel fact table analyzing OLAP data, 6, 7 foreign keys, 169 building report, 121–125 OLAP, 18 data source connections from OLAP to, 374– primary key, 169 378 Reseller Sales table, 169 importing external data into, 375 facts invoking actions via, 266, 267 Analysis Services, 183 PowerPivot from, 320–326 measures, 183 PowerPivot pivot table in, 323, 324 processing Analysis Services objects, 205, 206 PowerPivot tab, 323 scalability, 367 Slicer Tools tab, 325 file extensions viewing measures, 192 storage file extensions, dimensions, 59 Excel 2007, 373–398 FileStream, Adventure Works, 432 GETPIVOTDATA function, 385 Find Table button, DSV designer, 106 pivot charts, 374, 386–398 Find Table dialog, 106 pivot tables, 374, 379–386 Fiscal calendar, Date dimension, 160, 163 user-friendly interfaces for OLAP solutions, flexible relationships, attributes, 151 373 flows, account intelligence, 255 Excel 2010 folder hierarchy, SQL Server 2005, 52 PowerPivot tab, 320, 321 folder naming, SQL Server 2008, 53 setting pivot table options, 317 folders slicers, 323 AllowedBrowsingFolders property, 56 Excel Services foreign keys, tables PowerPivot workbook in, 326, 327, 328 attribute relationships, dimensions, 150, 151 Excel Services, MOSS 2007, 421, 423 fact table, 169 Excel writeback, 311, 313–320 self-referential table, 138 see also writeback Format statement, MDX, 246, 249 Clear Writeback Data action, 313 Format string, Cube Designer, 191 Convert to Partition action, 313 Format tab Disable Writeback action, 313 pivot charts, Excel 2007, 388 enabling, 313 FormatString, 184 leaf-level, 314 formulas Scenario dimension, 314 custom member formulas, BI, 256 using, 315–320 From Other Sources drop-down execute method, XMLA, 35 data source connections from OLAP to Excel, executing MDX queries, 229 375, 389 Existing Connections button Full Text Search data source connections from OLAP to Excel, setting up Adventure Works, 432 375 Functions tab, Cube Designer, 262 expenses, account intelligence, 255 functions, MDX see MDX functions 447
  17. INDEX G using partitions, 356 HTTP access, 348, 349 Gallery, PowerPivot, 326, 327 general actions, 268, 269 General tab I Data Source Designer, 99, 100 ideal line, Lift Chart tab roles, 349, 350 Mining Accuracy Chart view, 298, 299 Generate a time table in the data source method, identifiers see members 160 IgnoreUnrelatedDimensions property, measure Generate a time table on the server method, 161 groups, 185 Generate tables in the data source option, Cube Impact Analysis dialog, 210 Wizard, 170 Impersonation Information tab, Data Source Geography dimension, 137 Designer, 100 GETPIVOTDATA function, Excel 2007, 385 specifying dimension storage modes, 145 Goal Expression, KPIs, 263 Import Analysis Services Database wizard, 85 Import Data dialog, Excel data source connections from OLAP to Excel, H 377, 390, 391 importing external data into Excel, 375 hardware requirements income, account intelligence, 255 Analysis Services/SSAS, 41–42 indexes, Analysis Services header file building attribute relationship in dimension, files in data sets, SSAS, 58 151 helper objects, SSAS, 51 info file, SSAS, 58 hierarchies InfoPath Forms Server, MOSS 2007, 420 see also tree functions, MDX, INI file Ancestor function, MDX, 239 changing location, 54 attribute relationship designer, Dimension input data column Editor, 70, 71 Clustering algorithm, 277 AttributeHierarchyDisplayFolder property, 156 Decision Tree algorithm, 277 AttributeHierarchyEnabled property, 156 Naïve Bayes algorithm, 276 AttributeHierarchyVisible property, 156 Input Selection tab, Mining Accuracy Chart view, balanced hierarchy, 25 297 cubes and, 25 instawdwdb.sql file, Adventure Works, 434 CurrentMember function, MDX, 235 Integration Services, 347 dimension designer SSAS Processing Task, 215, 216 Browser tab, 132, 134 intelligence Dimension Structure tab, 133 account intelligence, 254–255 dimensions, 23–26 business intelligence, 251–258 hierarchies built in date dimension, 26 dimension intelligence, 255 with several hierarchies in BIDS, 64 time intelligence, 252–254 MDX notation, 222 Invalid login for user (null) error, 101 natural and unnatural hierarchies, 354, 355 ISO 8601 calendar, Date dimension, 160 natural hierarchies, 151 Isolation level, Data Source Designer, 100 OLAP engines, 25 pivot table nesting, 381 selecting hierarchy Members collection, 234 J unbalanced hierarchy, 25 HOLAP (hybrid OLAP), 39, 54 job-execution engine, 337 data sources, SSAS, 61 Jobs folder proactive caching, 141 scheduling OLAP maintenance, 217 448
  18. INDEX K logical architecture, SSAS, 49–51 logins Katmai, 41 Invalid login for user (null) error, 101 Kerberos, 101 key column Clustering algorithm, 277 M Decision Tree algorithm, 277 maintenance Naïve Bayes algorithm, 276 scheduling OLAP maintenance, 215–217 key performance indicators see KPIs Management Data Warehouse (MDW), 65–67 keys Manufacturing calendar, Date dimension, 160 composite key, 169 many-to-many relationships foreign keys, 138, 150, 151, 169 associating dimensions with measures, 63 primary key, 138, 169 Currency dimension, 140 KPI Designer, 12, 263 relationship types, 181 KPI lists, MOSS 2007, 420, 421–423 many-to-one relationships KPI organizer, 263 Currency dimension, 140 KPIs (key performance indicators), 11, 251, 263–265 marketing Goal Expression, 263 Accessory Buyers campaign, 277 KPI designer in BIDS, 12 Master Data Services (MDS), 311–313 revenue, 263 matrices Status (indicator and expression), 263 building reports with tablix, 408, 410 trend indicator, 264 Report Builder 2.0, 419 Value Expression, 263 SQL Server Reporting Services, 403, 404 tablix, SSRS, 402, 404 L Maximum number of connections, Data Source Designer, 100 languages, translations, 272 MDS (Master Data Services), 311–313 Layout tab MDW (Management Data Warehouse), 65–67 pivot charts, Excel 2007, 388 MDX (multidimensional expressions), 36–37, 187 LazyAggregations setting action expressions, 270 ProcessingMode, dimensions, 140 ampersand, 222 leaf level, hierarchies executing queries, 91–92 cubes and hierarchies, 25 Format statement, 246, 249 data members for nonleaf members in hard-coding members in, 351 parent-child hierarchy, 158 notation, 222–223 writeback, 314 parentheses, 227 leaf members, 221 sets, 228 liabilities, account intelligence, 254, 255 SQL and, 219–221, 235 Lift Chart tab, Mining Accuracy Chart view, 298 square brackets, 222 lift line, Lift Chart tab testing expressions and queries, 229 Mining Accuracy Chart view, 298, 299 time dimensions, 26 link analysis, 13 tuples, 223–227 linked measures and dimensions using MDX to produce pivot table, 232 choosing Standard/Enterprise SSAS, 45 MDX Designer, SSMS, 236 localhost MDX functions, 233–249 changing development server name from, aggregate functions, 241–245 195 Ancestor function, 239 Log On tab, SQL Server Analysis Services Properties Avg function, 241–242 screen Children function, 234, 240 specifying dimension storage modes, 146 CurrentMember function, 235, 245 449
  19. INDEX MDX functions (cont.) processing Analysis Services objects from Members function, 234 BIDS, 213 Nonempty function, 237–239 ProcessingMode property, 185 ParallelPeriod function, 247, 248 relating measures and dimensions, 179 Parent function, 239 relationship types, 180–181 PeriodsToDate function, 247, 248 Select Measure Group Tables page, Cube PrevMember function, 245 Wizard, 170, 171 time functions, 245–249 storage of, 71 TopCount function, 242–244 StorageLocation property, 185 tree functions, 239–241 StorageMode property, 185 YTD function, 248 Type property, 185 MDX queries, 36, 228–249 using partitions, 356, 357 see also queries Measure objects, SSAS, 51 aggregate functions, 241–245 measured location line, Lift Chart tab AS keyword, 237 Mining Accuracy Chart view, 299 axes, 233 MeasureExpression, 184 basic MDX query, 230 MeasureGroup collection, SSAS, 51 compared to SQL PIVOT command, 221 measures, 27–31, 182–184 CREATE MEMBER statement, 236 additive measure, aggregations, 32 executing, 91–92, 229 aggregations, 27, 183 functions, 233–249 as numbers, 18, 169 parenthetical operators, 237 associating dimensions with, 63 query-scoped calculated measure, 236 BIDS, 182 SELECT statement, 229–232 calculated measures, 33–34, 185–193, 259– session-scoped calculated measure, 236 262 slicer, 233 count measure, 27 SSMS, 229 creating cubes, 171–172, 175, 176 testing, 229 creating skeleton dimensions and, 170 time functions, 245–249 cubes, 17 tree functions, 239–241 fact table, OLAP, 18 using dimensions for columns and rows, 232 facts, 183 WHERE clause, 232–233 MDX queries, 230, 231 WITH statement, 236 nonadditive measure, aggregations, 32 year over year growth, 236 OLAP, 182, 233 MDX query editor, SSMS, 91 relationships with dimensions, 178, 179–181, MDX statements, 36 324 processing Analysis Services objects, 207 Select Measures page, Cube Wizard, 172 MdxMissingMemberMode property, dimensions, selecting, 171–172, 175, 176 140 for aggregation wizard, 362 measure groups, 184–185 semiadditive measure, aggregations, 32 AggregationPrefix property, 185 storage of, 71 BIDS, 182 time intelligence, 253 creating cubes, 170–171, 174, 175 viewing, 192 DataAggregation property, 185 Measures pane, BIDS, 182 ErrorConfiguration property, 185 members, 16, 221 EstimatedRows property, 185 All member, 221 folders for, 57 calculated member, 187 IgnoreUnrelatedDimensions property, 185 count measure, 27 metadata locations, 55 CREATE MEMBER statement, 236 ProactiveCaching property, 185 creating calculated member, 186 creating URL from dimension members, 35 450
Đồng bộ tài khoản