Parv The IT Geek

What is Business Intelligence ( BI )?

What is Business Intelligence ( BI )? Business Intelligence Development Studio ( BIDS )? SQL Server Reporting Services ( SSRS )? SQL Server Integration Services ( SSIS )? SQL Server Analysis Services ( SSAS )? What is Business Intelligence ( BI )?

Business intelligence is simply using software to manage large amounts of data to get meaningful and actionable results. It is also an growing field within IT where people are choosing to specialise in.

Software associated with BI includes SQL Server Reporting Services, SQL Server Analysis Services, Biztalk, Business Objects, Cognos…..

Business intelligence is used to make smarter marketing, identify new markets, efficiency and cost savings.

What is Business Intelligence Development Studio ( BIDS )?

Business Intelligence Development Studio ( BIDS ) is a custom version of Visual Studio which is supplied with SQL Server where the development of Analysis Services databases and mining models, Integration Services packages and Reporting Services reports is completed.

If BIDS is installed on a system that already has a version Visual Studio installed then these additional project types are added to the existing installed Visual Studio.

What is SQL Server Reporting Services ( SSRS )?

SQL Server Reporting Services is a comprehensive reporting feature of SQL Server where reports can be created, deployed and managed across an Enterprise scale environment.

SSRS is used by many firms to replace Crystal Reports, Excel and Access as it has a far greater range of abilities and features as well as far greater performance.

What is SQL Server Integration Services ( SSIS )?

SQL Server Integration Services is often described as an ETL tool as its function is to Extract, Transform and Load data (ETL). It is often used to perform repetitive tasks as each process can be saved as a SSIS package and can be scheduled to run automatically.

SSIS packages can be made simply via the GUI, BIDS ( drag and drop ) or enhanced using T-SQL.

What is SQL Server Analysis Services ( SSAS )?

SQL Server Analysis Services is made up of two distinct part which an Online Analytical Processing (OLAP) and Data mining.

OLAP allows the building of multidimensional structures which are known as cubes. Using these structures businesses can then analyse the data to solve problems. Cubes are queried using Multidimensional Expressions (MDX) rather than T-SQL.

Data mining allows the analysis of huge amounts of data and is used to find patterns and insight into information which is not as seemingly obvious as that found in Cubes.

Originally published at https://parvtheitgeek.com on January 14, 2014.

How can I find out what SQL Server version is running? — Parv The IT Geek

I was asked by someone which SQL Server version a server was running and was surprised when my DBA colleague said to run a stored procedure I had never heard of. I had not thought that there would be more than one way as the usual method I used below was so simple I didn’t think that there would be a need for another method. I then did a deep dive of all the different ways I could find out the version of SQL Server I was running and have included them below.

Method 1

Go to Help > About

Method 2

Use the following script .

SELECT @@VERSION

That is the script which I would use and I think is pretty much the most commonly used method within the SQL Server community.

Method 3

EXEC sp_MSgetversion

Method 4

EXEC xp_msver

Method 5

SELECT SERVERPROPERTY(‘productversion’),

SERVERPROPERTY (‘productlevel’),

SERVERPROPERTY (‘edition’)

Method 6

Within the SQL logs the current version will be listed when the log was last cycled.

Originally published at https://parvtheitgeek.com on January 13, 2014.

What is Denormalization? — Parv The IT Geek

This is simply the process of combining smaller tables to make larger tables. This can be used to address problems with performance or scalability.

This is usually caused by tables being stored in separate files on disks and when databases are being queried there is a requirement for each several files to be accessed according to the joins thus slowing the query down.

Originally published at https://parvtheitgeek.com on January 12, 2014.

What is Normalization? — Parv The IT Geek

I would simply describe this as the process of making big tables into smaller tables.

The proper definition is that database normalization is a method of reorganising data within tables to reduce the level of dependency. This helps to isolate data so that insertion, deletions and updates in a field can be made into a single table. The relationships between the tables would then propagate this throughout the database.

The goal of normalization is to reduce the amount of data within a table and also to make the data within each table make more sense.

Originally published at https://parvtheitgeek.com on January 11, 2014.

What are DDL , DML, DCL and TCL ? — Parv The IT Geek

These are used to create and manage the structure of objects within a database. For example, CREATE, DROP, ALTER commands ON tables, views, indexes, keys…

DML — Data Manipulation Language

These are the commands used to actually query, modify and delete data within a database. For example SELECT, INSERT, UPDATE, DELETE.

DCL — Data Control Language

These are used to control access to databases and objects. For example GRANT, REVOKE, DENY

TCL — Transactional Control Language

These are used to control data modifications made my transactions. For example ROLLBACK TRANSACTION, BEGIN TRANSACTION, COMMIT TRANSACTION

Originally published at https://parvtheitgeek.com on January 10, 2014.

What is CRUD (Create, read, update and delete ) — Parv The IT Geek

CRUD is used to describe the majority of commands which are used within databases. Some database professionals I know have used the term BREAD instead which stands for for “Browse, Read, Edit, Add, Delete”.

Create

Basically this means adding something new

This can be used to add new data or objects….

Read

This means to retrieve data

This would be the majority of the SELECT queries

Update

This is the changing of existing data

Delete

This is the removal data

Originally published at https://parvtheitgeek.com on January 9, 2014.

What is ACID ( Atomicity, Consistency, Isolation, Durability)? — Parv The IT Geek

Atomicity — this basically means the transaction works or doesn’t and is sometimes called the “all or nothing” rule. I always liken it to an Atomic bomb because they either work or fail. A failed transaction would then enter a state of rollback.

Consistency — this means only valid data is written to the database. This means if the constraints, keys….. are violated the data would not be committed.

Isolation — this means that multiple transactions should not impact other transactions which are occurring simultaneously.

Durability — this means when a transaction is successful all the pending changes are applied to the database.

Originally published at https://parvtheitgeek.com on January 8, 2014.

Create a new column with an Expression — Parv The IT Geek

Sometimes there may be a requirement to create a new column based upon existing columns with a minor manipulation.

For example in the AdventureWorks database the table TransactionHistoryArchive shows the quantity and the actual cost of each unit but not the total value of the order.

That would be achieved by multiplying the Quantity and AcutalCost columns.

Current results from the table are below.

SELECT [TransactionID] ,[ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionDate] ,[TransactionType] ,[Quantity] ,[ActualCost] ,[ModifiedDate] 
FROM [AdventureWorks].[Production].[TransactionHistoryArchive]

However we need to multiply Quantity * ActualCost and call the column TotalOrderValue.

SELECT [TransactionID] ,[ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionDate] ,[TransactionType] ,[Quantity] ,[ActualCost] ,[ModifiedDate] ,[Quantity] * [ActualCost] AS TotalOrderValue 
FROM [AdventureWorks].[Production].[TransactionHistoryArchive]