Index

A

Accounts type 66

ADDCOLUMNS function 229–230, 234, 242, 319

additive measures 82

ADDMISSINGITEMS function 243–244

affinity 286

AGGREGATE function 201

AggregateFunction property 79, 80, 81, 82, 87

aggregate functions 87–89, 232, 247

aggregation

semi-additive 88–89

Aggregation Design Wizard 324

AggregationMemoryLimitMax property 313

AggregationMemoryLimitMin property 313

aggregations 293, 323, 324–326

partition 331

processing 313

ALLEXCEPT function 236

ALL function 234, 241–243

allocations 265–268

ALTER SCHEMA permissions 293

ALTER TRACE permission 293

Analysis Services 6, 32

Analysis Services DDL Task 119

Analysis Services Management Object (AMO) client library 135

Analysis Services Stored Procedure (ASSP) 311

Analysis Services stored procedures (ASSP) 217–219

Analyze In Excel dialog box 150–151, 160–161

ANCESTOR function 196

AND function 254

AND logic 238

AND operator 239

ASSOCIATED_MEASURE_GROUP property 208

ASSP. See Analysis Services Stored Procedure

AttributeHierarchyEnabled property 25

AttributeHierarchyVisible property 64–65

AttributeKey 25

attribute member groups 321–322

attribute members 22, 24

grouping into ranges 25–26

AttributeName 25

Attribute property 76

attribute relationships 332–333

defining 69–77

performance tuning and 76

properties 75–76

attributes 8, 22

configuration, in the Date dimension 68

converting to member property 25

granularity 37, 85–86

mixing, from multiple hierarchies 177

parent 45

AttribuuteHierarchyEnabled property 65

audit key 9, 11

AutoFilter menu 102

AVERAGEA function 247

AVERAGE function 247

AverageOfChildren function 88

AVERAGEX function 248

axis naming 175

B

bidirectional filters 113, 114–115

BillOfMaterials (BOM) type 66

Boolean expressions 215, 238, 239, 254

bottlenecks

in queries 307, 311–312, 317–318

BufferMemoryLimit property 313

bulk-mode evaluation 311

business intelligence (BI) semantic model

defined 1

multidimensional 1–96

tabular 1

business key 8

business logic 206, 219

business requirements 3–4

ByAccount function 88

ByAttribute setting 312–313

ByTable setting 312–313

C

cached queries 300

cache warming 312

calculated columns 110, 123–124, 133, 137

calculated measures 77, 86, 202–203, 206–209, 229, 293

creating 246–256

date and time functions 250–252

filter functions 253–254

logical functions 254–255

mathematical functions 247–249

time intelligence functions 250–252

variables 255–256

calculated members 200, 209–210

calculated tables 124–125

CALCULATE function 207, 223, 233–237, 241, 249, 261

to transform row context 235–236

with ALL 234

CALCULATETABLE function 237, 245–246

calculations

query-scope 199–206

CALENDARAUTO function 125

CALENDAR function 125

cardinality 110

Cardinality property 76

CASE statement 202

cell-by-cell evaluation 311, 312

cell-level security 213–217, 311

Change Settings dialog box 339

Channel type 66

child members 201

CHILDREN function 195

client-side impersonation 100

CLOSINGPERIOD function 197

Color attribute 64

Color hierarchy 184

columnar storage 130–134

column filters 102

column names

syntax for 224

columns

calculated 110, 123–124, 133, 137

filtering 238–240, 241–244

columnstore indexes 155

compatibility level 99, 128

composite keys 25, 46

conditional expressions 202–203

configuration

refresh interval settings 138–142

security and permissions 143–154

tabular model

storage and data refresh 129–138

conformed dimensions 26

Connection Manager 13

constrained delegation 129

CONTAINS function 153

CoordinatorExecutionMode property 315

COUNTA function 247

COUNTBLANK function 247

Count function 88

COUNTROWS function 247, 319

COUNTX function 248

CPU utilization 314–316, 321

CREATE CACHE statement 310

CREATE command 211

CREATE MEMBER command 207, 208, 209, 210, 219

cube dimensions 47

cubes

adding measures to 77–78

custom MDX solutions in 206–222

