Home > Uncategorized > SSAS: how to import data into SQL Engine from SSAS

SSAS: how to import data into SQL Engine from SSAS

This will show you how to import data from SSAS to SQL Engine.

Connect to SQL Engine and Right Click on database where you like to import data from SSAS – Let’s say in my case I have a test database

clip_image002

It will open Import Windows – Provide Data Source (Select Provider for Analysis Services – in my case I am using SQL 2008R2, so selected 10.0)

clip_image004

Hit Properties for providing SSAS Server and Database Details

clip_image006

Choose a Destination

clip_image008

So you have an option of Writing a query or select a table – I haven’t explored option of writing Query, so selected Select Table

clip_image010

Select Single or Multiple table – for demo purpose, I have selected Item as Source

clip_image012

Hit Preview – this will show you data which you are going to import

clip_image014

there is a gotcha, if you click on Edit Mapping you will find something strange –

clip_image016

Yes, you got it – its data types

clip_image018

So, you need to change the data types, something as shown below –

clip_image020

Click OK – > Next and Hit Finish

clip_image022

Some warnings – related to truncate of data, I stated Type as Varchar and Size 254 – so it’s just informing that I can import only that much data in a column,

clip_image024

so I can ignore this message, because I know that I have less than 254 chars in my Attribute / Column / Filed.

After import checked table and found – as I have very small result set, so I can confirm that imported successfully

clip_image026

Questions – If I lost my SQL Database (RDBMS), can I use this methodology to get my data back from the SSAS Database?

Answer – No, it may not give you complete data again depends on design of your Database, let’s say you have ROLAP Partitions (Fact) in case of ROLAP you will not able to import data because AS doesn’t store data in SSAS – and if RDBMS source is missing for ROLAP Partition, Select query will not work; for MOLAP you can do but as I said earlier design of your database may impact data import.

As you have seen earlier in Item Dimension, I have only two columns but if you check DSV in a SSAS Database you will find three columns – oops which means, I lost one column – yes you are right.

clip_image028

So why Col is missing during import – here is an explanation, if you notice in my Dimension Structure I have only two attributes, that explains why only two columns were visible when we were importing.

clip_image030

Second question is why we are getting Item Name, in Item ID column.

Item – is a key attribute but if you notice Properties of this Key Attribute you will find what’s going on

clip_image032

Notice here, Name Col is ItemName – that explains – why I can’t see ItemId Value when I have imported data; Instead of ID as integer value its showing Name of the Item – which is expected because I set name column property.

Like I told you earlier – for disaster recovery you can’t rely on such imports – you need to have your SQL / RDBMS Backups in Place.

Advertisements
Categories: Uncategorized
  1. ArturV
    May 30, 2012 at 10:10 am

    Hi Karan, rellay great post.
    Simple and easy. I know that there are some caveats, but even so, it’s better than notihng.
    Keep up the great work.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: