Welcome to Class Guys , Well , we will be starting our week of by taking a sneak peek into SQL BI gearing up to the more elaborative and deep understanding about BI and its applications .

Before , just diving in , I would like to mention here , the useful resources , which are being referred here up in this article.

1. SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms130214.aspx)

Jorge had written about the Sql server books online , and its availability in his article ,which can be found on http://sqlchicken.com/2009/09/sql-university-basic-tools/

2. http://www.sqlauthority.com

First of all , a general question I would like to answer about BI .

Q . What is BI and what exactly it is there for ?

Answer : BI Stands for Business Intelligence .

We’ll make it little bit mathematical

BI=Business Intelligence

BI = Business + Intelligence

      Business= (Enterprise , Customary,etc)

      Intelligence= Decision

                  Decision = Data and Analysis

                           Analysis = Skills + Processes + Technologies + Tools(applications)

BI = (Enterprise,Customary,etc) + (Data and analysis)

So, now from above equation its clear that BI Means Analysis of Enterprise/customary Data .

Now , just summing up the above equation in words , BI basically refers to Processes,Skills,Applications,Technologies that are use to support the Decision making Process.

BI Technologies : When we do talk about BI Technologies , then its not just about reporting , but its lot more than reporting .

Just to explain about why BI is lot more than reporting , I would like to state here an live example ( going out of boundaries of BI and sql server) , of ICEBERG . when we see an iceberg , its 10% above the sea water while 90% beneath , so that’s the same thing with Reports , reports are only the visualization of that 90% of what is beneath it i.e a lot things including , Data , OLAP , Text mining ,Querying , Statistical analysis ,Benchmarking ,Predictive Analysis .

Summing up the above we got to know a new equation

BI = Reporting + Data mining + OLAP +Text mining + Benchmarking + Statistical analysis + Predictive Analysis + Querying .

Its Well Said by Danny Siegel about BI , that an effective BI system provides corporations with “one version of the truth”.

What BI is for ?

BI is there for Success of a business , because it helps you in making decisions and when you are able to make correct , accurate decisions on time then only the success is yours . Because if BI is there , then you are handy with your statistical analysis and predictive analysis (future , foreseen analysis) and can better make your decisions to remain competitive and stay ahead .

Hopes , the question we had started off with is answered and we are quite clear about the concept of BI.

Now , another Question Comes out to mind .

Q. What is the History of BI  ? Where all it came from ?

Answer : A basic question , what is the history of BI , how it came into existence and from where it get evolved , is it something new born ??

No , its not something which is recently evolved but today it is used more and more that’s why we are wondering and taking it as some new born , but its nothing like that ,

In a 1958 article, IBM researcher Hans Peter Luhn used the term business intelligence. He defined intelligence as “the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal.”

In 1989 Howard Dresner (later a Gartner Group analyst) proposed BI as an umbrella term to describe “concepts and methods to improve business decision making by using fact-based support systems.”. It was not until the late 1990s that this usage was widespread.

Wow , so above we came to know that BI do exists there since from a very long time , but today it is widespread and is present everywhere ,no matter whatever the business is , whatever the technology is , if some system is there , decision is there , BI is THERE .

Now comes another question :

Q. If BI is so vast , then from where to start with .?

Answer : Anywhere , that totally depends upon your choice , you can go for data mining , OLAP ,Text mining , Reporting , that all is your part , you need to decide what best you can do , because there are lots of tools available under BI , but you had to choose which tools you wish to learn , use and master .

Another tricky question :

Q. What language(terminology) does BI Speaks ?

Answer : Sure , that’s very important to learn the BI Language(Terminology) , because unless and until we are familiar with BI Terminology , we won’t be able to get hands on it , so just a quick view of BI Terms :

Aggregation : Information stored in summarized form

Agent : Application (client ) who searched the data and sends the alert.

Alert : Message sent by agents

Attribute : Fields or columns in table ,report ,charts .

Cube : Known as multidimensional format used for viewing and analyzing data .The fundamental structure for data in a multidimensional (OLAP ) System.

Data : Reality , facts stored in and processed.

Data Base : Collection of data

Data Cleansing / Scrubbing : Removing erroneous data

Data Mart : Local Data Warehouse , Small subset of Data warehouse for one or few divisions

Data Migration : Process of moving of data from one environment to other

Data Mining : Digging deep the hidden relationship of data and attributes among each other

Data Transformation : Process after data migration is done ,i.e the step of modifying the data as per the new data warehouse.

Data Warehouse : A storage of Enterprise/ customary data meant for to be analyzed .

Drill down and drill up : Process to move across hierarchy used in OLAP.

ETL : Extract , transform and load makes ETL . i.e get the data , transform it(modify) and migrate it .

DTS : its below ETL , because it’s a tool for ETL .

Star Schema : The simplified database as per the normalized forms of database management system

Fact Table : the table containing :

1. The fields storing the foreign keys which connect each particular number to the appropriate value in each dimension.
2. The fields storing the individual number, measure or any numeric value. Example(discount,price,etc)