developing 27–30

dimensions in 36–75

measure groups in 83–86

measures in 77–89

Cube Wizard 27–30

Currency type 66, 67

CURRENTMEMBER function 202, 311

CUSTOMDATA function 154

CustomData property 154

Customers type 67

D

data

aggregation 293

compression 131–133

numeric 10

real time access to 155–162

data analysis

using DAX 258–267

Data Analysis Expression (DAX) language 104–105, 171

creating formulas using 222–265

performing analysis using 258–267

database dimensions 47

database role 143, 145–148

databases

multidimensional. See multidimensional databases

OLAP 2, 5

OLTP 2

tabular. See tabular data model

DataDir property 288

data mining dimension models 54–56

data mining model viewer 56

Data Mining Wizard 54–55

data model

designing 2–11, 322–328

snowflake dimension design 6

star schema 4–6

tabular 97–170

data provider

installation 13

data retrieval

for MDX queries 34

data source

development 12–15

filtering 101–102

tabular data model 99–100

view design 16–18

with DirectQuery 155–156

DataSourceImpersonationInfo database property 15

data source view (DSV) 16–18

data table 124–125

DataType property 79

data warehouse 124

data warehouses 2

DATEDIFF function 250

date dimension 8

date functions 250–252

DATESYTD function 252

Date type 68

DAX formula engine 300

DAX functions 156

DAX queries 306

calculated measures 246–256

CALCULATE function 233–237

comments in 239

evaluation context 230–233, 238

filter 237–246

for allocations 265–268

for ratio to parent calculations 261–264

for top ranked items 258–260

for variance 260–261

incorrect order of operations 232

ORDER BY clause in 224–225

query-scoped measures 228–230

START AT sub-clause in 225

structure of 223–228

tracing 302–303

tuning 317–319

variables in 255–256

DAY function 265

DefaultMeasure property 174

DefaultMember property 174

default members 212–213

DEFINE clause 228–229

Define Relationship dialog box 37

degenerate dimension column 10

deployment

multidimensional database project 31–32

options 127

SSAS 279–291

tabular data model 126–129

transactional 32, 127

DESCENDANTS function 191–195

Description property 79

design warnings 30

development cycle, 9, 12, 16, 19, 13, 138, 143, 52, 152, 71, 255

Diagram view 109

dictionary encoding 131–132

dictionary fragmentation 136–137

dimensional modeling techniques 4

dimension models

data mining 54–56

fact 38–40

many-to-many 57–61

parent-child 40–45

reference 49–53

regular 36–38

roleplaying 45–49

slowly changing 61–65

dimension processing 312–313, 316, 334–335

dimensions 2–3

changing, and mining structures 75

conformed 26

database vs. cube 47

developing 19–27

for roleplaying 47

in cubes 36–75

intermediate 52

linked 26–27

materializing 53

optimization 323

snowflake 117

storage models and 35

dimension security 213–217

dimension tables 6

attributes 8

lineage or audit key 9

primary key for 8

SCD history 8–9

dimension types 66–68

dimension usage 36–38

Dimension Wizard 19–23

DirectQuery mode 117, 128, 129, 155–162

conversion to 156–157

convert in-memory queries to 162

data source for 155–156, 157

limitations 156–157

modeling with 157–160

with Excel and PivotTables 162

with Oracle and Teradata 161–162

DirectQuery query plans 306

DirectQuery With In-Memory option 128

discretization properties 26, 322

disk layout

configuration of 288–289

Display Folder 106

DisplayFolder property 79

DistinctCount function 88

DIVIDE function 249, 258

division operator (/) 249

DMVs. See dynamic management views

domain controllers 154

drillthrough limits 322

dynamic link library (DLL) files 217

dynamic management views (DMVs) 289, 307, 309

dynamic security 153–155

E

EffectiveUserName property 151

END SCOPE statement 220

EOMONTH function 265

ErrorConfiguration property 332

error handling 339

EVALUATE clause 223, 228

evaluation context

function arguments and 238

of queries 230–233

Excel 106, 171, 300

DirectQuery with 162

Extended Events 300, 305–306

Extensible Markup Language for Analysis (XMLA) 15, 140

