[ mssql ] create ODC file from the Excel to use in another program

Do 1. 2. like [ mssql ] query OLAP cube from the Excel

, but on 2. clear checkbox `Connect to a specific cube or table`.

3. Click Property

Image

4.

Image

Advertisements

[ mssql ] query OLAP cube from the Excel

Prerequisites

– `SQL Server Analysis Services (MSSQLSERVER)` running on server

1. In Excel, Data > From Other Sources

Image

Connect from same machine, fill `localhost`.

If connect from different machine, fill the IP address w/o port w/o instance name ( i.e. 192.168.17.35 ) and the username I used is w/o the workgroup. However, we have to enable firewall port 2383 on the server.

NOTE : just notice the connection string generated in the connection property

Provider=MSOLAP.5;Persist Security Info=True;User ID=Administrator;Initial Catalog=My Customers initial;Data Source=192.168.17.35;Location=192.168.17.35;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

NOTE : `My Customers initial` is the default name of the Analysis Services database.

2. Select cube that is on the server

Image

3. Add measure, select fact

Image

4. slice and dice

Image

[ xls ] VB Tips about VBA and macros in excel

1. From the Sheet, Right Click > Show Codes

2. The VB editor will be appears

TIPs for VB editor

– Go to definition ( Open Declaration )

right click > definition

Go back

right click > last position

– To prevent the screen swaping

Application.ScreenUpdating = False

or if we want to set it back

Application.ScreenUpdating = True

– Activate Worksheet

Worksheets(strSheetName).Activate

– Access Cell Values ( For example, if we want to access E4 cell

Worksheets(strSheetName).Range("E4").Value

– MsgBox function

definition

MsgBox(prompt(, buttons) (, title) (, helpfile, context))

prompt : string

buttons : can be vb MsgBox constanti.e. vbCritical

title : string

– Go To

Declaration

XXX: 

Usage

On Error Go To XXX

OR

XXX

– recordset

If Not rs Is Nothing Then rs.close

OR

rs.EOF

TIPs : record macro

1. View > Macros > Record Macro

2. do the event in excel ( i.e. save file as CSV )

3. View > Macros > Stop record Macro

The excel will generate the code for that action. The result will be in VBA modules.

TIPs : excel change column width and height

Home > Cells > Format

Insert Button

For MS 2010

Developer > Insert > ActiveX control > Button

Writing Excel macros

– save as .xls ( .xlsx does not contains all macro )

Option Explicit : force define variable ( This will be error at compile time. In VBA, we can check by `Debug > Compile VBA Project`.

On load worksheet and On Terminate Sheet

Note that the code is `Worksheet`, do not replace it with worksheet name !!!!

Private Sub Worksheet_Activate()
MsgBox ("Hi")
End Sub

Private Sub Worksheet_Deactivate()
MsgBox ("Bye")
End Sub

Protect VBA code

In VBA screen, Tools > VBAProject Properties

Protection tab
Tick Lock project for viewing
and set password