Like Treats

At this point, I would like to share a treat with you by introducing you to a special product— Microsoft Analysis Services. Analysis Services is a special kind of database product that ships in the box with Microsoft SQL Server. Analysis Services is an OLAP product. OLAP stands for Online Analytical Processing. OLAP applications enable rapid analysis of numerical data along multiple dimensions. For example, an OLAP database may be designed to summarize financial data that can be viewed by product, business line, customer, and time period.

Analysis Services is rapidly gaining market share over traditional expensive, arcane OLAP products such as Hyperion Essbase due to its ease of use, performance, and price. Analysis Services ships with every version of SQL Server (Developer, Enterprise, Personal, and Standard). If you're building an analytical application that uses a large amount of data, I'd encourage you to give Analysis Services a look. If you don't have SQL Server, you can get a free trial version from Microsoft's website (http://www.microsoft.com/sql/).

Analysis Services is a very complimentary product to Excel. Many, if not most, Excel applications are analytical and numerical in nature. With Analysis Services, you can analyze an obscene amount of data using Analysis Services to serve the data and Excel to view the data. Excel can natively connect to Analysis Services via a PivotTable. Figure 16.19 shows an example of Analysis Services data as viewed through a PivotTable.

Microsoft also has an Excel add-in called CubeCellValue that you can download (http:// www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=3c4bbc1c-24da-4e44-8f9b-995341ff2c67); this allows you to retrieve Analysis Services data using cell formulas. In Figure 16.20, I've created a simple report that retrieves all of its values from Analysis Services using the CubeCellValue function from within Excel.

Figure 16.19

Analysis Services data viewed through a PivotTable

File Edt V»bv

IrtsBrt Format

Tools Dato Whdow Holp

! Type a question for help

-Sx

-1 -i -1 'à -

11 aa-. m ' • M

ft id

1 1

- *£É

1 j BvofcTatle -, .

C1

ß

A

B

1 G

1 D 1

E

f

1__d

1

Time

11997 M

A

2

Store Sales

Gender 1 »

4

Slore Courilrv [t

Slore State

Store City

F

M

Grand Total

5

USA

CA

Beverly Hills

23,280

22.462

45,750

B

Los Angeles

25.SD9

2B.B37

54,545

7

San Diego

27,588

26 343

54,431

a

San Francisco

2,266

2.175

4 441

3

1 CA Total

79,051

00.117

153,168

1C

OR

Portland

26,409

26550

55,059

11

Salem

43,417

43.802

87,218

12

1 OR Total

69 £25

72.452

142,277

13

WA

Bellingharn

2322

2.417

4,739

14

Bremerion

24,979

27317

52,096

15

Seattle

28,369

24.375

53,844

16

Spokane

25,466

24,1JS

49,634

17

Tacoma

37,287

37.557

74,844

10

Wall a Walla

2,185

2,521

4,706

19

Yakima

10,722

13303

24,329

20

1 WA Total

131,350

132,443

263,793

21

USA Total

280,226

285.012

565,238

22

Grand Total

280,226

285.012

565,238

23

v

H i

i M \ 3heetl / Street! X sheets / üheetl /

J<

►j

[ :J

The CubeCellValue add-in allows you to use formulas to retrieve data.

Figure 16.20

The CubeCellValue add-in allows you to use formulas to retrieve data.

□ Microsoft EKcel - clrapter 16 1 > ci ilil -s k ^

BS®

EH Pilo Edt Vbw Insert

Format Tools

Data Whdaw

HHp Tvpe a question F et help ~

-Sx

Ii J iï ¿1 ¿U

'AJ

IBwtTatfe-

m 1, I

C23 I

=CubeCeliValueffüüdmart 2POO - Lt-jtfG-C" ¡Vl^-UX',| ^3'xrj Sales]", 1 "[Time]. [1997 ]" Store]. [Al 1 Stores]. [USA]. [CA] . [ B29 &"]1[PrD d u et [. | All 1

B

25

Producls].[,ftCt26&nn

26

27

1393 Sales liy uty nml Product Type

26

Drink

Food

Non-Conaumsble

Total By City

29

Beverly Hills

3,341 1

33,424

8,386

45.750

3D

Los Angeles

4,824

39.137

10,534

54.545 54.431 4,141

31

San OiegO

5.065

35.306

10,060

32

San Francisco

"'374

3,276

792

33

Total By Pmdact

14.203

115,193

29,771 159.1ÖS

_g

34

35

--9

H *

1 n\ Steer i / sheet; \stieet3 / stieeti ,

f l<

mm

an

I Kcadr

Although PivotTables and the CubeCellValue are useful ways to access Analysis Services, they don't serve all purposes. For other purposes, you can use your VBA skills in conjunction with ADO to retrieve Analysis Services data just the way you (or your users) want it. From a developer perspective, data in Analysis Services is much easier to work with when compared to the same data from a traditional database. SQL statements can get pretty complex when working with data that your users want to view along different dimensions and at different levels of detail.

There is a catch to writing your own queries for Analysis Services. You have to learn a different query language. To query an Analysis Services database you use a language known as MDX (multidimensional expression). It's not very difficult to learn enough basic MDX to start becoming productive.

NOTE f you are totally new to Analysis Services and want to learn more, I recommend Microsoft SQL Server 2000 Analysis Services Step by Step by Reed Jacobson (Microsoft Press, 2000).

NOTE A good MDX book is MDX Solutions: With Microsoft SQL Server Analysis Services by George

Spofford (John Wiley & Sons, 2001).

Analysis Services ships with a sample database named FoodMart. Listing 16.5 presents an example that connects to FoodMart, queries FoodMart, and displays the results on an Excel worksheet. As an example of the versatility of ADO, Listing 16.5 uses standard ADO objects and methods to query the Analysis Server.

Listing 16.5: A Basic Example Using Data from Analysis Services

Option Explicit

Private Const msCONNECTION = _

"Data Source=localhost;Initial Catalog=FoodMart 2000;Provider=msolap;"

Sub BasicQueryExampleIQ

Dim rst As ADODB.Recordset Dim sMDX As String Dim ws As Worksheet

On Error GoTo ErrHandler

Set ws = ThisWorkbook.Worksheets(2)

' An Analysis Services query sMDX = "SELECT { [Measures].[Units Shipped], " & _ "[Measures].[Units Ordered] } on columns, " & _ "NON EMPTY [Store].[Store City].members on rows " & _ "from Warehouse"

' You can use ADODB.Recordset or ADOMD.Cellset Set rst = New ADODB.Recordset

' Open the recordset - implicit Connection object creation rst.Open sMDX, msCONNECTION

' Use of the Recordset object is handy because ' it allows use of the CopyFromRecordset method ws.Cells(1, 1).CopyFromRecordset rst rst.Close

ExitPoint:

Set rst = Nothing Set ws = Nothing Exit Sub ErrHandler:

MsgBox "An error occured - " & Err.Description, vbOKOnly Resume ExitPoint End Sub

Did you get a sense of déjà vu reading over this listing? In a true testament to the versatility of ADO, this listing is extremely similar to Listing 16.2. The output of BasicQueryExampleI is shown in Figure 16.21.

The reason ADO works with so many diverse data sources is because there is an abstraction layer between the data source and ADO. The provider specified in the connection string handles all of the details associated with performing various tasks using ADO. This makes your job easier because you don't have to learn a new object model for each type of data source. However, because OLAP data is so different from traditional data sources, ADO alone won't handle all of the tasks you may need to perform when working with data from Analysis Services.

Figure 16.21

The output of Listing 16.5

LJ Microsoft EKttl

chapter 16 1 > ciiiil----. s

se®

I Filu Edt View Ertsert

Forrnüt Tools Dato Wridow

Hdp

- 5 X

: _J

a -1 -J ¡a JI

iS ■ ÖM ï

ate ai

- * 1 ^ •

A1

f.

USA

A

B

1 c 1

D

E 1

F ~

1

USA

CA

Beverly Mills

10753

1 1 FflFl

Ï

USA

CA

Los Angeles

24,567

26.463

j

USA

CA

San Diego

23,635

26,370

j

USA

CA

San Francisco

1,656

1 £75

5

USA

OR

Fl MI an ü

a.515

9,108

a

USA

OR

S sie m

32,353

7*7

7

USA

WA

Bellingham

2,343

2.454 1 1 1

3

USA

WA

Bremerton

22,734

24 £10

3

USA

WA

Seattle

24,110

26.703

10

USA

WA

Spokane

11.663

12.028

11

USA

WA

Tsc-IITIH

32,411

=i5.<?=in

12

USA

WA

Wall a Wa lia

1P6D

2.074

13

USA

WA

Yakima

10,569

11 .42S

14

15

sfl

H i

I n\5heetl S,Sheet2 J5heet3 .iSheeW /

~~ l<

>J

I Ready

Consequently, there is a special version of ADO that can be used specifically to work with Analysis Services data. This version is called ADOMD. On one hand, ADOMD is a little more difficult to work with; on the other, it provides a richer object model that gives you more flexibility and control when you're working with data served from an analysis server. Key ADOMD objects are listed in Table 16.5. The most noticeable difference between ADO and ADOMD is that ADOMD uses a Cellset object to represent the data retrieved from a data source rather than a Recordset object as you saw with ADO.

Table 16.5: Key ADOMD Objects

Object*

Catalog

CubeDef(s)

Level(s)

Cellset

Cell

Parent

None

Catalog

Dimension(s) CubeDef

Hierarchy (Hierarchies) Dimension

Hierarchy

None

Cellset

Description

Contains multidimensional schema information specific to a multidimensional data provider

Represents a cube from a multidimensional schema

Represents one of the dimensions of a cube

Represents one way in which the members of a dimension can be aggregated

Contains a set of members, each of which has the same rank within a hierarchy

Embodies the results of a multidimensional query

Represents the data at the intersection of axis coordinates in a cellset

Axis (Axes)

Cellset

Represents a positional or filter axis of a cellset

Table 16.5: Key ADOMD Objects (continued)

Description

Represents a set of one or more members of different dimensions that defines a point along an axis

Member(s) Level or Position Represents a member of a level in a cube

Listing 16.6 presents an example that uses ADOMD. In order to use this example, you need to set a reference (Tools ^ References in the VBE) to Microsoft ActiveX Data Objects (Multi-dimensional) 2.7 Library.

Listing 16.6: A Basic Example Using ADOMD

Option Explicit

Private Const msCONNECTION = _

"Data Source=localhost;Initial Catalog=FoodMart 2000;Provider=msolap;"

Sub BasicQueryExampleII() Dim cst As ADOMD.Cellset Dim cat As ADOMD.Catalog Dim sMDX As String Dim ws As Worksheet

On Error GoTo ErrHandler

Set ws = ThisWorkbook.Worksheets(2)

' An analysis services query sMDX = "SELECT { [Measures].[Units Shipped], " & _ "[Measures].[Units Ordered] } on columns, " & _ "NON EMPTY [Store].[Store City].members on rows " & _ "from Warehouse"

' Unfortunately you need to explicitly create ' this object for the Cellset object (a Cellset ' object can't implicitly create a connection ' like a Recordset object can) Set cat = New ADOMD.Catalog cat.ActiveConnection = msCONNECTION

' Create new cellset and query away Set cst = New ADOMD.Cellset cst.Open sMDX, cat.ActiveConnection

Object* parent

Position(s) Axis or Cell

' Call procedure to display the data DisplayCellset cst, ws.Ce11s(1, 1)

cst.Close

ExitPoint:

Set cat = Nothing Set cst = Nothing Set ws = Nothing Exit Sub ErrHandler:

MsgBox "An error occured - " & Err.Description, vbOKOnly Resume ExitPoint End Sub

Sub Disp1ayCe11set(cst As ADOMD.Cellset, rgTopLeft As Range) Dim nRow As Integer Dim nRowDimensionCount As Integer Dim nColumnMember As Integer Dim nRowDimension As Integer Dim nRowMember As Integer

On Error GoTo ErrHandler nRowDimensionCount = cst.Axes(1).DimensionCount

' Loop through the rows contained in the cellset For nRow = 0 To cst.Axes(1).Positions.Count - 1

' Display labels for each row item

For nRowDimension = 0 To nRowDimensionCount - 1

rgTopLeft.Offset(nRow, nRowDimension).Va1ue = _ cst.Axes(1).Positions(nRow) _

.Members(nRowDimension).Caption

Next

' Display values at each dimension intersection For nColumnMember = 0 To cst.Axes(0).Positions.Count - 1 rgTopLeft.Offset _

(nRow, nRowDimensionCount + nCo1umnMember).Va1ue = cst.Item(nCo1umnMember, nRow).FormattedVa1ue

Next

Next ExitPoint:

Exit Sub ErrHandler:

Debug.Print "DisplayCellset Error: " & Err.Description Resume ExitPoint End Sub

Figure 16.22

The output of Basic-QueryExampleII

Figure 16.22

The output of Basic-QueryExampleII

□ Microsoft Enc.fcl

Chapter IG Examples.Kls

ses

\m E'l» Edt View Insert Format IdoIs &ata

Window Kelp

- 5

1 -J

U -J ¿frill

E - gl

fc-äH

A1

ft Beverly Hills

A

1 B 1

c I

D 1 E

1

Beverlv Hills

10,753

11,699

2

Los Angeles

24,507

26,403

3

San Diego

23,035

26,270

4

San Franeiset

1,638

1,875

5

Portland

8,515

9,163

a

Salem

33.393

35,797

?

Bellingham

2,34 S

2,454

3

Bremerton

22,734

24,010

3

Seattle

24,110

26,703

10

Spokane

11,069

12,028

11

Tacoma

32.41 i

35,930

12

Waila Wallal

1,350

2,074

13

Yakima

10,509

11 ,420

1 u

15

■it

' M

v

Ii 1

► M \ Sheetl \Sheet2 / Steet3 / Siieet4 /1 <

" .1

[ Read*1

Looking at this listing, you can really appreciate the convenience of the CopyFromRecordset method used in the prior listing. Rather than one simple statement to transfer the result set to the worksheet, this listing requires an entire procedure. The output of BasicQueryExampleII is shown in Figure 16.22.

One of the nuances that you need to be aware of when using the Cellset object is that you can't use the ActiveConnection property the same way as you could with a Recordset. In ADO, you can implicitly create a Connection object by passing a connection string to a Recordset's ActiveConnec-tion property. With ADOMD, you must set the Cellset's ActiveConnection using an explicitly created Catalog object. That is why a Catalog object is explicitly created in the BasicQueryExampleII procedure. A Catalog is roughly analogous to a Connection object in ADO.

The DisplayCellset procedure is a bit more difficult to understand then its ADO counterpart, the LoopThroughRecordset procedure presented in Listing 16.3. The extra difficulty comes from the need to handle data that is multidimensional. An ADO recordset is a two-dimensional entity. The two dimensions are fields (columns) and records (rows). A Cellset, on the other hand, can represent many dimensions. Consequently, the Cellset object uses more generic terminology to refer to items. To retrieve data out of a cellset, you need to examine the cellset's axes. An axis can be composed of one or more dimensions from the multidimensional data source. Depending on what you need to examine in a cellset, you use the Positions and Members objects or you can use the Item method of the Cellset object to retrieve a particular Cell object.

0 0

Post a comment