Friday, December 30, 2016

How to Fix SSAS database ID and Name miss match?


You can have a situation where your SSAS database ID and Name can be miss matched. If you try to manually change the Database Name but ID still the same; this could happen when you move solution from development to Test and then change the name but still ID did not change.

What could possibly happen if the ID and Name are not same:

1) If you deploy SSAS package via Visual Studio it will look like processing but actually you will not find updated processed cube. (you can look at the last processed property of the database)
2) If you have job agent and then try to run the job it will show like it's working but actually there will be no update of the cube. You can look at the last processed property timestamp.

So, it means your SSAS package will not be updated any more.

How to check Name and ID are miss matched?

After the deployment of your SSAS Package your SSAS database look like below; where you have Tabular and OLAP cube.

Fig 1: Analysis Server Database


If you look at the tabular database closely and see the property where name and Id are miss matched.

Fig 2: Name and Id miss match for the tabular cube




What is the fix?

At first, you need to create script of the database as like below, which will generate .XMLA file:
Fig 3: Create script of the Tabular database

Generated .xmla file will look like below where you can find ID and Name.



Secondly, You need to change the ID and Name from .xmla script and press 'F5' to execute the change. Now if you look into the databse you will find the ID and Name are completely matched.

Fig 4: ID and Name is updated


Now you will be able to deploy your package with new database name but with same metadata.

Fig 5: Deploy package with new database Name

You may not be happy at this point, since you wanted to have your original name back, well, it's simple just open SQL Server Data Tools and from the solution property change your Database name as like above screenshot (Fig 5), you can put your original Database name and then deploy; you are all set now.  You may want to delete other database that you created, no worries; go ahead and delete the old one.

Monday, December 5, 2016

How to Insert data into a table with Identity column

In general, you don't insert data manually in a table which has an identity column. However, in obvious cases if you need to insert data into the table manually then writing general inset Query will failed with error:

For example, you have a table (DDL) like below:
  Create table PurchaseOrder
   (orderId int identity(1,1),
    item nvarchar(100))

If you would like to insert data into the table, standard syntax for inserting data is like below:

    Insert into PurchaseOrder(orderId,item)
     values (101,'strawberry')


However, above query will failed with below error message:

Fig 1: Eror Inserting data into the table with Identiy column

To insert the data into a table with identity column you should not put any value for the identity column. e.g. 

Insert into PurchaseOrder (orderId,item)
values ('strawberry')  

And then you will see the result set like below:


Fig 2. Output of the above insert command

However, if you would like to explicity put value for the identity column. e.g. you would like to add a negative number as orderid to make an item invalid. (sometimes you may need for the data warehouse dimension and fact tables). SQL for inserting data with your own choice:

SET IDENTITY_INSERT PurchaseOrder ON
insert into PurchaseOrder(orderId,item)
values (-1,'An Island')
SET IDENTITY_INSERT PurchaseOrder OFF


Now the result will look like below:

Fig 3: Output after  the above SQL command