|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/ssas2019/
课程评论：没有评论
本课程深入探讨SQL Server Analysis Services (SSAS)，一个功能强大的多维OLAP服务器和分析引擎。SSAS令用户能够高效地对海量数据进行切片和切块分析，并用于创建和管理分析数据库。 **核心内容：** * **SSAS概述：** SSAS（SQL Server Analysis Services）是一个分析服务平台，用于构建和管理分析数据库，这些数据库包含预聚合数据，以支持多种分析用途。它提供多维（Multidimensional）和表格（Tabular）两种模式。 * **SSAS优势：** * 高性能报表 * 多维数据分析 * 数据切片与切块分析 * 数据挖掘 * **利用Cube/Analysis数据库：** * 在SSDT/BIDS浏览器中分析数据。 * 使用Excel的PivotTable连接和操作。 * 通过SSRS、Cognos等报表工具生成报表。 * 编写MDX（Multidimensional Expressions）查询。 * 使用Panorama Novaview、ProClarity等工具进行数据分析。 * **SSAS角色：** * **SSAS开发人员：** 理解数据库结构、设计Cube、脚本编写、掌握MDX语言。 * **SSAS管理员：** 负责安装、配置、部署、处理、安全管理、备份恢复、监控及故障排除。 * **OLAP基础概念：** * **Cube：** Analysis Services中数据存储和分析的基本单元，包含聚合数据以快速查询，由维度和度量组成。 * **Dimension table：** 包含用于汇总数据的分层信息（如时间、地理位置）。 * **Dimension：** Cube中的分类，基于一个或多个维度表，通常包含自然层级以便汇总。 * **Level：** 单个维度中可检索的摘要类型（如时间维度中的周、月）。 * **Fact table：** 包含待汇总的基本事实信息（如订单明细、销售额）。 * **Measure：** Cube中基于事实表列的分析指标（如销售数量、利润）。 * **Schema：** 事实表与维度表之间的关系，主要有星型Schema（Star Schema）和雪花型Schema（Snowflake Schema）。 * **Data Source：** 定义SSAS如何连接物理数据存储的连接字符串。 * **Data Source Views：** SSAS数据库对象（Cube、维度、挖掘结构）使用的逻辑模型，以XML格式存储。 * **Named Queries：** 将SQL表达式表示为表，用于简化复杂维度表或合并多表数据，无需修改基础表。 **总结：** 本课程为学习者提供了SSAS的全面视图，涵盖了其基本架构、核心概念、应用方法以及不同角色的职责，旨在帮助用户构建和优化高效的数据分析解决方案。
SSAS full form is SQL Server Analysis Services.Multi-dimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data.SSAS is an analysis service platform, which can be used to create and manage the analysis database.SSAS Contains Preaggregated data , internally creates analysis database, and once the analysis database is ready, it can be used for many purposesIt has 2 variants Multidimensional and Tabular.One or more cubes can be presented in the analysis database.Advantages of SSAS:· High performance reports· Multidimensional data analysis· Slice and data analysis· Data mining purposeUsing the Cube Data Base or Analysis Data Base:There are several client tools to use Analysis database.a. Analyzing the cube database data in the SSDT/BIDS browser.b. Using the PIVOT table in the excel application to connect and work with cube database.c. Using Reporting tools (SSRS, Cognos) to generate the reports.d. By writing the MDX queries in the cube database.e. Using Panorama Novaview and ProClarity tools to analyze the data.SSAS Developer Roles:· Understanding the Data base structure· Designing the cubes· Scripting· Mdx languageSSAS Admin Roles:· Installation· Configuration· Deployment· Processing· Security· Managing[Backup and Restore]· Monitoring & Trouble shootingThe basic concepts of OLAP include:CubeDimension tableDimensionLevelFact tableMeasureSchemaData Source:Connection string that defines how Analysis Services connects to a physical data store.Data Source ViewsA data source view contains the logical model of the schema used by Analysis Services database objects-namely cubes, dimensions, and mining structures. A data source view is the metadata definition, stored in an XML format.Named Queries:A Named Query is a SQL expression represented as a table. It Can be used to divide large and complex dimension table to smaller and simple dimensions, it can also help us to unite columns from multiple tables to single table.It allows us to extend our table schema without modifying underlying base tables.CubeThe basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that's been aggregated to allow queries to return data quickly.Cubes are ordered into dimensions and measures. Dimensions come from dimension tables, while measures come from fact tables.Dimension tableA dimension table contains hierarchical data by which you'd like to summarize. Examples would be an Orders table, which you might group by year, month, week, and day of receipt, or a Books table that you might want to group by genre and title.DimensionEach cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data: time or category in the examples above. Typically, a dimension has a natural hierarchy so that lower results can be "rolled up" into higher results. For example, in a geographical level you might have city totals aggregated into state totals, or state totals into country totals.LevelEach type of summary that can be retrieved from a single dimension is called a level. For example, you can speak of a week level or a month level in a time dimension.Fact tableA fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, drug effectiveness information, or anything else that's amenable to summing and averaging. Any table that you've used with a Sum or Avg function in a totals query is a good bet to be a fact table.MeasureEvery cube will contain one or more measures, each based on a column in a fact table that you'd like to analyze. In the cube of book order information, for example, the measures would be things such as unit sales and profit.SchemaFact tables and dimension tables are related, which is hardly surprising, given that you use the dimension tables to group information from the fact table. The relations within a cube form a schema. There are two basic OLAP schemas: star and snowflake.Star Schema:Every dimension table is related directly to the fact table.Snowflake Schema:Some dimension tables are related indirectly to the fact table.For example, if your cube includes OrderDetails as a fact table, with Customers and Orders as dimension tables, and Customers is related to Orders, which in turn is related to OrderDetails, then you're dealing with a snowflake schema.
