-Design a multidimensional business intelligence (BI) semantic model
– Create a multidimensional database by using Microsoft SQL
– Server Analysis Services (SSAS)
– Design, develop, and create multidimensional databases
– Select a storage model
– Design and implement dimensions in a cube
Select an appropriate dimension model, such as fact, parent-child,roleplaying, reference, data mining, many-to-many –
, and slowly changing dimension
– Implement a dimension type
– Define attribute relationships
– Implement measures and measure groups in a cube
– Design and implement measures, measure groups, granularity,calculated measures, and aggregate functions
– Define semi-additive behavior
Design a tabular BI semantic model-
– Design and publish a tabular data model
– Design measures, relationships, hierarchies, partitions,perspectives, and calculated columns
– Relationships
– Create a Time Table
– Publish from Microsoft Visual Studio
– Import from Microsoft PowerPivot
– Select a deployment option, including Processing Option,Transactional Deployment, and Query Mode
– Configure, manage, and secure a tabular model
– Configure tabular model storage and data refresh
– Configure refresh interval settings
– Configure user security and permissions
– Configure row-level security
– Develop a tabular model to access data in near real time Use DirectQuery with Oracle, Teradata, Excel, and PivotTables Convert in-memory queries to DirectQuery
-Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)
Create basic MDX queries –
– Implement basic MDX structures and functions, including tuples, sets, and TopCount
Implement custom MDX solutions –
– Create custom MDX or logical solutions for pre-prepared case tasks or business rules
– Define a SCOPE statement
Create formulas by using the DAX language –
– Use the EVALUATE and CALCULATE functions
– Filter DAX queries
– Create calculated measures
– Perform analysis by using DAX
Configure and maintain SQL Server Analysis Services (SSAS)-
– Plan and deploy SSAS
– Configure memory limits
– Configure Non-Union Memory Access (NUMA)
– Configure disk layout
– Determine SSAS instance placement
– Monitor and optimize performance
– Monitor performance and analyze query plans by using Extended Events and Profiler
– Identify bottlenecks in SSAS queries
– Monitor processing and query performance
– Resolve performance issues
– Configure usability limits
– Optimize and manage model design
– Configure and manage processing
– Configure partition processing
– Configure dimension processing
– Use Process Default, Process Full, Process Clear, Process Data, Process Add, Process Update, Process Index, Process Structure, and Process Clear Structure processing methods Configure Parallel, Sequential, and Writeback processing settings
– Create Key Performance Indicators (KPIs) and translations
– Create KPIs in multidimensional models and tabular models
– Configure KPI options, including Associated measure group, Value Expression, Goal Expression, Status, Status expression, Trend, Trend expression, and Weight
– Create and develop translations