extract-transform-load (ETL) processes 317

extract-transform-load (ETL) tool 6–7, 104

F

fact dimension model 38–40

factless fact table 11

fact tables 10–12

file system cache 281, 293

filter context 230–231

filter DAX queries 237–246

ADDMISSINGITEMS function 243–244

ALL function 241–243

FILTER function 237–240

filter direction 111–115

FILTER function 190–191, 237–240, 318

filter functions 253–254

filters

bidirectional 113, 114–115

column 102

data source 101–102

MDX queries 182

partition 119

row 102, 148–152

financial account data 66

FirstChild function 88

FirstNonEmpty function 88

flags

for DESCENDANTS function 192

Flight Recorder 288–289

FORE_COLOR property 208

foreign keys 9, 10, 17, 40, 46, 54, 107, 109

FORMAT function 226

FormatString property 79, 80

FORMAT_STRING property 200

formula engine 300, 318

FREEZE statement 220

FROM clause 173

Full Control (Administrator) permissions 147

Full Control (Administrator) role 214

fully qualified column names 224

functions. See also specific functions

aggregate 87–89, 232, 247

arguments 238

date and time 250–252

DAX 156, 222–265

filter 253–254

in MDX queries 182–199

iterator 237

iterator aggregate 248–249

KPI 343

late binding in 311

logical 254–255

mathematical 247–249

navigation 195–196

nested 239–240

nonadditive 87

semiadditive 87

set 183–195

statistical 201–203

string 203–205

time 197–199

user-defined 311

G

Geography type 67

Get Data From Cache events 314

granularity attributes 37, 85–86

Grid view 109

GroupAffinity property 285–286

GROUP BY clause 72, 226–227

H

HardMemoryLimit property 284

Hard Memory Limit threshold 283

HASONEFILTER function 319

HASONEVALUE function 319

HEAD function 187

hierarchies

attribute relationships and 69

default members 212

leaf level 207

natural 71, 73, 116, 323

parent-child 40–45, 117

ragged 40

sets 176

storage 134

tabular model 115–117

unique names for 69

unnatural 72–74

user-defined 65, 69–77, 261, 323

history

managing 6–7

SCD 9–10

history tracking 8

Hybrid OLAP (HOLAP) storage model 33, 35

I

IFERROR function 319

IF function 254, 266

IgnoreUnrelatedDimension property 84

ImpersonateCurrentUser option 129

impersonation account

read permission for 16

Impersonation Settings property 128

incremental development 104

indexes 133, 317

columnstore 155

In-Memory option 128

in-memory queries

converting to DirectQuery 162

in-memory storage 130

in-memory tabular query monitoring 300–306

In-Memory with DirectQuery option 128

installation

data providers 13

instance placement 289–291

IntelliSense 105

intermediate dimensions 52

internal rate of return (IRR) calculations 217

Internal VertiPaq Scan 304

IO trashing 312

ISBLANK function 319

ISEMPTY function 319

ISERROR function 319

ISFILTERED function 263, 266

iterator aggregate functions 248–249

iterator functions 237

J

JavaScript Object Notation (JSON) files 346

joins 317, 327

K

key attribute 24

KeyColumn property 322

key performance indicators (KPIs) 340–345

Kimball, Ralph 4

L

LANGUAGE property 210

LastChild function 88

LastNonEmpty function 88

leaf level 207

lineage key 9, 11

linked dimensions 26–27

linked measure groups 85

linked tables 126

locale ID values 210

LogDir property 286–287

logical AND 238

logical functions 254–255

logical keys 17

logical primary keys 51

logical query plan 301, 303

logical solutions 206–219

calculated measures 207–210

calculated members 209–210

default members 212–213

named sets 211–213

SCOPE statements 219–222

LowMemoryLimit property 284

M

Manage Relationships dialog box 109–110, 112

many-to-many dimensional models 57–61

many-to-one cardinality 110

materialization 318

dimension 53

mathematical functions 247–249

MAXA function 247

Max function 88

MAX function 247

MAXX function 248

MDX formula engine 300

MDX objects 172–173, 206

MDX queries. See Multidimensional Expression (MDX) queries

architecture 300

axes in 175

calculation results caching 206

