Monday, July 18, 2016

Physical structure of database in MS SQL Server

The physical structure of the database is divided into the MDF, NDF and LDF. We can say Primary Data files, Secondary data files and Log files respectively.

MDF (Primary Data files)
It store data of tables, stored procedures, views, triggers etc. The file name extension for primary data files is .mdf

NDF (Secondary data files)
An NDF file is a user defined secondary database file of Microsoft SQL Server with an extension .ndf, which store tables, stored procedures, views, triggers data. Moreover, when the size of the database file growing automatically from its specified size or we apply partition table, we can use .ndf file for extra storage and the .ndf file could be stored on a separate disk drive.

LDF (Log files)
Log files in SQL Server databases hold all the log information of transactions. Those information can be later used to recover the database in case of any mismanagement. The file name extension for primary data files is .ldf. 


When we create a database, it creates a data file with .mdf extension and log file with .ldf extension. For example if we create a database named “My_Database” in SQL Server then by default  it will create 2 files named “My_Database.mdf” and “My_Database_Log.ldf”.


SQL Server Architecture

Today we will discuss about Microsoft SQL Server architecture. Basically there are 3 components in SQL Server.
  •  Relational Engine (Query Processor)
  •  Storage Engine
  •  SQL Operating System (SQL OS)

Now we will discuss one by one.

Relational Engine (Query Processor)
When we run a query in window, it manages the execution of query. It mean, first check the syntax of query then request the query output data from Storage Engine and next return the result to query output.

Storage Engine
It manage storage and retrieval of the data on to the storage system (Computer Hard Disk) when we create database, schemas, tables, queries, views and other objects in SQL server.

SQL Operating System (SQL OS)

It is software program that is in host machine (Windows OS) and SQL Server. It controls entire SQL Server behavior and activities. It is a configurable operating system and provide Graphical user Interface (SSMS,BIDS,SSRS.SSIS tool).


What is Microsoft SQL Server ?

Microsoft SQL Server is a software application for relational database management system developed by Microsoft. By this application, we can create database server, database, table, schema and run SQL query to fetch the data from database.
Microsoft has released many versions of SQL Server.
  •     SQL Server 200
  •     SQL Server 2008
  •     SQL Server 2008 R2
  •     SQL Server 2012
  •     SQL Server 2014
  •     SQL Server 2016


Microsoft SQL Server  provide some service given below.
  • Analysis Services (SSAS)
  • Reporting Services (SSRS) 
  • Integration Services (SSIS)
  • SQL ServerManagement Studio (SSMS)
  • SQLCMD
  • Business Intelligence Development Studio (BIDS)

What is SQL ?

SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS). It is designed based on relational algebra (Set theorem, Joins).

SQL consists of a data definition language(DDL), data manipulation language(DML), and Data Control Language(DCL).

The Data Definition Language (DDL) manages table and index structure in database. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements.

The Data Manipulation Language (DML) manages add, update and delete data in database.


The Data Control Language (DCL) authorizes users to access and manipulate data. Its two main statements GRANT and REVOKE.

What is Relational model ?

The relational model (RM) for database management is an approach to  provide details of specific data pulled from various table based on existing relation mentioned between them.

For example, there are 3 tables and they are related by a common column named “Roll No” 

From this model we can get detailed data for Roll No 1 and 2.


A Relational Database is a digital database whose organization is based on the relational model of data.

What is Table ?

A table is a collection of related data held in a structured format within a database. It consists of columns, and rows.


What is Database Management System (DBMS) ?

A database management system is system software for creating and managing databases.
DBMS makes it possible for users to create, read, update and delete data in a database.

A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.

DBMS provider company
1.   MySQL
2.   PostgreSQL
3.   Microsoft SQL Server
4.   Oracle
5.   Sybase
6.   SAP HANA
7.   IBM DB2

Why Database is required ?


  1. Databases provide an efficient way to store the data. All organized collection of various data stored in single location or central storage.
  2. More than one guys can retrieve and analyze data in same time from a single source of truth.

What is Database ?

A database is an organized collection of data. It is the collection of schema, tables, queries, reports, views and other objects.


Example: If we want to make a database for a school, then we have to collect some organized collection of Student Details, Staff Details, Class details, book subject details, Class wise fees details etc. Here all the information are related together.


Monday, July 11, 2016

POWER BI: Microsoft self service Business Intellegence

What is Power BI?
Power BI is a suite of business analytics tools to analyze data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device.

Power bi Component:
It is not only one tool. It is suite tools.
1
        Power Bi Web Portal (3 major building Blocks of Power BI)
1.1  Dataset
1.2  Report
1.3  Dash Board

2   Power Bi Designer
2.1  Power Query
2.2  Power Pivot
2.3  Power View

Power Bi Analysis Services connector.

Look of Power BI Report



SSRS: Download all report RDL files with folder specific from Report Server in single shot

SSRS Report Server does not provides visual option to download all reports at single shot. Report Manager does not support downloading all the report files (.rdl files)  at single shot.That is why we have to face crucial situation while migrate report server to higher version. 

They provided solution for that. By the help of Bulk Copy (BCP) we can able to achieve it.
For that we have to write few lines of SQL code.

Today we will achieve the same by BCP option.

To do that first we have to know some basic about report database. When we configure Reporting Services configuration manager then we have to create report database. This report database contain whole information about reports. If we connect this report server database to SSMS, we can see various tables inside it.





Here one of the most important table is dbo.Catalog. This table contain report information and report XML body. Below picture showing few column of dbo.Catalog.



