C


CALCULATE statement, 183-184, 185

calculated measures

creating, 170-173

custom rollups versus, 210-211

defined, 76, 169

properties, 172

querying, 173

for reports, 611-612

calculated members

Calculations view for, 169-170

CREATE statement for, 188-189

defined, 169

deleting with DROP statement, 188

hidden, for KPI metrics, 278

on measures dimension (calculated measures), 76, 169, 170-173

on members, 169

overview, 75-77

in WITH clause, 188

Calculation function (MDX), 710

CalculationCurrentPass function (MDX), 710-711

CalculationPassValue function (MDX), 711

calculations. See also calculated measures; calculated members

adding to cubes, 264-272

assignments, 193-195

attributes as building blocks, 182

AUTO EXISTS behavior, 189-190

CALCULATE statement, 183-186

calculated members, 188-189

calculation model, Analysis Services versions and, 181, 182

cell calculations and assignments, 191-193

cube space and, 189-190

debugging, 182, 267-272

Freeze statement, 196

named sets, 186-187

order of evaluation, 222

recursion, 196

reviewing definitions of, 265-267

SCOPE statement, 190-191

call centers, dimension uses in, 487

CALL statement, 335-336

Call UDF-Name function (MDX), 711-712

calling functions (MDX)

built-in COM UDFs, 323

overview, 82-83

static versus non-static functions and, 331-332

stored procedures, 331-332, 334, 335-336

CASE expressions (MDX), 194-195, 712-713

cell calculations

assignment statements for, 193-195

CASE expressions for, 194-195

conditional, IF statement for, 193-194

CREATE CELL CALCULATION statement for, 191-193

direct assignment for, 195

SCOPE statements for, 193-195

cell writeback

defined, 341

equal allocation for, 361-362

incremental allocation for, 363-364

in-session (what-if) scenarios, 341, 354

measure groups and, 356

modifying the cube for, 355-358

partitions and, 356, 358

SSMS for, 365

updating a single cell value, 358-361

updating non-leaf cell values using allocation, 361-365

weighted allocation for, 362-363

cells

attributes specifying dimension type, 157

defined, 10, 66

Freeze statement for changing values in MDX scripts, 196

methods for accessing in cube space, 182

multiple calculations per cell, 222

overview, 66-68

removing empty cells, 201-203, 469-471

retrieving data from, 67-68

security scenario, 684-691

tuples for identifying, 68-69

writeback, 341, 354-365

cells action target type, 282

Change Settings dialog, 377-379, 577-578

Chart Wizard (Excel), 544, 546

.Children function (MDX), 713

Choose Data Source dialog (Excel), 523

classID of COM UDFs, 326

classification data mining algorithms, 488, 489, 490

client tools, 564

ClosingPeriod function (MDX), 713, 760-761, 762

CLR assemblies. See .NET assemblies (stored procedures)

clustering mining model

cluster characteristics in, 510-511

cluster discrimination in, 511-512

cluster profiles view, 510

creating, 507-508

creating with wizard, 512

default model, 508

nodes, 508

training the model, 508-509

CoalesceEmpty function (MDX), 713-714

colon (:) as MDX operator, 81

COM (Component Object Model) UDFs. See also assemblies

access permissions for, 325, 386

accessing in MDX queries, 326

adding to a database, 324-325

built-in functions, 322-323

calling, 323

classID of, 326

creating DLLs for, 324

creating functions, 323-324

cumulative sum function, 323-324

dimension security and, 681-682

disambiguating between functions, 326

impersonation mode options, 387-388

languages for, 321

managing assemblies, 386-389

.NET-based UDFs versus, 321-322, 338

overview, 321

parameter types for, 323

porting to .NET, 338

restrictions on, 323

return types for, 323

scope of, 324

USE LIBRARY syntax for, 325

CommandLine actions, 281

Commands and Options dialog (OWC), 554, 556

commas (,)

as MDX operators, 80

in tuple representations, 68

comments (MDX), 79

COMMIT TRANSACTION statement (MDX), 359

committing transactions

ForceCommitTimeout server property, 439

processing database objects and, 374-375

read DB commit lock and performance, 438-439

updating a single cell value and, 359

comparison operators (MDX), 80

completely balanced hierarchies, 9

Component Object Model UDFs. See COM UDFs

COMUDFEnabled feature, 398

configuring Analysis Services instances, 368

conformed dimensions, 13

Connect to Database dialog, 400-401

Connect to Server dialog, 476

Connection Manager dialog

for Integration Services, 570

remote partitions and, 307, 309

usage-based aggregation design and, 459

Connection Managers window (Integration Services), 568-569

Connection Properties dialog

Data Analyzer, 558

Report Wizard, 599, 605-606

Console Application template, 406