conditional expressions 202–203

creating 171–206

filtering 182

functions 182–199

identifying bottlenecks in 307

in SSRS 205

members and sets 176–178

Profiler trace of 297

query-scoped calculations 199–206

steps in processing 182

stucturee oof 173–176

tuning 311–312

tuples 178–180, 188, 208

tuple sets 181–182, 244

with single measure 175

MDX solutions, custom 206–222

Analysis Services stored procedures 217–219

calculated measures 206

calculated members 209–210

default members 212–213

dimension and cell-level security 213–217

named sets 211–213

SCOPE statements 219–222

measure columns 11

MeasureExpression property 79

measure groups 83–86

properties 84–85

remote linked 85

measures 2

adding to cubes 77–78

additive 82

aggregate functions 87

calculated 77, 86, 229, 246–256, 293

designing and implementing 77–82

filter by 240

name assignment 229

properties of 78–79, 80

query-scoped 228–230

tabular data model 104–107

member expressions 200

MEMBER keyword 200

member property 25

members 176–178

calculated 209–210

default 212–213

MEMBERS function 183–186

MembersWithDataCaption property 43

MembersWithData property 43

memory

management 312–314

nonshrinkable 280

non-union memory access 285–288

property values 282, 284

shrinkable 281

memory configuration 138, 140

Memory HardMemoryLimit property 281

memory limits

configuration 280–285

default 281

multidimensional-mode 280–282

tabular-mode 283–285

Memory LowMemoryLimit property 281

Memory TotalMemoryLimit property 281

Metadata folder 204

MINA function 247

Min function 88

MIN function 247

MINX function 248

model design 322–328

model viewer

data mining 56

monitoring tools 307–310

dynamic management views (DMVs) 309

Performance Monitor 308–309

Task Manager 309

trace events 310

Msmdsrv.exe process 280, 283, 286

Msmdsrv.ini file 145

multidimensional databases 1–96

creating, using SSAS 2–33

cube development 27–30

data source development 12–15

data source view design 16–18

developing dimensions 19–27

project creation 12–13

project deployment 31–32

soure table design 2–11

defining attribute relationships 69–77

dimensions in cubes 36–75

KPIs in 342–345

measures and measure groups 77–89

model optimization 322–327

role-based security 213–217

star schema for 4–6

storage model for 33–35

Multidimensional Expression (MDX) queries 34

Multidimensional Expressions (MDX) language 171

multidimensional-mode memory limits 280–282

Multidimensional OLAP (MOLAP) storage model 33–34

multidimensional performance issues 311–317

multidimensional processing tuning 316–317

multidimensional query monitoring 292–300

multidimensional translations 345–346

multi-threaded operations 286

N

NameColumn property 61

named calculations 18

named queries 18, 50–51, 58–60, 84

named sets 211–213

NAME function 204, 205

Name property 76, 79, 80, 85

NamingTemplate property 43

natural hierarchies 71, 73, 116, 323

natural key 8

navigation functions 195–196

nested FILTER functions 239–240

Network Load Balancing (NLB) clusters 291

NEXTMEMBER function 196, 204

nonadditive functions 87

None function 88

NONEMPTY function 190–191

NON EMPTY keyword 189–190

nonshrinkable memory 280

non-union memory access (NUMA) 285–288

NOT ISFILTERED function 263

NUMA. See non-union memory access

numeric data 10

O

objects

cubes 27–30

MDX 172–173, 206

named sets 211–213

processing options 134–138

renaming 18, 24

ON COLUMNS clause 175

one-to-one cardinality 110

online analytical processing (OLAP) databases 2, 5

online transactional processing (OLTP) system 279

ON ROWS clause 175

OPENINGPERIOD function 197

Oracle 161–162

ORDER BY clause 74, 224–225, 226, 227

OrderBy property 24, 186

ORDER function 186–188, 191

OR function 254

Organization type 67

OR logic 238, 239

P

PARALLELPERIOD function 198–199

parallel processing 338–339

parent attributes 45

parent-child dimension models 40–45, 117

PARENT function 195

ParentIsBlankSelfOrMissing setting 44

ParentIsBlank setting 44

ParentIsMissing setting 45

