The Dynamics of the Microsoft SQL Server Business Intelligence Tools

 

1. SQL Server 2000

a. SSAS (Data warehousing, OLAP, and data mining)

Including the components of Analysis Server, Analysis Manager, Decision Support Objects, Client Components, and the Sample Applications. Selectable in SQL Server 2000 installation

b. DTS

DTS is a node of the SQL Server Enterprise Manager in SQL Server 2000. Part of SQL Server installation.

c. Reporting Services

It was first released in 2004 as an add-on to SQL Server 2000. In order to use Reporting Services you must also have both SQL Server 2000 as the data source and Visual Studio .NET 2003 as the IDE. 

Background: Before 2004, there are a number of third-party reporting products available, the most (in)famous one being Crystal Reports. Starting from 2004, Microsoft released their own reporting solution software, Reporting Services. Reporting Services was an add-on for SQL Server 2000, and has been made available by Microsoft since early 2004. Furthermore, Reporting Services is licensed as part of SQL Server 2000, which means if your company already has a licensed copy of SQL Server, you're in luck - Reporting Services can be installed and run on the same server for no additional license fee.

2. SQL Server 2005, SQL Server 2008, SQL 2008 R2 - BIDS

a. SSAS: BIDS + SSMS

b. SSIS: BIDS + SSMS

c. SSRS: BIDS/Report Builder + SSMS + Report Manager 

Note: 

1. SQL Server 2005 uses Visual Studio 2005 Shell, .NET Framework 2.
2. SQL Server 2008 and 2008 R2 uses Visual Studio 2008 Shell, .NET Framework 3.5

3. SQL Server 2012 – SQL SERVER DATA TOOLS (SSDT), included in SQL Server 2012, replaces BIDS. Also notice there are two versions of SSDT: one for SQL Server and another for Visual Studio.

a. SSAS - Tabular model added

b. SSIS - Project Deployment added

c. SSRS: - No major enhancements 

Note: 

1. SSDT for SQL Server 2012 – Initially Visual Studio 2010 Shell, .NET Framework 4.0. When you create the first BI project, it may force you to download and install SSDT one more time to upgrade the .NET Framework to 4.5. 