Measure : A numeric value in fact table

Multidimensional Analysis / OLAP =Online Analytical processing, the technique to process and fetch out results in different –different dimensions of data i.e cubes .(widespread use of graphs and spreadsheets )

Slice : Tool for OLAP Viewing of data through multi dimensions example : Where clause in OLAP.

Dice : Tool for OLAP Viewing of data through multi dimensions

Replication : Replica of one data at another place.

Schema : Logical arrangement of data within database.

MDX : Multidimensional expressions, the query language for OLAP ,

Hopes , now ,as we have discussed couple of basic questions about BI ,we are ready with to get dive into the BI sea , but before getting in it , we’ll have a break and will continue on this interactive session in the same manner (if nobody complains of this is not a good idea to learn about what BI is .?) .

While writing this article , I was just wondering up from where to start this article on , because it is such a vast area to discuss on with that I cannot see the boundaries of , then thought of , why not to make it more interactive in an question / answer manner . I myself started asking questions to me as a layman and then tried to answer the questions .Hope you’ll enjoy this question answer class of BI , not to mention here , that if you do have any question up there in mind that is not covered up or required some more lights , then do revert back , I am waiting for your questions and would try my best to answer them .

You can reach me at govindsyadav[@]soliddotnet[dot]com

We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

Step1. Open Server Explorer and add a Data connection and connect to a your database which you need to publish. For this example i am using my tsqlchallenge database. You should point to the database you want to publish.

Step 2: Right Click tsqlchallenge.dbo node in Server explorer context menu will appear. You will find “Publish to provider” option there.

Step 3. Click Publish to Provider to launch Database Publishing Wizard

Step 4. Select Database which you want to publish.

Step 5.  Select output mode and file location for were script (.SQL) will generate and save. You can also directly publish it your shared hosting also by selecting “Publish to shared hosting provider”

Step 6. In the  NEXT  step you will get the Publishing Options. Where you have the option to choose the script for target database (SQL Server 2000 or SQL Server 2005) and the types of data to publish (Schema, Data or Schema+Data).

Step 7. Click finish & Review your selection summary this will now generate the script file.

Next you will see your publishing progress.

Step 8. The file generated ( .SQL)  contains your database  script that you can run on any SQL server to re-create all the tables, sprocs, views, triggers, full-text catalogs, etc. for a database, as well as import and add all of the table row data that was in the database at the time the .SQL file was created

Step 9. Now that you have .SQL file with all the necessary script to recreate the database, You can use Query Analyzer or Web Based SQL Server Admin to run this Script against your production SQL Server

I know everyone will be thinking why am i posting about BEGIN TRANSACTION and COMMIT TRANSACTION which are the very basic and common thing that every one is known about it and why and where we we need to use this statement in sql statements/queries  or in our code. But  sometimes, we make such kind of small mistake with this BEGIN and COMMIT statement that make a cause of deadlock in our application and when it happens no one thinks that this could be the reason of the dead lock. The same problem i faced sometimes back.

So the issue was, i have one store procedure which has some logic and on the basis of that some data Insert/Delete into the table. So in that store procedure there was a BEGIN transaction statement at the beginning but there was no COMMIT transaction statement at the end so whenever any statement executes on that table the resources for that connection never frees because there was no COMMIT transaction and due to this for the particular table resources blocked (means the table is locked to do any operations) and whenever you execute a query against this table the execution time will be infinite or you will get some kind of MSSQL error message for this. So I just wanted to tell this small mistake can make a big issue. Every time make sure in your code or SQL statement that if you are using BEGIN TRANSACTION then you must use the COMMIT TRANSACTION at the end which makes all data modifications performed since the start of the transaction a permanent part of the database and frees the resources held by the connection.

If i am missing something please leave your comments and make this more knowledgable for everyone.

Hello All,

I am back with new topic in SQL Server from last few days i tried lots of stuff in Sql Server 2008 in which once thing is Service Broker but i am not able to do that… but with the grace of god while googling i found lots of  article on this who tells this topic in detail…. so i did all this things and able to do that successfully… i m mentioning all the steps which i did….

In this topic we will see how to configure, send and receive messages using Service broker in SQL Server

Use one of my database and enabled Service Broker

 
USE master
ALTER DATABASE sumitdb
SET ENABLE_BROKER

After that Created Valid Message type as Service Broker required this

 
--Create Message Types for Request and Reply messages
USE sumitdb
 