ParentIsSelf setting 44

Partition Manager 119–120, 159, 159–160, 160

partition processing 315–316

partitions 326–327

adding 328–330

aggregations 331

defragmentation 136–137

DirectQuery mode and 157, 158–160

multidimensional models 121

processing 328–334, 337

processing scripts for 140

properties 332–333

remote 290

storage 330–331

tabular model 117–121

writeback 333

Partition Wizard 328–330

performance counters 308, 310, 315, 321

performance issues

CPU utilization and 314–316

memory management and 312–314

multidimensional 311–317

query bottlenecks 311–312

resolving 310–321

tabular 317–321

tabular processing memory consumption 320–321

tabular processing time 319–320

performance management 291–328

Performance Monitor 307, 308–309

performance monitoring

drillthrough limits 322

identify bottlenecks in queries 307

in-memory tabular query monitoring 300–306

model design 322–328

monitoring tools 307–310

multidimensional query monitoring 292–300

usability limits 321–322

performance tuning

attribute relationships and 76

PERIODSTODATE function 199–200

permissions

additive 143, 217

assigning 146

database 147, 214

user 293

permissions table 153

PerNumaNode property 287

perspectives 122–123

physical query plan 301, 305

pivot tables 79, 81–82, 82

with allocations 267–268

with average and variance measures 261

with calculated measures 209–210

with calculated members 210

with consolidated ratio to parent variables 265

with count measures 249

with filtered measures 253, 254

with measures containing variables 258

with named sets 211, 212

with ranking measure 260

with ratio to parent variables 263

PivotTables 106, 111–112, 116, 150

DirectQuery with 162

Power BI Desktop 171

PowerPivot

importing model from 126

Power View 106, 171

PREVIOUSMONTH function 251–252

PREVMEMBER function 196

primary keys 8, 17, 51, 109

Prior Year Sales formula 252

Prior YTD Sales formula 252

ProactiveCaching property 334

Process Add operation 320, 336

Process Add operations 135

Process Affected Objects setting 339

Process Clear operation 336

Process Clear operations 135–136

Process Clear Structure operation 337

Process Database dialog box 138–139

Process Database role 147, 214

Process Data operation 320, 336

Process Data operations 136

Process Default operation 336, 337

Process Default operations 136

Process Defrag operations 136–137

Process Full operation 336, 337

Process Full operations 134, 137

Process Index operation 337

processing

dimension 334–335

multidimensional objects 336–338

partition 328–334, 337

settings 338–339

ProcessingGroup property 334

ProcessingMode property 332

processing operations

creating 138–140

options 134–138

Processing Order setting 338

Processing Pool Job Queue Length 315, 321

ProcessingPriority property 332–333

processor groups 285

Process Recalc operation 320, 321

Process Recalc operations 136, 137

Process Structure operation 337

Process Thread Pool 312–313

Process Update operation 336, 337

Products type 67

project creation 12–13

Promotion type 67

PROPERTIES function 205

pseudo-SQL 317–318

Q

qualified names 176

Quantitative type 67

queries

bottlenecks 311–312, 317–318

cached 300

converting in-memory to DirectQuery 162

DAX 222–265, 306

CALCULATE function 233–237

comments in 239

evaluation context 230–233

filter 237–246

incorrect order of operations 232

query-scoped measures 228–230

structure of 223–228

tracing 302–303

tuning 317–319

variables in 255–256

evaluation context 238

identify bottlenecks in 307

MDX 34

architecture 300

creating 171–206

functions 182–199

members and sets 176–178

structure of 173–176

tuning 311–312

tuples 178–180

tuple sets 181–182, 244

named 18, 50–51, 58–60, 84

optimization of 322–328

performance, and SCD modeling 65

slow 292, 311

SQL 155–156, 173, 174, 316

VertiPaq 318–319

query binding 329

Query Editor 119

Query Mode property 128

query monitoring

in-memory tabular 300–306

multidimensional 292–300

Query Parser 292

query performance

natural hierarchy and 71

query plans 301, 303, 305, 306

query processing architecture 294, 300–301

query-scoped calculations 199–206

query-scoped measures 228–230

Query Subcube events 307, 312