(See http://www.mssqltips.com/sqlservertip/2804/introduction-to-sql-server-data-tools/) 

The above article stated that after upgrading the SSDT, the SSDT will be VS 2012 shell. However, in my case, it is still a VS 2010 shell with .NET 4.5. 

2. When I opened Visual Studio 2012 ultimate. Surprisingly, I did not see any BI templates installed. Googled and found this link: http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx. 

After Downloaded SQL Server Data Tools for Visual Studio 2012 and installed it, I now have BI templates in VS 2012 Ultimate. This is SSDT for VS 2012 - adding BI templates to VS 11.0.61030.00 .NET Framework 4.5.51641, independent of the SQL Server Data Tools - adding VS shell with BI templates to SQL Server. 

3. As the file is x86, and my SQL Server 2012 is x64, I have to install it as a new instance to avoid the installation error.

4. SQL Server 2014 – use the same SSDT as that for SQL Server 2012 so far.

SQLCMD -L does not return a list of servers on Windows 8.1

Problem:

I tried to run SQLCMD -L to list all of the SQL Server instances on the Windows 8.1 (x64) PC.

C:\Users\xxx>sqlcmd -L

Surprisingly, it does not work as expected. Instead, it returns:

Servers:
    ;UID:Login ID=?;PWD:Password=?;Trusted_Connection:Use Integrated Security=?;*APP:AppName=?;*WSID:WorkStation ID=?;

But when I ran the same command on the Windows 7(x64) PC, it correctly lists all of the SQL Server instances on the machine.

I tried to do the same thing with the following PowerShell command, it works fine.

(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

Update:

It works fine on Windows 10 Professional. See below:

Reason:

???

Strange Query Results on Decimal Data Operations - This is why!

--The following query returns 0.500000. Do you know why?

SELECT 50 / CAST(100 AS decimal(3))

See the article below for details:

http://technet.microsoft.com/en-us/library/ms190476.aspx

Copied here:

Precision, Scale, and Length

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

In SQL Server, the default maximum precision of numeric and decimal data types is 38. In earlier versions of SQL Server, the default maximum is 28.

Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.

When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.

When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.
When two expressions of the same data type but different lengths are compared by using UNION, EXCEPT, or INTERSECT, the resulting length is the maximum length of the two expressions.

The precision and scale of the numeric data types besides decimal are fixed. If an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type. If an operator has two expressions with different numeric data types, the rules of data type precedence define the data type of the result. The result has the precision and scale defined for its data type.

The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either of the following is true:

• Both expressions are decimal.
• One expression is decimal and the other is a data type with a lower precedence than decimal.

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression. 

Operation	Result precision	Result scale *
e1 + e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 - e2	max(s1, s2) + max(p1-s1, p2-s2) + 1	max(s1, s2)
e1 * e2	p1 + p2 + 1	s1 + s2
e1 / e2	p1 - s1 + s2 + max(6, s1 + p2 + 1)	max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2	max(s1, s2) + max(p1-s1, p2-s2)	max(s1, s2)
e1 % e2	min(p1-s1, p2 -s2) + max( s1,s2 )	max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

In our case, e1 is int 50, so p1 = 10, s1=0; e2 is CAST(100 AS decimal(3)) - returning 100, so p2=3,s2=0

Hence, the precision of e1/e2 is p1 - s1 + s2 + max(6, s1 + p2 + 1) = 10-0+0+max(6, 0+3+1)=16; the scale is max (6, s1+p2+1) = max(6, 0+3+1)=6. That's why there are 6 decimal points.

Linked Reports, Subreports, Drillthrough Reports, Drilldown Reports, and Nested Data Regions

1. Linked Reports

 1.1. What is a linked report?

A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

You can create a linked report when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.

Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings. 

1.2. How to create a linked report

• In Report Manager, navigate to the folder containing the report that you want to link to, and then open the options menu can click Create Linked Report.

• Type a name for the new linked report. Optionally type a description.

• To select a different folder for the report, click Change Location. Click the folder you want to use, or type the folder name in the Location box. Click OK.  If you do not select a different folder, the linked report is created in the current folder (where the report it is based on is stored).

• Click OK.  The linked report opens.

• A linked report's icon differs from other items managed by a report server. The following icon indicates a linked report: 

1.3. Example

• Step 1 – This is the original ‘Company Sales’ report in Adventure Works 2008 Sample Reports

• Step 2 – Click the Properties tab to see the ‘Create Linked Report’ Option

• Step 3 – After click the ‘Create Linked Report’, you can customize the liked report such as Name, Description, report location

• Step 4 – After click the ‘Create Linked Report’, you can also change the parameters, execution options, maintenance of report history, or report security. The chart below shows the parameters only

• Step 5 – You can see the linked report is shown in the report folder as we keep the linked report on the same folder of the original report.

 2.Subreports

 2.1. What is a subreport?

A subreport is a report item that displays another report inside the body of a main report. Conceptually, a subreport in a report is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The report that is displayed as the subreport is stored on a report server, usually in the same folder as the parent report. You can design the parent report to pass parameters to the subreport. A subreport can be repeated within data regions, using a parameter to filter data in each instance of the subreport.

2.2. Example

In this illustration, the contact information displayed in the main Sales Order report actually comes from a Contacts subreport.

Note: You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com

2.3. Comparing Subreports and Nested Data Regions

If you're thinking of using subreports to display separate groups of data, consider using data regions, such as tables, matrices, and charts, instead. Reports with data regions only may perform better than reports that include subreports.

Use data regions to nest groups of data from the same data source within a single data region. Use subreports to nest groups of data from different data sources within a single data region, reuse a subreport in multiple parent reports, or display a standalone report inside of another report. For example, you can create a "briefing book" by placing multiple subreports inside the body of another report.

Data regions provide much of the same functionality and flexibility as subreports, but with better performance. Because the report server processes each instance of a subreport as a separate report, performance can be impacted. For more information, see Nested Data Regions (Report Builder and SSRS).

3. Drillthrough Reports

 3.1. What is a drillthrough report?

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, in this illustration, the sales summary report lists sales orders and totals. When a user clicks an order number in the summary list, another report opens that contains details about the order. 

3.2. Example

The data in the drillthrough report is not retrieved until the user clicks the link in the main report that opens the drillthrough report. If the data for the main report and the drillthrough report must be retrieved at the same time, consider using a subreport.

4. Drilldown reports 

4.1. What is a drilldown report?

A drilldown report is a layout design that at first hides complexity and enables the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. The main reason for hiding items is to provide a report that shows summary data but enables a user to drill down into detail data. For reports that use large amounts of data, consider drillthrough reports instead. 

4.2. Example:

In this illustration, the user clicks the plus signs (+) in the report to show detail data.

For example, you can initially hide all the rows except the outer group summary row for a table with row groups. For each inner group (including the details group), add a toggle to the grouping cell of the containing group. When the report is rendered, the user can click the text box to expand and collapse the detail data. 

5. Which one to use: Drillthrough, Drilldown, Subreports, or Nested Data Regions? 

5.1. Comparisons

You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drilldown action. You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix. You can display the data in a subreport that is completely contained within a main report. Or, you can put the detail data in drillthrough reports, separate reports that are displayed when a user clicks a link.

A. Drillthrough report
B. Subreport
C. Nested data regions
D. Drilldown action

All of these have commonalities, but they serve different purposes and have different features. Two of them, drillthrough reports and subreports, are actually separate reports. Nesting is a means of putting one data region inside another data region. Drilldown is an action you can apply to any report item to hide and show other report items. They all are ways that you can organize and display data to help your users understand your report better.

5.2. Summary of Characteristics

This table summarizes these different traits. Details are in separate sections later in this topic. Drilldown isn't included in these comparisons because you can apply its showing and hiding action to any report item.

Trait	Subreport	Drillthroug	Nested
Uses dataset of main report	Same or different	Same or different	Same
Retrieves data	Data retrieved at the same time as main report	Data retrieved one drillthrough report at a time	Data retrieved all at the same time as main report
Is processed and rendered	With the main report	When link is clicked	With the main report.
Performs	Slower (but retrieves all data with main report)	Faster (but does not retrieve all data with main report)	Faster (and retrieves all data with main report)
Uses parameters	Yes	Yes	No
Can be reused	As report, or subreport or drillthrough report in other reports	As report, or subreport or drillthrough report in other reports	Cannot be reused.
Is located	External to main report, same or different report server	External to main report, same report server	Internal to main report
Is displayed	In the main report	In a different report	In the main report

Restore commands in backup sets

1.RESTORE FILELISTONLY

Returns a result set containing a list of the database and log files contained in the backup set.

--The following example returns the information from a backup device named AdventureWorksBackups. The example uses the FILE option to specify the second backup set on the device.

RESTORE FILELISTONLY FROM AdventureWorksBackups

   WITH FILE=2;

GO

2.RESTORE HEADERONLY

Returns a result set containing all the backup header information for all backup sets on a particular backup device.

/*The following example returns the information in the header for the disk file C:\AdventureWorks-FullBackup.bak.

UNLOAD - Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.

NOUNLOAD - Specifies that after the RESTORE operation the tape will remain loaded on the tape drive.

*/

RESTORE HEADERONLY

FROM DISK = N'C:\AdventureWorks-FullBackup.bak'

WITH NOUNLOAD;

GO 

3.RESTORE LABELONLY

The RESTORE LABELONLY option allows you to see the backup media information for the backup device.  So if a backup device, such as a backup file, has multiple backups you will only get one record back that gives you information about the media set, such as the software that was used to create the backup, the date the media was created, etc...

This information can only be returned using T-SQL there is not a way to get this information from SQL Server Management Studio.

The RESTORE LABELONLY option can be simply issued as follows for a backup that exists on disk.

--Get labelonly information from a backup file

RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'

GO

4.RESTORE VERIFYONLY

Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks.

If the backup is valid, the SQL Server Database Engine returns a success message.

/*Check a backup file on disk

The following command will check the backup file and return a message of whether the file is valid or not. If it is not valid, this means the file is not going to be usable for a restore and a new backup should be taken. One thing to note is that if there are multiple backups in a file, this only checks the first file.*/

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK

GO

/*Check a backup file on disk for a particular backup

--This command will check the second backup in this backup file. To check the contents in a backup you can use RESTORE HEADERONLY and use the Position column to specify the FILE number.*/

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2

GO

5.RESTORE REWINDONLY

Rewinds and closes specified tape devices that were left open by BACKUP or RESTORE statements executed with the NOREWIND option. This command is supported only for tape devices.

RESTORE REWINDONLY is an alternative to RESTORE LABELONLY FROM TAPE = <name> WITH REWIND. You can get a list of opened tape drives from the sys.dm_io_backup_tapes dynamic management view.

--To close the device and unload the tape, use this:

RESTORE REWINDONLY FROM '\\.\tape0' WITH UNLOAD

--To close the device and not unload the tape, use:

RESTORE REWINDONLY FROM '\\.\tape0' WITH NOUNLOAD