-- For Request
CREATE MESSAGE TYPE
[//SumitdBTest/SBSample/RequestMessage]
VALIDATION=WELL_FORMED_XML;
 
-- For Reply
CREATE MESSAGE TYPE
[//SumitdBTest/SBSample/ReplyMessage]
VALIDATION=WELL_FORMED_XML;

Create contract for the conversion… as Service Broker required Contract to send or receive message in databases (either single or more then 2 databases)

 
--Create Contract for the Conversation
USE sumitdb
 
CREATE CONTRACT [//SumitdBTest/SBSample/SBContract]
(
[//SumitdBTest/SBSample/RequestMessage]
SENT BY INITIATOR
,[//
 
SumitdBTest/SBSample/ReplyMessage]
SENT BY TARGET
);

Create queues for Message

 
USE sumitdb
 
--Create Queue for the Initiator
CREATE QUEUE SBInitiatorQueue;
 
--Create Queue for the Target
CREATE QUEUE SBTargetQueue;

Create Services for the Communication

 
-CREATE Service FOR the Target AND the Initiator.
 
USE sumitdb
 
--Create Service for the Initiator.
CREATE SERVICE [//SumitdBTest/SBSample/SBInitiatorService]
ON QUEUE SBInitiatorQueue;
 
--Create Service for the Target.
CREATE SERVICE [//SumitdBTest/SBSample/SBTargetService]
ON QUEUE SBTargetQueue
([//SumitdBTest/SBSample/SBContract]);

Sending and Receiving messaging in Service Broker

 
--Sending a Request Message to the Target
USE sumitdb
 
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000)
 
BEGIN TRAN
 
--Determine the Initiator Service, Target Service and the Contract
BEGIN DIALOG @InitDlgHandle
FROM SERVICE
[//SumitdBTest/SBSample/SBInitiatorService]
TO SERVICE
'//SumitdBTest/SBSample/SBTargetService'
ON CONTRACT
[//SumitdBTest/SBSample/SBContract]
WITH ENCRYPTION=OFF;
 
--Prepare the Message
SELECT @RequestMessage = N'<RequestMessage> Send a Test Message </RequestMessage>';
 
--Send the Message
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//SumitdBTest/SBSample/RequestMessage]
(@RequestMessage);
SELECT @RequestMessage AS SentRMessage;
 
COMMIT TRAN

Receiving and Sending Message to Initiator

 
--Receiving a Message and sending a Reply from the Target 
USE sumitdb  
 
DECLARE @TargetDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyMessage VARCHAR(1000)
DECLARE @ReplyMessageName Sysname 
 
BEGIN TRAN; 
 
--Receive message from Initiator
RECEIVE TOP(1)
@TargetDlgHandle=Conversation_Handle
,@ReplyMessage=Message_Body
,@ReplyMessageName=Message_Type_Name
FROM SBTargetQueue; 
 
SELECT @ReplyMessage AS ReceivedRMessage; 
 
-- Confirm and Send a reply
IF @ReplyMessageName=N'//SumitdBTest/SBSample/RequestMessage'
BEGIN
DECLARE @RplyMsg VARCHAR(1000)
SELECT @RplyMsg =N'<RplyMsg> Send a Reply Message to Initiator</RplyMsg>'; 
 
SEND ON CONVERSATION @TargetDlgHandle
MESSAGE TYPE
[//SumitdBTest/SBSample/ReplyMessage]
(@RplyMsg);
END CONVERSATION @TargetDlgHandle;
END 
 
SELECT @RplyMsg AS SentReplyMessage; 
 
COMMIT TRAN;

Receiving a Reply Message from the Target

 
--Receiving Reply Message from the Target.
USE sumitdb
 
DECLARE @InitiatorReplyDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyReceivedMessage VARCHAR(1000)
 
BEGIN TRAN;
 
RECEIVE TOP(1)
@InitiatorReplyDlgHandle=Conversation_Handle
,@ReplyReceivedMessage=Message_Body
FROM SBInitiatorQueue;
 
END CONVERSATION @InitiatorReplyDlgHandle;
 
SELECT @ReplyReceivedMessage AS ReceivedRepliedMessage;
 
COMMIT TRAN;

Please try this at your end and still if you face any problem please let me know will be happy to assist you.

Hello,

Just moving here and there in sql server 2008 looking for something new these days and found some information which may work for some…

when i am checking system database found importance of msdb database…this database contains lots of important information… like when user tried to take backup / restore of database with exact date and time…

right now i m posting small information will come with more details very soon ….

you can just check these four tables in your msdb details you will find all the relative information…

SELECT * FROM backupfile
SELECT * FROM backupset
SELECT * FROM restorefile
SELECT * FROM restorehistory

or you can run this query ….

SELECT  backupfile.logical_name, restorehistory.user_name, backupfile.backup_set_id, restorehistory.restore_date, restorehistory.destination_database_name, restorefile.destination_phys_name, backupset.database_creation_date, backupset.backup_start_date, backupset.backup_size
FROM ((backupfile INNER JOIN backupset ON backupfile.backup_set_id = backupset.backup_set_id) INNER JOIN restorehistory ON backupfile.backup_set_id = restorehistory.backup_set_id) IN<code>NER JOIN restorefile ON restorehistory.restore_history_id = restorefile.restore_history_id
GROUP BY restorehistory.user_name, backupfile.backup_set_id, restorehistory.restore_date, restorehistory.destination_database_name, restorefile.destination_phys_name, backupset.database_creation_date, backupset.backup_start_date, backupset.backup_size, backupfile.logical_name;

if you need any thing in detail regarding the same please let me know will be more then happy to help you...