Accessing and Analyzing Data with Microsoft Excel (Bpg-Other) - page 127


Index

Q

Query, 63–70, 213, 220

Query Analyzer, SQL Server 2000, 333

Query Wizard, Excel, 62–63, 219–24, 347–48

querying

Access, 142, 145–50, 155–56, 354–55

data analysis and, 28

data connection file types, 213

Excel, 12, 63–70, 275–76, 347–49

OLAP data, 219–24, 349

relational data, 33

SQL Server, 14

Web data, 12, 67–70, 156, 275–76, 348

question mark (?), 45

quick references

Access, 353–55

Data Analyzer, 358–61

Excel, 345–49

Office Web Components, 356–58

OLAP data in Excel, 349–51

PivotTable and PivotChart reports, 351–53



Index

R

radar charts, 28, 59

ragged data, 10

random sampling, 85

Rank and Percentile tool, Excel, 75, 83–84, 87–88, 305–6

ranking, Excel, 42

reading. See books

recording data, 5–9, 201–2

records, 2, 32–36. See also data lists, Excel

re-creating SQL Server databases, 333–34

References dialog box, 291

refreshing cube files, 219

relational data, 11, 13, 14, 32–35, 129–32

relational OLAP (ROLAP), 203–4

relationships, 32, 34–35, 196

renaming Spreadsheet Component worksheets, 171

repairing Access databases, 339–40

replicating Access databases, 338

reporting

Access, 150–53, 135–36, 355

Excel. See PivotChart reports, Excel; PivotTable reports, Excel

systems maintenance. See systems maintenance

restoring

Access databases, 338

OLAP cubes, 341

SQL Server databases, 334

results, function, 49

retail sales organizations, 4, 7, 9, 226

ROLAP (relational OLAP), 203–4

row fields, PivotTable report, 98

rules, XML, 264–66

run time, Office Web Components and, 161, 164–65, 174–75, 184–85



Index

S

Sampling tool, Excel, 75, 84–88

saving

Access databases, 142

Excel OLAP queries, 219

XML data in Excel using code, 311–13

scatter charts, 28, 58–59

schemas, XML, 259, 270–75

security

Access databases, 338, 340–41

SQL Server databases, 336–37

Web data, 159–60

select queries, 147–49, 354–55

series fields, PivotChart report, 100

server-based data storage, 3, 10–11, 195

servers, OLAP, 208–14

shortcut menu, PivotTable report, 115

slicers, 255

slides, exporting Data Analyzer views to PowerPoint, 256–57, 361

software, business intelligence, 225. See also Microsoft software

Solver Add-In, Excel, 89–93, 349

sorting

Access, 138–40, 144–46, 353

Chart Component, 187, 192

data analysis and, 20–21

Excel, 42–44, 49, 345

Excel data lists, 297–302, 345

Office Web Components, 356

PivotTable Component, 175–76

querying and, 28

relational data and, 33

Spreadsheet Component, 169

sources. See data sources

Spreadsheet Component, 162–72. See also Office Web Components

analyzing data, 167–70, 356–57

data-bound sheets, 171–72

features, 14, 162–67, 170–72

multiple worksheets, 171

programming, using code, 315–20

publishing Excel data to, 166, 356

XML data support, 170–71, 273

spreadsheet flattener, Excel, 266–70

spreadsheets, 3, 10. See also Excel; Spreadsheet Component

SQL Server 2000

Analysis Services. See SQL Server 2000 Analysis Services

backing up and restoring databases, 334

Data Transformation Services, 10, 15, 342–43

database maintenance, 331–37

Database Maintenance Plan Wizard, 335–36

databases, Access and, 155–56

Desktop Engine, 15–16, 155, 337

documenting and re-creating databases, 333–34

features, 14–15, 202

Office Web Components and, 171–72, 192

securing databases, 336–37

as server-based, 11, 195

SQL Server 2000 Analysis Services

archiving and restoring OLAP cubes, 341

Data Analyzer code and, 321

data formats, 10

features, 15, 16, 202

OLAP and, 195, 208–14. See also online analytical processing

processing OLAP cubes, 341–43

systems maintenance, 341–43

statistics tool, Excel, 76–78

status bar functions, Excel, 72

stock charts, 28, 59

storage, data, 3–6, 10–12, 203–4

stored procedures, SQL Server, 155–56

strategies, data analysis, 5–10

subtotaling. See summarizing

SUM function, 121, 302–3

summarizing

calculated totals in PivotTable Components, 179–80, 358

data analysis and, 22–23

Excel data lists, 70–73, 297–302, 348–49

filtering vs., 25

multidimensional data, 35

Office Web Components summary functions, 167–70, 178–79, 190, 357–58

percentages as totals, 181–82

PivotTable Component, 179, 358

querying and, 28

relational data, 33

sorting vs., 20

summary statistics, 76

surface charts, 28, 59

surveys, 8

system databases, SQL Server, 335

systems maintenance, 329–43

Access databases, 337–41

books about, 343

plans, 329–31

SQL Server 2000 Analysis Services, 341–43

SQL Server 2000 databases, 15, 331–37

SQL Server 2000 Desktop Engine databases, 337