CoordinatorExecutionMode server property, 444, 474

Corporate Information Factory (Inmon, Imhoff, and Sousa), 13

Correlation function (MDX), 714-715

Count function (MDX)

Adventure Works example, 167-168

for aggregations, 166

overview, 86, 714

.Count functions (MDX), 715-716

Cousin function (MDX), 716

Covariance function (MDX), 716-717

CovarianceN function (MDX), 716-717

CREATE CELL CALCULATION statement (MDX), 191-192

Create command for assemblies, 388-389

Create Cube File dialog (Excel), 547-549

CREATE GLOBAL CUBE statement (MDX), 549

Create Named Calculation dialog, 99-101

Create Named Query dialog, 99, 100

Create New Data Source dialog (Excel), 524, 525-526

Create Role dialog, 397-398

CREATE SESSION statement (MDX), 154, 155-156, 162

CREATE statement (MDX)

calculated member creation by, 188-189

named set creation by, 187

CREATE SUBCUBE statement (MDX), 197-198

CreateWriteBackExampleTables.sql script, 342-343, 355

credentials

for data source connections, 661

for Report Server, 625, 626, 627

for synchronization, 395-396

CRISP-DM data mining process guide, 484

CrossJoin function (MDX)

Filter function with, 84-85

overview, 83, 718

cube action target type, 282

Cube Designer

creating KPIs, 273-276

overview, 152-153

Perspective window, 174-175

UDM and, 143

viewing KPIs, 276-277

cube dimensions

adding, 167-169, 258

browsing reference dimensions, 162-163

changing relationships with measure groups, 156-157

defined, 258

granularity attribute, 157

properties, 258

types of relationships with measure groups, 157-158

Cube Editor

browsing cubes, 53

Calculations view, 169-170

creating calculated measures, 170-173

Dimension Usage tab, 156-157

establishing reference relationships, 158-161

overview, 48-50

right-clicking within windows, 50

Translations pane, 175

cube space. See also cubes

AUTO EXISTS behavior and, 189-190

CREATE SUBCUBE statement for restricting, 197-198

EXISTS function for restricting, 198-199

methods of accessing cells in, 182

overview, 189

removing empty cells, 201-203, 469-471

SCOPE statement for restricting, 190-191, 197

SUB-SELECT clause for restricting, 200-201

Cube Wizard

creating a cube using, 44-48, 49, 145-151

custom rollups example, 213

default MDX script created by, 183

operations performed by, 156

cubes. See also browsing cubes; cube space; real-time cubes; UDM (Unified Dimensional Model)

access permissions, 316-319

actions, 281-293

adding and enhancing dimensions, 257-264

adding calculations, 264-272

aggregations in, 10, 11

bottom-up versus top-down approach to building, 43

business analysis and, 13-14

calculated measures, 170-173

calculated members, 169-170

cells, 10, 66-68

clearing data in, 376

creating mining models from, 512-513

creating using the Cube Wizard, 43-50, 145-152

defined, 8, 9

deleting, 145

dimensions, 156-163

drill-through, 280-281

establishing reference relationships, 158-161

fine tuning, 430-437

FROM clause specification of, 72

intelligence, 294-301

Key Performance Indicators (KPIs), 272-280

local, creating from Excel, 547-550

local, server cubes versus, 547

Measures object, 62

modifying for cell writeback, 355-358

objects, 57

overview, 9-11, 33-34

partitions, 57, 301-316

perspectives, creating, 173-175

processing, 375-379

properties, 165-166

querying calculated measures, 173

relationships between fact tables and measure groups, 164

removing empty cells, 201-203, 469-471

security, 316-319, 683-691

sets, 69-70

slicing, defined, 521

translations, creating, 175-176

tuples, 68-69

UDMs as, 341

writeback objects, 57

Cubes folder, 33-34

curly braces ({}) as MDX operators, 80

currency

conversion using business intelligence, 296-301

FormatString property for, 255-256

MeasureExpression property and, 254-256

.Current function (MDX), 718

.CurrentMember function (MDX), 718-719

.CurrentOrdinal function (MDX), 719

custom aggregates for reports, 620-623

custom functions. See COM (Component Object Model) UDFs; .NET assemblies (stored procedures)

custom rollups

Adventure Works example, 212-217

calculated measures versus, 210-211

CustomRollupColumn property, 212, 215, 216, 221

CustomRollupPropertiesColumn property, 216-217

defined, 210

depreciation example, 210-212

need for, 210

unary operators for, 218-222

UnaryOperatorColumn property, 218, 219

CustomData function (MDX), 719

CustomFilter function, 330-331

CustomRollupColumn property, 212, 215, 216, 221

CustomRollupPropertiesColumn property, 216-217

cut-and-paste, for modifying data of dimension members, 349



Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net