Prepare data for tabular models in Power BI

Question 1

PBI data model definition

Answer 1

query-able data resource that’s optimized for analytics

Power BI uses DAX (Data Analysis Expressions), while paginated reports can use either DAX or MDX (Multidimensional Expressions)

Question 2

PBI dataset definition

Answer 2

after developing data model in PBI desktop, once you publish to PBI service, it becomes known as a dataset

Question 3

analytic query

Answer 3

3 phases that are executed in this order:

1) filter (slicing)
2) group (dicing)
3) summarize

Question 4

tabular model

Answer 4

your PBI model (one or more tables of columns; can include relationships, hierarchies, and calculations)

Question 5

table storage mode

Answer 5

with the exception of calculated tables, each table has a storage mode

3 options:
1) import - queries data that is cached in the model
2) DirectQuery - queries go to data source; fact tables that use DirectQuery storage mode facilitate aggregate tables
3) dual - PBI determines most efficient of the above 2 options, striving to used cached data when possible

Question 6

model framework

Answer 6

table storage mode&raquo_space; determines model framework

1) import
2) DirectQuery
3) composite

Question 7

import model

Answer 7

BENEFITS
supports all source types
supports all DAX and Power Query (M)
supports calculated tables
cached data means fast queries

LIMITATIONS
shared capacity 1-GB limit
dedicated (Premium) capacity can go beyond 10-GB
imported data must be periodically refreshed
up to 8 times per day in shared capacity
up to 48 times per day in dedicated (Premium)

Question 8

DirectQuery model

Answer 8

BENEFITS
good for large or fast-changing sources
great when source database has RLS

LIMITATIONS
not all data sources are supported
not all Power Query (M) are possible (pivot and unpivot are not possible, for example)
analytic query performance can be slow (especially if things aren’t optimized with indexes)
can impact source system performance

Question 9

composite model

Answer 9

BENEFITS
design flexibility
enterprise models benefit from using DirectQuery on large sources and leverage some imported tables too
hybrid table gets latest data in real-time

LIMITATIONS
import still requires periodic refresh, of course
limited relationships

Question 10

dataflow

Answer 10

BENEFITS
reduce load on database queries
reduce number of users accessing source data
provide single version of properly structured data

LIMITATIONS
not a replacement for a data warehouse
row-level security not supported