SQL SERVER – What is SSAS Tabular Data Model and Why to Use it

Analysis services in SQL Server 2012 can be either deployed in multi-dimensional mode or tabular mode or power pivot for SharePoint as well. Tabular mode is a new enhancement in SQL Server 2012 analysis service database structure. It is a columnar database capable of incredible performance and compression ratio. At this point, there is a lot of confusion in users on why to use the tabular model when we already have multidimensional model.So, let’s discuss these points first before creating a tabular model project.

Why to Use:

1)      Tabular model is quite easy to understand and implement, and is particularly made for empowering  information workers.

2)      The tabular model uses DAX for scripting which is similar to using excel formulas and is faster to learn.

3)      It uses Vertipaq (x-velocity) engine for in memory column storage, which gives great performance  and direct query mode for retrieving data and aggregates directly from the database which is beneficial for querying data in real time.

4)      Powerpivot models can be easily upgraded to tabular models, thus providing a path for business users and IT professionals to author models in familiar tools like MS Excel.

5)      All the client applications that support  multi-dimensional mode will also support  tabular and work natively with it. This is because tabular uses the same data provider that understands both MDX and DAX queries.

When Not to Use :

1)      When the source is based on dimensional modeling and has complex relationships with very large volume of data.

2)      No support for writing back or parent, child hierarchy

3)      When you want to do complex calculations, scoping and named sets

Getting started with the Tabular Model Project :-

Click on the new project in SQL server data tools, there will be 5 templates options under Business Intelligence à Analysis Services.  For this example, I‘m creating an Analysis service tabular project.

As soon as you click ok, you will be prompted to specify an instance of analysis services installed in tabular mode. This is the instance which will be used for creating a workspace database for the project.

Once you click ok, you will notice that, a model. bin file is generated. To  import data in the tabular model, go to model  in the toolbar menu and click Import data from source.

As you can see, you can import data from a variety of other sources. For the purpose of this example we will choose Microsoft SQL Server and click on Next >

Here you will be asked to enter credentials to connect from the data source. After entering the credentials, click Next >

Here you have to choose whether you want to import data from tables and views or either write a customized query  to specify the import data. For the purpose of this example, I’ve taken  the first option.

As I choose the first option we will get a list of tables and views from the database we selected in the earlier steps. Here you can select the tables and views that you want to import or optionally choose one table and click on the “selected related tables” button to include all related tables based on relationships. Click on Finish button

After you click Finish button import process will start, you can see the status of the import in the below screen

After the import process is finished, you can see the project, either in grid or diagram view. By default, you will see the grid view of all the tables you selected.

Click on the bottom right corner to see the same in the diagram view

Right click any table to see various options  that will enable you to create relationships, hierarchy or hide table from the client tool.

To summarize it all, we have created a tabular mode project, and imported data from a data source into this project.

Hope this post was helpful to clear all your doubts. To understand these concepts in greater detail, you can enroll for a Microsoft SQL Server 2012 course from a recognized IT training organization such as Koenig Solutions, which offers more than 1000 IT courses and certifications.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

This is a very common error with beginning developers when they start working with computed columns.

When a new developer finds a joy of computed column, it is very natural to use the same computed column for other columns. However, a computed column cannot be used in another computed column or it will give an error.

Here is a simple reproduction of the scenario.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);

Now we will add another computed column Col3 based of Col2 (which is computed column itself).

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col2*10;

When we execute above statement, it will give us following error.

Msg 1759, Level 16, State 0, Line 2
Computed column ‘Col2′ in table ‘Table1′ is not allowed to be used in another computed-column definition.

This is because we can not use computed columns in another computed column. This kind of referencing is not allowed in SQL Server.

Workaround:

Here is a quick workaround. Our end goal is to create another column which is multiply of Col2 by 10. Now Col2 is Col1 multiplied by 10. That means Col3 is Col1 multiplied by 100. Let us create a new column which is Col1 multiplied by 100.

-- Create Column
ALTER TABLE Table1
ADD Col3 AS Col1*100;

That’s it! We are done.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

A Very common question, I often receive is

Q: “How to ALTER computed columns?”

A: There is NO way to alter computed column. You will have to drop and recreate it.

Here is a demonstration of it.

If you try to alter the computed column it will throw following error.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);
-- Failed attempt to alter column
ALTER TABLE Table1
ALTER COLUMN Col2 AS Col1/10;

Above script will give following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

The best way to fix this is to drop the column and recreate it.

-- Drop Column
ALTER TABLE Table1
DROP COLUMN Col2;
-- Create Column
ALTER TABLE Table1
ADD Col2 AS Col1/10;

Let me know if there is any other way of altering the column without dropping it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

About four years ago, I wrote a blog post where I posted a script about finding backup time for all the databases. You can see the blog post over here SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. However, this script was just giving details about last full backup time. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database.

Here is the script.

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE
@dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Sravani, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Say No to DB Data Roles – SQL Security – Notes from the Field #022

[Note from Pinal]: This is a 22nd episode of Notes from the Field series. Security is very important and we all realize that. However, when it is about implementing the security, we all are not sure what is the right path to take. If we do not have enough knowledge, we can damage ourself only. DB Data Roles are very similar concept, when implemented poorly it can compromise your server security.

In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.


I am prejudiced against two fixed database roles: db_datareader and db_datawriter. When I give presentations or talk to customers, some are surprised by my stance. I have two good reasons to recommend against these two roles (and their counterparts, db_denydatareader and db_denydatawriter).

A Violation of the Principle of Least Privilege