Query Subcube Verbose events 307

Query Thread Pool 314

R

ragged hierarchies 40

random access memory (RAM) 131

RANK function 259

RANKX function 259

Rates type 67

ratio to parent calculations 261–264

Read Definition role 214

read permission 16

Read permissions 147, 153

reference dimension models 49–53

refresh interval settings 138–142

refresh operations 142

Register Server Assembly dialog box 217–218

regular dimension models 36–38

RELATED function 238

Relational OLAP (ROLAP) storage model 33, 34–35

relational tables 317

relationships

cardinality 110–111

filter direction 111–115

manually adding 109

storage 134

tabular data model 107–115

RelationshipType property 76, 332–333

remote partitions 290

ROLAP dimension 313

role-based security 213–217, 270–274

role-based security model 143–154

database role 143–145, 145–148

server administrator role 143–145

roleplaying dimension models 45–49

Roles property 151

rolling window strategy 118–119

ROLLUP syntax 227, 234, 242–243

RootMemberIf property 44

row context 230, 231–232

row filters 102

row filter security 148–152

ROW function 244–245

row-level security

configuration 148–154

row-oriented storage 130

run length encoding 132–133

S

sales quota allocations 265–268

SAMEPERIODLASTYEAR function 252

Scan_Vertipaq operators 301–302

SCD. See slowly changing dimension

Scenario type 68

SCOPE statements

defining 219–222

Script View 207–208, 211

security

cell-level 213–217, 311

dimension 213–217

dynamic 153–155

role-based 143–154, 213–217, 270–274

row filter 148–152

row-level 148–154

testing 150–152, 154–155, 216

SELECT clause 173, 174, 175

Select Creation Method page 19

SELECT DISTINCT statements 334

SELECT statement 18, 51, 74, 334

semi-additive aggregation 88–89

semiadditive functions 87–88

sequential processing 338–339

server administrator role 143–145

server-side impersonation 100

server tuning 311

service account 285

set alias 311

set expressions 195, 200

set functions 183–195

SET keyword 200

sets 176–178

named 211–213

tuple 181–182, 244

SETTOSTR function 203, 204

shrinkable memory 281

SIBLINGS function 196

Slice property 333

slowly changing dimension models 61–65

slowly changing dimension (SCD) 6–9

snowflake dimension design 6, 117

solution structure 12

sort order

for attribute members 24

Source property 79

source table design 2–11

business requirements 3–4

primary key 8

star schema 4–6

SQL queries 155–156, 173, 174, 316

SQL Server

authentication 13

SQL Server Agent 141–142

SQL Server Analysis Services (SSAS) 1, 2

configuration

disk layout 288–289

memory limits 280–285

non-union memory access 285–288

usability limits 321–322

creating multidimensional databases using 2–33

cube development 27–30

data source development 12–15

data source view design 16–18

developing dimensions 19–27

project creation 12–13

project deployment 31–32

soure table design 2–11

dimension processing 334–335

instance placement 289–291

key performance indicators 340–345

options for copying database 290

partition processing 328–334

performance monitoring and optimization 291–328

identify bottlenecks in queries 307

in-memory tabular query monitoring 300–306

model design 322–328

monitoring tools 307–310

multidimensional query monitoring 292–300

process monitoring 307

query performance 307

resolving performance issues 310–321

planning and deployment 279–291

role-based security 143–154

tabular model for 97–170

translations 345–346

SQL Server Data Tools for Visual Studio 2015 (SSDT) 2, 98, 125–126, 328

SQL Server Integration Services (SSIS) 6, 119, 289

SQL Server Management Studio (SSMS) 16, 120–121, 281

SQL Server Profiler 297, 300, 307, 310

SQL Server Reporting Services (SSRS) 171, 289, 300

SQL Server stored procedures 217

SSAS. See SQL Server Analysis Services

SSAS cache

clearing 296

SSAS server

reconnecting to 48

SSIS. See SQL Server Integration Services

SSMS. See SQL Server Management Studio

star schema 4–6

START AT sub-clause 225

statistical functions 201–203

storage

options 330–331

partition 330–331

row-oriented 130

tabular model 129–138

columnar 130–134

DirectQuery 129, 155–162

