Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transfer SSAS cellset to recordset

Posted on 2010-01-04
6
Medium Priority
?
850 Views
Last Modified: 2016-02-12
I have been using some VBA in Excel to load data from a cube in SQL Servies 2005 Analysis Services into a an Excel worksheet.  This works great.

My problem is that I'm trying to use the code to load the results of the cube into an Access table.
I've attached the code which is used to load the cube cellset into a worksheet.   I need to change this code to load the cellset (rs) into an access table.
I have no idea how to create a recordset.
Set rs = New Cellset
        
    'Tidy the query of an erroneous spaces
    sQry = Trim(sQry)
    
    'Open the query that was constructed above
    With rs
        .Open sQry, cnn
    End With
        
    ' with the worksheet that we passed in (ws)
    With ws
        Dim curRow As Integer ' Just to help explain where we are
        curRow = startRow
        '*--------------------------------------------------------------------------------------------------
        '* Read in Column Header
        
        '*--------------------------------------------------------------------------------------------------
        For i = 0 To rs.Axes(0).Positions.Count - 1
            intCellY = startCol + i + rs.Axes(1).Positions(0).Members.Count + 1 '  change 1 if extra dimensions are added.
            '*Moves the Header across*'
            If rs.Axes(0).Positions(i).Members.Count = 1 Then
                .Cells(10, intCellY).Value = rs.Axes(0).Positions(i).Members(0).Caption 'header labels
            Else
                .Cells(5, intCellY).Value = rs.Axes(0).Positions(i).Members(0).Caption
                .Cells(6, intCellY).Value = rs.Axes(0).Positions(i).Members(1).Caption
            End If
        Next
        
        '*--------------------------------------------------------------------------------------------------
        '* Read in Row Header
        
        '*--------------------------------------------------------------------------------------------------
        For j = 0 To rs.Axes(1).Positions.Count - 1
            'intCellX = j + 1
            
            intCellX = j + rs.Axes(0).Positions(0).Members.Count + 10
            
            
            '*Shifts the rows down (originally 1)*'
            If rs.Axes(1).Positions(j).Members.Count = 1 Then
                .Cells(intCellX, 1).Value = rs.Axes(1).Positions(j).Members(0).Caption ' row label
            Else
                .Cells(intCellX, 1).Value = rs.Axes(1).Positions(j).Members(0).Caption
                .Cells(intCellX, 2).Value = rs.Axes(1).Positions(j).Members(1).Caption
                .Cells(intCellX, 3).Value = rs.Axes(1).Positions(j).Members(2).Caption
                .Cells(intCellX, 4).Value = rs.Axes(1).Positions(j).Members(3).Caption
                .Cells(intCellX, 5).Value = rs.Axes(1).Positions(j).Members(4).Caption
                .Cells(intCellX, 6).Value = rs.Axes(1).Positions(j).Members(5).Caption
                .Cells(intCellX, 7).Value = rs.Axes(1).Positions(j).Members(6).Caption
                .Cells(intCellX, 8).Value = rs.Axes(1).Positions(j).Members(7).Caption
                .Cells(intCellX, 9).Value = rs.Axes(1).Positions(j).Members(8).Caption
            
            
            
            
            
            
            
            
            
            End If
        
        
            '*--------------------------------------------------------------------------------------------------
            '* Read in values for corresponding row header
            
            '*--------------------------------------------------------------------------------------------------
            For k = 0 To rs.Axes(0).Positions.Count - 1
            intCellY = k + 2 'Shifts the numbers to the left - change 2 to according to the number of dimensions set above.
            .Cells(intCellX, intCellY).Value = rs(k, j).FormattedValue
            Next
        Next
    End With

Open in new window

0
Comment
Question by:lee_jd
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 26172234
1. You can use the xls to export data into MS access
2. Why do you need CUBE? You can create an export file and load MS access db directly through SQL server
0
 
LVL 2

Author Comment

by:lee_jd
ID: 26172788
I ahve the data in a cube in analysis services.  I need to get the data from the cube into Access.  I don't want to load the data into Excel as it's too large.  I need to take it straight into Access.  I'm sure it can be done.   I've got the part where I have the cube in memory in a cellset object.   I just need to move this data into a table.
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 800 total points
ID: 26176876
You can define a datasource to MS access db. And then use the code similar to the following...

Dim testDatasource As SqlDataSource = New SqlDataSource()
testDatasource.ConnectionString = "...." <<--map this to your connection db details
testDatasource.ProviderName = "...." <<---- map this to your version of access
testDatasource.InsertCommand = "INSERT INTO [tblaccess] ([UserName], [Address]) VALUES (@UserName, @Address)"
Loop until end of cellset
	{
        testDatasource.InsertParameters.Add("UserName", CS(1,1).FormattedValue)
        testDatasource.InsertParameters.Add("Address", CS(r,c).FormattedValue)
        testDatasource.Insert()
        }

Open in new window

0
 
LVL 5

Accepted Solution

by:
grzegorzs earned 1200 total points
ID: 26179345
The best way in my opinion is to execute MDX query which has one dimension on each axis. Of course you should also have an Access table with structure corresponding to cube dimensions. It should have one column for each dimension to store member key (or caption). You can have two or more columns for dimension member in order to store member captions and other properties. The table should also have one column for each cube measure.

For example - the cube has three dimensions and one measure. MDX Query can look like:
SELECT
  Dim1.ALLMEMBERS ON 0,
  Dim2.ALLMEMBERS ON 1,
  Dim2.ALLMEMBERS ON 2
FROM
 Cube
WHERE ([Measures].[My Measure])

The Access table has columns:
  Dimension1
  Dimension2
  Dimension3
  MeasureValue
The caption of member will be stored in the table.

Code below.

Best regards
Grzegorz

'oConnection - connection to SSAS
'mConnection - connection to Access

With mCellset

'Open cellset
.Open sQuery, oConnection

'open recordset to write
Set recSRC = New ADODB.Recordset
recSRC.Open "TableName", mConnection, adOpenKeyset, adLockPessimistic, adCmdTableDirect


'Check if we have any results
If .Axes(0).Positions.Count > 0 Then
  For nDim1 = 0 To .Axes(0).Positions.Count - 1
    sD1Caption = .Axes(0).Positions(nDim1).Members(0).Caption
    For nDim2 = 0 To .Axes(1).Positions.Count - 1
      sD2Caption = .Axes(1).Positions(nDim2).Members(0).Caption
      For nDim3 = 0 To .Axes(2).Positions.Count - 1
        sD3Caption = .Axes(1).Positions(nDim3).Members(0).Caption
 
        'cell value
        sValue = mCellset(nDim1, nDim2, nDim3).Value
        'is null?
        If Not IsNull(sValue) Then
          With recSRC
            .AddNew
            !Dimension1 = sD1Caption
            !Dimension2 = sD2Caption
            !Dimension3 = sD3Caption
            !MeasureValue = sValue
           .Update
          End With
        End If 'Not IsNull(sValue)
      Next
    Next
  Next
End If

'close recordset
recSRC.Close

'close cellset
.Close

End With

Open in new window

0
 
LVL 2

Author Closing Comment

by:lee_jd
ID: 31672426
Thanks for your help.  This solved my problem.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question