Type is the one column of  dbo.Catalog Table. Here we can see some number, each number has a specific indication. 

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part

8 = Shared 

To get all the report we have to pass type as 2.

Below is the SQL scripts to download all report RDL files


DECLARE @ReportDetails TABLE
(
  Report_No int,
  ItemId uniqueidentifier,
  Report_name nvarchar(425),
  Report_Path nvarchar(425),
  Folder_Path nvarchar(425)
)

DECLARE @intFlag INT=/*To start with 1 in while loop*/
DECLARE @MaxReportCount INT

Insert into @ReportDetails(Report_No,ItemId,Report_name,Report_Path,Folder_Path)
select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name,[Path],replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path
from [dbo].[Catalog]
   where [Type]=2 /*Load all the reports (Type=2 indicates) from catalog table to table variable*/
select @MaxReportCount = MAX(Report_No) from @ReportDetails /*Get max count to pass as highest value in while loop*/


WHILE (@intFlag <= @MaxReportCount)  /*Declare loop to generate rdl file of all reports one by one*/
BEGIN
     Declare @FolderContainer AS NVARCHAR(200)
     declare @cmd as varchar(MAX) /*Hold xml file for report*/
     declare @Reportqueryout as varchar (240) /*Hold report output path*/
     declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell string for           generating RDL file*/

     Select @FolderContainer='D:\ReportDetails'+Folder_Path from @ReportDetails where Report_No=@intFlag

     Select @cmd =  CONVERT(VARCHAR(MAX),  
           CASE       
             WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')        
             ELSE C.Content        END) 
     FROM  [ReportServerPDReport].[dbo].[Catalog] CL
       CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content)WHERE  CL.ItemID =
               (Select ItemId from @ReportDetails
     where Report_No=@intFlag)

     select * into ##Temp from (select @cmd temp) a /*store XML file to temp table*/
       ------------------------------------
      
        EXEC master.dbo.xp_create_subdir @FolderContainer
   
       ------------------------------------
        select @Reportqueryout='"'+@FolderContainer+'\'+Report_name+'.rdl"'
     from @ReportDetails
     where  Report_No=@intFlag /*Generate output path */

     select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '
                           + 'Select Temp from ##Temp" QUERYOUT '
                           + @Reportqueryout
                           + ' -c -S Localhost\reportinstance  -T''' /* Here Localhost\reportinstance  is the SQL server name with instance*/

     EXEC SP_EXECUTESQL @DynamicQuery
           
     Drop table ##Temp

     SET @intFlag = @intFlag + 1 /*By increasing this go to or fetch next report*/



END

Steps to understand this

1. Open ReportServer database in SSMS (SQL Server management Studio).
2. Select all data from dbo.catalog table where type=2 (2 indicates reports only)
3. Create table variable named @ReportDetails to store few columns form dbo.catalog              table.
4. Create 2 more variable named @intFlag and @MaxReportCount, next assign values.
5. Run while loop from 1 to @MaxReportCount, and convert content to XML file.
6. Push the XML file to ##Temp table.
7. Give path for export RDL file.
8. Create proper syntax of xp_cmdshell and store it into @DynamicQuery variable.
9. Now execute the @DynamicQuery variable.


In case of more than 500 reports in report server, we can use one more way to avoid System.OutOfMemoryException" exception



DECLARE @intFlag INT=/*To start with 1 in while loop*/
DECLARE @MaxReportCount INT

Select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name as Report_name,[Path] as Report_Path,replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path
       into #ReportDetails
       from [dbo].[Catalog]
       where [Type]=2 /*Load all the reports (Type=2 indicates) from catalog table to table variable*/
select @MaxReportCount = MAX(Report_No) from #ReportDetails /*Get max count to pass as highest value in while loop*/

     Declare @FolderContainer AS NVARCHAR(200)
     declare @cmd as varchar(MAX) /*Hold xml file for report*/
     declare @Reportqueryout as varchar (240) /*Hold report output path*/
     declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell string for generating RDL file*/

WHILE (@intFlag <= @MaxReportCount)  /*Declare loop to generate rdl file of all reports one by one*/
BEGIN
    
     Select @FolderContainer='D:\ReportDetails'+Folder_Path from #ReportDetails where Report_No=@intFlag

     Select @cmd =  CONVERT(VARCHAR(MAX),  
           CASE       
             WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')        
             ELSE C.Content        END) 
     FROM  [ReportServerPDReport].[dbo].[Catalog] CL
       CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content)WHERE  CL.ItemID =
               (Select ItemId from #ReportDetails
     where Report_No=@intFlag)

     select * into #Temp from (select @cmd temp) a /*store XML file to temp table*/
       ------------------------------------
      
        EXEC master.dbo.xp_create_subdir @FolderContainer
   
       ------------------------------------
        select @Reportqueryout= '"'+@FolderContainer+'\'+Report_name+'.rdl"'
     from #ReportDetails
     where  Report_No=@intFlag /*Generate output path */

     select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '
                           + 'Select Temp from #Temp" QUERYOUT '
                           + @Reportqueryout
                           + ' -c -S Localhost\reportinstance  -T''' /* Here Localhost\reportinstance  is the SQL server name with instance*/

     EXEC SP_EXECUTESQL @DynamicQuery
           
     Drop table #Temp
        set @FolderContainer=''
        set @cmd=''
        set @Reportqueryout=''
        set @DynamicQuery=''

     SET @intFlag = @intFlag + 1 /*By increasing this go to or fetch next report*/
      

END