in-memory 130

storage engine cache 293

storage engine tuning 308, 311

storage model 33–35

HOLAP 35

MOLAP 33–34

ROLAP 34–35

stored procedures

Analysis Services 217–219, 311

SQL Server 217

string functions 203–205

STRTOMEMBER function 203, 204

Structured Query Language (SQL) 173

subcube operations 293

Sum function 87

SUM function 201, 202, 232, 247

SUMMARIZECOLUMNS function 226

SUMMARIZE function 225–228, 229, 236, 237, 319

with ROLLUP syntax 242–243

SUMX function 233, 248

surrogate keys 8, 10

SWITCH function 254, 266

syntax coloring 105

T

Table Import Wizard 125, 157

table names

syntax for 223

tables

calculated 124–125

creating relationships between 17–18

denormalization 327

pivot. See pivot tables

relational 317

renaming 18

time 124–125

tabular data model 97–170

adding data 99–103

calculated columns 123–124

calculated measures 246–256

compatibility level 99

configuration

refresh interval settings 138–142

row-level security 148–154

security and permissions 143–148

storage and data refresh 129–138

deployment 126–129

designing 97–125

diagram view of 108

for real time data access 155–162

hierarchies 115–117

importing from Microsoft PowerPivot 126

KPIs in 340–342

measures 104–107

optimization 327–328

partitions 117–121

perspectives 122–123

processing options 134–138

project creation 98–103

publishing, from Visual Studio 125–126

relationships 107–115

time table 124–125

Tabular Model Scripting Language (TMSL) 135, 140

tabular-mode memory limits 283–285

Tabular Object Model (TOM) 135

tabular performance issues 317–321

tabular processing

memory consumption 320–321

optimizing CPU utilization during 321

tuning to reduce time 319–320

tabular translations 346

Task Manager 307, 309

TempDir property 289

temporary files 289

Teradata 161–162

THIS function 220

Thread Pool Command property 286

Thread Pool IOProcess process 286

thread pools 284–286, 312–313

thread tuning 315

time functions 197–199, 250–252

time intelligence function 250–252

time series analysis 197, 250–252

time table

creating 124–125

Time type 67, 68

TMSL. See Tabular Model Scripting Language

TOM. See Tabular Object Model

TOPCOUNT function 188, 212

TOPN function 258–260

top ranked items 258–260

TotalMemoryLimit property 284, 313

Total Memory Limit value 283

TOTALYTD function 251

trace events 292, 296–297, 302–303, 307, 310

extended 305–306

Trace Properties dialog box 295

transactional deployment 32, 127

Transaction Mode setting 338

transactions 138

translations

adding 26

multidimensional 345–346

tabular 346

tuples 178–180, 188, 207, 208, 293, 291

tuple sets 181–182, 244

Type 1 dimensions 7–8

Type 2 dimensions 7

U

UnaryOperatorColumn property 45

UNION function 244–245

UNIQUENAME function 204, 205

unique names 176

UnknownMember property 23–24

unnatural hierarchy 72–74

unqualified column names 224

usability limits 321–322

usage-based optimization 289

Usage-Based Optimization Wizard 326

user-defined functions 311

user-defined hierarchies 65, 69–77, 261, 323

USERNAME function 153–154

user permissions 293

Utility type 68

V

value encoring 132

variables 255–256

variance 260–261

vectors 298, 299

VertiPaqMemoryLimit property 138, 284

VertiPaq operators 284, 305

VertiPaqPagingPolicy property 138, 285

VertiPaq queries 318–319

VertiPaq Scan events 304, 307

VertiPaq SE Query Begin events 317–318

VertiPaq SE Query End events 307, 317–318

VertiPaq storage engine 299

views

data source 16–18

dynamic management 309

Visible property 76, 79

Visual Studio

publishing tabular model from 125–126

W

WHERE clause 119, 158, 180, 181, 200, 208, 291, 318

Windows login 14

Windows privileges 285

WITH clause 200, 211

writeback partitions 333

writeback processing 338–339

Writeback Table Options setting 338

write permission 16

X

XMLA. See Extensible Markup Language for Analysis

xVelocity 103, 134

Y

YTD function 199