Business Intelligence (BI) - Developing SQL Data Models

Explore multidimensional models, cubes versus tabular models, and define dimension attribute relationships, measures, and aggregation functions in a multidimensional database.

Design a multidimensional database to build cubes by defining dimensions, measures, and measure groups within a BI environment that includes ETL, staging, data warehouses, and data marts.

Create multidimensional databases with SQL Server Analysis Services using Visual Studio, data sources, data source views, and the cube wizard to build an internet sales cube with measures and dimensions.

Design a multidimensional model by configuring dimensions with attributes and hierarchies to enable drilling, slicing, and filtering, including balanced, unbalanced, parent-child, and time dimensions.

Create and configure dimensions and hierarchies in Microsoft SQL Server Analysis Services, including customer and employee dimensions with parent-child relationships and a calendar time dimension, plus deployment and impersonation notes.

Define attribute relationships to the key attribute and polish member keys, naming, and ordering to improve dimension usability and query performance, with rigid time relationships as an option.

Optimize SQL data models by configuring attribute relationships, choosing key versus name, and ordering months by number. Deploy changes, fix non-unique relationships, and group names for usability.

Create measures and measure groups to form the first cube in your multidimensional model, aligning them with fact tables and applying distinct counts, visibility, and four-part formatting for readable queries.

Learn to build an SSAS cube with two measure groups—internet sales and reseller sales—defining measures, managing dimensions, and deploying with role-playing date dimensions.

Explore selecting aggregate functions for measures and measure groups, including sum, count, min, max, first, last, and average, categorized as additive, semi additive, or non additive for time-based analyses.

Create and deploy aggregate functions on reseller and internet sales data, using min, max, sum, and distinct count on the extended amount column, with Excel pivot table examples.

Learn to design a tabular model by importing from Power Pivot or building from scratch, compare tabular and multidimensional approaches, and prepare its deployment to Analysis Services.

Build a simple tabular model in Visual Studio by importing data from SQL Server and Power Pivot, create relationships among internet sales, reseller sales, and customers, and prune unused columns.

Deploy and process a tabular model in analysis services, explore deployment options from Visual Studio data tools and management studio scripts, and examine in-memory versus real-time processing.

Deploy a tabular model, script deployment with XML, and perform backup, restore, and synchronization. Process data using default or full options in management studio and integration services.

Explore tabular model storage options by comparing in-memory cache with direct query, balancing fast, cached queries against real-time data access, security, and flexible refresh strategies.

Explore direct query mode versus in-memory cache, processing strategies, and calculated tables in a role-playing date dimension, weighing real-time connectivity against refresh schedules for robust data models.

Configure tabular model security to control who connects and what data users see, using role-based access, direct query considerations, and dynamic DAX-based visibility, with perspectives for usability.

Create and manage roles in the tabular model to control read and processing permissions, apply row-level security with DAX expressions, and test access by impersonating users.

Explore MDX fundamentals for querying multidimensional models, contrast MDX with DAX, and apply calculated members, named sets, and scope for dynamic security in analysis services.

Compare sql and mdx by showing how a cube’s pre-aggregated measures drive mdx results using on rows, on columns, sets, and where clauses, with top-n analysis via generate.

Create calculated members and named sets in an MDX cube, place them with relevant measure groups, and optimize performance by leveraging cube aggregation and pre-summed values.

Create and deploy calculated members and sets in a cube using MDX, exploring the calculation and form views, debugging with management studio, and visualizing results in Excel.

Explore how mdx functions such as sums, averages, counts, and top 10 enhance dynamic cube queries by manipulating sets and using parent, children, and descendant relationships in hierarchies.

Explore MDX functions to analyze reseller sales by employee, using non empty and filter to show only revenue-generating employees, and build a time-series year-to-date with periods to date.

Utilize the MDX scope statement to manipulate values at intersections without creating a new member, applying alternative formulas for contexts like customers or regions with semicolon syntax.

Learn to adjust internet sales amounts per person using a calculated member or a scope statement, leveraging the married or single hierarchy to halve values for married customers.

Learn how custom MDX, calculated members, and named sets, guided by the cube scope, support a BI solution and improve the user experience, leveraging Excel and other analytics tools.

Explore how to query tabular models with DAX, compare it to MDX and SQL, and use the evaluate and calculate functions to derive filtered sums and time series analyses.

Demonstrates how calculated measures and calculated columns extend tables with derived values, using sums and averages, and creates a full name by concatenating last and first names for Excel pivots.

Develop data analysis skills using DAX to build time series calculations, including prior day and year-to-date totals, variance, and trend patterns. Learn patterns for simplifying DAX expressions with calculated columns.

Plan and deploy analysis services for cubes and tabular models, balancing memory, cpu, and disk footprint with availability, scalability, and edition differences to support production use.

Explore strategies for high availability and scalability of SQL Server Analysis Services, including failover clustering, load balancing, and remote partitions, while planning recovery and staging processing.

Monitor and optimize production model performance by troubleshooting processing and querying issues, using SQL Server Profiler, Performance Monitor, and Task Manager to identify CPU and memory constraints.

Monitor memory and cpu usage to spot spikes and max allocations, then optimize by redistributing instances and using profiler and performance monitor to trace mdx and tabular activity.

Identify bottlenecks in SSAS queries by evaluating server constraints, memory, CPU, and disk I/O; use performance monitor, task manager, and profiler insights to troubleshoot performance.

Configure memory limits for tabular and multidimensional server instances in management studio, using current and default values and enabling logging and backup directory options to monitor active users and queries.

Configure hard and total memory limits for analysis services to balance caching, avoid session drops, and monitor 65 to 80 percent thresholds and paging settings.

Configure partition processing to balance data freshness and performance, choosing full, default, or targeted partitions, with parallel or sequential execution and options for affected objects and recovery points.

Explore partition processing in Analysis Services for tabular and multidimensional models, using default, full, or incremental options to load and index yearly partitions efficiently.

Configure dimension processing to shape BI data models, aligning dimensions with tables and partitions, and automate processing with management studio, integration services, and XML scripts.

Configure dimension processing in management studio by selecting dimensions, choosing default, full, or update options, and optionally processing in parallel to optimize data refresh and metadata handling.

Build and deploy KPIs in cubes and tabular models, using a base value and goal to determine status and trend, then review results in Excel via Visual Studio.

Create a KPI for internet sales using MDX to define value, goal, status, and trend with parallel period comparisons and prior year data, plus folder organization in Excel.

Learn to create KPIs in tabular models with a gauge visualization, dynamic goals, and status ranges, using Visual Studio and Excel, and compare to multidimensional KPIs.

Create actions in the multidimensional model to support drill-through analytics and pass parameters to reporting services, delivering an integrated mdx-based action path beyond tabular models.

Create and customize drill-through actions in the cube designer, selecting a measure group and desired customer attributes to surface precise detail, exporting to Excel and to a report.

Learn how to create translations for BI models, enabling multilingual labels for dimensions, hierarchies, and measures in multidimensional and tabular designs using a JSON translation file and external translators.

Learn how to implement multilingual translations in a multidimensional model, mapping French and Spanish labels for measures and product attributes via relational data and Analysis Services.

Explore Microsoft SQL Server Analysis Services, from multidimensional and tabular models to dimensions, measures, calculated measures, and MDX queries, plus configuration, actions, translations, and KPIs.