The first reason is they violate the Principle of Least Privilege. If you’re not familiar with this security principle, it’s really simple: give permissions to do the job – no more and no less. The db_datareader and db_datawriter roles give access to all tables and views in a given database. Most of the time, this is more access than what is needed. This is a violation of the Principle of Least Privilege.

There are some cases where a user needs such access, but there is always the possibility that a new table or view will be added which the user should not have access to. This creates a dilemma: do I create new roles and remove the user from db_datareader or db_datawriter or do I start using DENY permissions? The first involves additional work.The second means the security model is more complex. Neither is a good solution.

Failing the 3 AM Test

The second reason is the use of these roles violates what I call the “3 AM test.” The 3 AM test comes from being on call. When I am awakened at 3 AM because of a production problem, is this going to cause me unnecessary problems? If the answer is yes, the solution fails the test. I classify db_datareader and db_datawriter role usage as failing this test. Here’s why: the permissions granted are implicit. As a result, when I’m still trying to wake up I may miss that a particular account has permissions and is able to perform an operation that caused the problem. I’ve been burned by it in production before. That’s why it fails my test.

An Example

To see why this is an issue, create a user without a login in a sample database. Make it a member of the db_datareader role. Then create a role and give it explicit rights to a table in the database. This script does so in the AdventureWorks2012 database:
USE AdventureWorks2012;
GO
CREATE USER TestDBRoleUser WITHOUT LOGIN;
GO
EXEC sp_addrolemember @membername = 'TestDBRoleUser', @rolename = 'db_datareader';
GO
CREATE ROLE ExplicitPermissions;
GO
GRANT SELECT ON HumanResources.Employee TO ExplicitPermissions;
GO

Pick any table or view at random and check the permissions on it. I’m using HumanResources.Employee:

We see the permissions for the role with explicit permissions. We don’t, however, see the user who is a member of db_datareader. When first troubleshooting it’s easy to make the assumption that the user doesn’t have permissions. This assumption means time is wasted trying to figure out how the user was able to cause the production problem. Only later, when someone things to check db_datareader, will the root cause be spotted. This is why I say these roles fail the 3 AM test.

If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Developer – How To Market Yourself as a Software Developer

“Everybody wants to make money and build a successful environment around and it is a universal truth my friend”, John mentioned to me while we were talking on Skype earlier this year.

Conversation: John and Pinal

John is the founder of simpleprogrammer.com and a very close friend of mine. We discuss pretty much anything and everything. He is from USA and I am from India, so our conversation usually starts either in the morning or evening, but it keeps on running for hours, thanks to our family who allows us to do this crazy long but productive talk.

When John said he has discovered how famous software developers make so much money, I found it very intriguing. I know John for many years, he does not do cheesy talk or say things without substance. I was wondering what would John do.

What would John do?

John had two options 1) He keeps this secret with him forever or 2) Tell his secret to the world. If you know John, you can easily guess that he is not going to keep quiet about it. He is going to make it public and make it universally accessible to the world. It is very similar to the publishing the books like Alchemist or The Secret or any other similar books. The author of those books does not keep the secret of success to themselves only. They share and make the secret a true instrument in people’s life.

As expected, John built a fine program of “How to Market Yourself as a Software Developer”. This is indeed a very interesting program as it is not a video, book, tutorial or any exercise file. It is the matter of the fact everything. John has not restricted the success story to a simple medium. He has let the story build around a concept and has not limited the potential of the program.

What did I do to help John?

Nothing. We all have nothing to do to help John. This is not about John or Simple Programmer. It is about a program which tells and shares secrets of How to Market Yourself as a Software Developer. We all are a software developer and we all are successful in our own field. When I say We – this also includes YOU too. Think about it, are not you successful in what you do? You absolutely are. We all have done something right in our past or present. We all have secrets which are not shared with the world.

Just like that, John has compiled his own success story with many other’s success stories and build this program. I think it is a story build with the experience of the many real software developer who has achieved something in their life.

What NEXT?

Join the program. I am sure there are plenty of things which you know, I should know to be more efficient and successful. Similarly, there are plenty of things other knows, but you need to learn to be more successful in your career. Why not learn it from each other. I will be interested to know your thoughts about how can I be more successful.

Secret Code

If you want to join the program. Here is the secret code I have got from John. You can go to the link http://simpleprogrammer.com/market to join the program. The program cost USD 299 however, when you use secret code sqlauthority, you will get a discount of USD 100 (that is 33% to the MRP).  You will have instantly access to the entire package immediately as well as 30 Day Money Back Guarantee.

What are you waiting for?

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – List User Defined Tables – Two Methods

There are different methods to list out user defined tables in MySQL. We will see two methods in this post.

Let us create the following tables in the database named Test

Create a database named Test

CREATE DATABASE Test;

Create the tables named items and sales in the database Test.

USE TEST;
CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Now to list out all user defined tables, use any one of the following methods

Method 1: Use SHOW TABLES command

SHOW TABLES;

If you execute the above statement, it will list out all the user defined tables in the database in which it is run (in this example the database name is Test)

The result is

Tables_in_test
 --------------
 items
 sales

You can also use database name along with this

SHOW TABLES FROM Test;

Which will give you the same result

Method 2: Use INFORMATION_SCHEMA view

You can make use of ANSI standard INFORMATION_SCHEMA.TABLES
view to do the same shown as below

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='Test';

The result is

Tables_in_test
 --------------
 items
 sales

This is a very small trick but works great when you want to know what are different tables exist in MySQL Database.

Reference: Pinal Dave (http://blog.sqlauthority.com)