Tuesday, July 23, 2013

SQL Server System Databases - Master, Model, Temp ,MS DB and Resource

You may have observed after installing SQL Server that some databases are automatically created under system databases node. These are five system databases as master, model, msdb, tempdb and resource. Resource database is hidden and cannot be seen in this node rest four databases are visible in the node. If you have observed, you will not get delete option when you right click on it so basically you will not be able to drop system databases as they contain metadata information.

              






Master Database-  

The master database is primary system database of SQL Server. It keeps and manages all system level information i.e. system information's which are defined at instance level such as system configurations, logon accounts, linked server, all other databases and their database files etc. Regularly you have to take master database backup because if it is corrupted then you won’t be able to start SQL Server and you have to follow different strategy to restart it (SQL Server cannot start if the master database is unavailable). Also avoid to create user objects in master database.


Model Database

The model database acts as a template for all the databases which have been created on particular instance. If you create any object on model database and if you create any user database on that instance then those objects will also be created in new user database. So you can use model database in such scenarios where you have to repeat particular objects in all newly created database. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.


Tempdb Database- 

As the name suggests, tempdb holds temporary data like local temporary tables, global temporary tables, temporary table variables and cursors, internal objects created by database engine for sorting, online index activities etc. If you have observed in SSMS there is no option for tempdb backup and restore so you cannot backup or restore this database as this database is re-created every time SQL Server starts.

MSDB Database- 

SQL Server agent information is managed by msdb database, all jobs and details, different configured alerts and operators etc. are managed by it. For example, if you perform a backup of database then all operation informations and events like backup type, backup time, name of the source performing the backup and device where performing backups etc. are stored in msdb database. By default msdb is in simple recovery model but in some scenarios it is recommended to use full recovery model.

Resource database- 

The resource database is hidden and not visible in system database list or node. It is read-only database and contains system objects (such as sys.objects) linked to sys schema in all the databases. Prior to SQL Server 2005 all system stored procedures, system functions and system views were defined and linked in master database but later all these have been defined in resource database. Every instance of SQL Server is associated with only one resource data file. The default path for resource database  is <drive name>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\ as in [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn].SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Distribution Database: 

The distribution database stores metadata and history data for all types of replication, and transactions for transactional replication.

In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher.


Highlights of SQL Server by Version








Editions of SQL Server

Editions of SQL Server: Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users.

Mainstream Editions

1)Parallel Computing Edition (PCE)
2)Datacenter Edition
3)Enterprise Edition
4)Standard Edition
5)Workgroup Edition
6)Web Edition
7)Business Intelligence (2012 New Edition)
8)Express Edition


Specialized Editions

1)Azure
2)Compact Edition
3)Developer Edition
4)Embedded Edition
5)Evaluation Edition
6)Fast Track
7)Parallel Data Warehouse (PDW)


Standard Edition:

Designed for small and medium businesses, the Standard edition supports up to 4 CPUs and 2TB of RAM, with unlimited database size. lacks many of the enterprise-oriented features from the Enterprise edition. It will be priced at $7,499 per processor or $1,849 per server with five CALs.


Web Edition:

This edition is designed to be run by web hosting providers. It provides support for up to 4 CPUs, 2TB of RAM, and unlimited database size. It supports Reporting Services priced at $15 per processor per month.


Workgroup Edition: 

Workgroup edition is designed to provide basic relational database services for departments and branch offices. It supports a maximum of 2 CPUs, 4GB of RAM, and unlimited database size. Its only BI feature is Reporting Services. It's priced at $3,899 or $739 per server including five CALs.


Parallel Computing Edition (PCE):

It will utilize a Massively Parallel Processing (MPP) scale-out architecture to support data warehousing ranging from 10TB to 1+ PB. The Parallel Computing Edition is priced at $57,489 per processor.


Data Center Edition:

Support from up to 256 logical processors, it's designed to address the highest levels of scalability. The Datacenter edition has no memory limitation and offers support for more than 25 instances. It is approximately priced at $57,489 per processor.


Enterprise/Developer Edition: 

The Enterprise edition is the one that’s targeted toward most businesses. With the advent of the new Datacenter edition, the Enterprise edition will be limited to support for 64 logical processors and 25 instances, which really isn’t much of a change. It will be priced at $28,749 per processor or $13,969 per server with 25 CALs. The Developer edition shares the same feature set and is licensed at $50 per developer.


Express Edition: 

Targeted toward developers and ISVs, SQL Server Express Edition comes in three versions.
1)SQL Server 2008 R2 Express provides relational database services
2)SQL Server 2008 R2 Express with Tools Edition adds SSMS
3)SQL Server 2008 R2 Express with Advanced Services adds SSMS and a local instance of Reporting Services.

All SQL Server 2008 R2 Express editions will continue to be free downloads

Evaluation Edition:

SQL Server Evaluation Edition, also known as the Trial Edition, has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop.

Versions of SQL Server





Introduction - SQL Server

        SQL stands for Structured Query Language and pronounced as 'Sequel' which is used against RDBMS to manage data. SQL is a computer language for accessing databases.Database is accessed using SQL statement, which is an ANSI (American National Standards Institute) standard”.

SQL is used to create and query databases i.e. creating, storing, accessing and manipulating the data. Due to its loose structure and flexibility, SQL is considered as a handy tool for building dynamic web applications.

Features of SQL :-

1. SQL stands for Structured Query Language and is used to communicate with databases.
2. SQL is NOT case sensitive i.e. SELECT is the same as select.
3. SQL is efficient, easy to use and learn.
4. SQL is used to create new databases.
5. SQL is used to create new tables in a database.
6. SQL is used to create views, stored procedures and functions in a database.
7. SQL is used to drop tables and databases.
8. SQL is used to execute queries and retrieve data from a database.
9. SQL is used to insert, delete and update records in a database.
10. SQL is used to set permissions on tables, procedures, and views.



SQL (pronounced "ess-que-el" (sequel)) stands for Structured Query Language. 
SQL is used to communicate with a database. 
SQL is a standard language for accessing and manipulating databases.

 Database is accessed using SQL statement, which is an ANSI (American National Standards Institute) standard”. SQL is used to create and query databases i.e. creating, storing, accessing and manipulating the data. 

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. 

SQL is an ANSI (American National Standards Institute) standard. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.


Keep in Mind That...


SQL is NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

The Evolution of SQL Server

In 1985, Microsoft and IBM announced "a long-term joint development agreement for development of operating systems and other systems software products.“

This announcement was the beginning of OS/2, a successor to the Microsoft MS-DOS operating system. OS/2 would be a more complete and robust operating system.

OS/2 was formally announced in April 1987, with shipment promised by the end of the year.

But shortly after the joint declaration, IBM announced a special higher-end version of OS/2 called OS/2 Extended Edition. This more powerful version would include the base OS/2 operating system plus an SQL RDBMS called OS/2 Database Manager (now famous as DB/2)

But if IBM could offer a more complete OS/2 solution, who would buy Microsoft OS/2? Clearly, Microsoft needed to come up with an answer to this question.
Microsoft needed a database management system (DBMS) product, hence Microsoft turned to Sybase, Inc., an upstart in the DBMS market. The deal between the two companies was a win-win situation.

In 1988, a new product was announced with the somewhat clumsy name Ashton-Tate/Microsoft SQL Server. This new product would be a port of Sybase DataServer to OS/2, marketed by both Ashton-Tate and Microsoft.

Ashton-Tate, Microsoft, and Sybase worked together to debut SQL Server on OS/2. (This was the first use of the name SQL Server). Today, Sybase's database server is known as Sybase Adaptive Server

The following timeline summarizes the development history of SQL Server:

- 1987 Sybase releases SQL Server for UNIX.

- 1988 Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.

- 1989 Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.

- 1990 SQL Server 1.1 is released with support for Windows 3.0 clients.
           
             Aston-Tate drops out of SQL Server development.

- 1991 Microsoft and IBM end joint development of OS/2.
- 1992 Microsoft SQL Server 4.2 for 16-bit OS/2 1.3 is released.

- 1992 Microsoft and Sybase port SQL Server to Windows NT.

- 1993 Windows NT 3.1 is released.

- 1993 Microsoft and Sybase release version 4.2 of SQL Server for Windows NT.

- 1994 Microsoft and Sybase co-development of SQL Server officially ends.

           Microsoft continues to develop the Windows version of SQL Server.

          Sybase continues to develop the UNIX version of SQL Server.

- 1995 Microsoft releases version 6.0 of SQL Server.

- 1996 Microsoft releases version 6.5 of SQL Server.

- 1998 Microsoft releases version 7.0 of SQL Server.

-2000 Microsoft releases SQL Server 2000.

-2001 Microsoft releases XML for SQL Server Web Release 1 (download).

-2002 Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).

-2002 Microsoft releases SQLXML 3.0.

- 2005 Microsoft releases SQL Server 2005 on November 7th, 2005.

-2008 Microsoft releases SQL Server 2008

-2011 Code Name Denali Announced

-2012 SQL Server 2012 Officially Released RTM is Ready

-2014 SQL Server 2014 (CodeName Hekaton) Officially Released RTM is Ready



History of Databases and Database Management System

Having data is not a problem but managing data is a big problem. File System failed to manage data in many ways that is why RDBMS was born.

We will understand what are the advantage of RDBMS over File System.

Lets start from very basic, assume you are a small organization and your company is maintaining data in a excel file. What Problem you may face while maintaining data in excel file?

Problem 1 (Size)- Excel can store maximum 1048576 rows what if you want to store lot more rows?

Problem 2 (Data Modification)-You can share your data file to multiple user, but what if 200 users are accessing the file and few of them want to modify the data at same point of time.

Problem 3 (Data Accuracy)-When you enter data manually into the excel file, you may press wrong key and end up by entering wrong data.It doesn't check your data, because no data constraints.

Problem 4 (Security)-You data file contains some sensitive information and you don't to share that information among other users. what if you want to share some part of data?

Problem 5 (Data Redundancy)-You may enter same record couple of time, It will allow duplicates rows.

Problem 6 (Data Importance)-Every single row of data is very very important, in system failure you may loss your important data.System failure can corrupt your data file.

To over come from the above problems RDBMS was born.



Data Vs Information


Data:

Data is collection of raw facts or unorganized form (such as alphabets, numbers, or symbols)

Information (Collection of facts): Systematic and meaningful form of data. Information helps human beings in their decision making.




What are Flat Files?

             During earlier days of computing what we had were File Systems i.e. Flat files. Data was stored in flat files (Ex: Editors, Notepad, Text Pad etc.)




Defects:

1)No Proper Formatting of Text
2)No Search capabilities
3)Redundancy


What are Spread Sheets?

              A spreadsheet is a program that stores data in a grid of rows and columns allowing for calculations and the generation of graphs. Spreadsheets are easy to create and maintain. Some of the most commonly used are Microsoft Excel. You can define mathematical formulas and statistical calculations for individual data cells. Excel is better at a lot of things—displaying charts, showing PivotTables, displaying different types of data on the same worksheet.




Defects:

1)Redundancy.
2)Multi-User Access
3)Security


Database:

A database is an integrated collection of logically-related records or files consolidated into a common pool that provides data for one or more multiple uses.

A database is a systematic collection of information that is organized so that it can be easily searched, accessed, managed and updated.

Databases consist of software-based "containers" that are structured to collect and store information so users can retrieve, add, update or remove such information in an automatic fashion.

   A database concept can be understand and explained as a collection of information (i.e. meaningful data) or in other words it is a set of inter-related data. Database provides data storage and data retrieval functionality and makes the management of data an easy process. There is countless number of examples for usage of database.

Let’s take some examples to understand database.
  • Your bank account is stored in the database of your bank to maintain your data such as name, contact number, address, transaction history, account summary and lot more. 
  • We all use Facebook to communicate with our friends and get in touch with the outer world. Facebook also stores data of their members such as their friends, activities, messages, etc in its database. 
Shape of Database:




DBMS (Database Management System):

DBMS stands for Database Management System.A set of programs to access the interrelated the data.
DBMS is a Software system for creating, organizing and managing the database.DBMS contains information about a particular enterprise.

It provides an environment to the user to perform operations on the database for creation, Insertion, deletion, updating and retrieval of data.

It is software that enables computer to maintain data or in other words it is a collection of programs which allows its users to access database, query database to manipulate data, representation of the output data.

A Database Management System (DBMS) is a set of computer programs that controls the creation, maintenance, and the use of a database. It allows organizations to place control of database development in the hands of Database Administrations (DBA's) and other specialists.

A DBMS is a system software package that helps the use of integrated collection of data records and files known as databases. It allows different user application programs to easily access the same database.

Characteristics of DBMS are as follows :-
  • It allows many applications to share the data. 
  • It helps in controlling data redundancy. 
  • The correct, consistent, and inter relationship of data with respect to the application which uses the data leads to the maintenance of integrity of data. 
  • It supports for transaction control and recovery such as update takes place physically after a logical transaction is complete. 
  • It provides tools by which DBA can ensure about the security of the database. 
DBMS may use any of a variety of database models, such as the Hierarchical model, network model or relational model.

Examples of Database Management Systems – Microsoft Access, MySQL, Microsoft SQL Server, Oracle, File-Maker Pro and lot more.




Advantages of DBMS:

  • Controlling Data Redundancy: Data is recorded into only one place in the database and it is not duplicated.
  • Data Consistency: Data item appears only once, and the updated value is immediately available to all users.
  • Control over concurrency: In a computer-file based system in updating, one may overwrite the values recorded by the other.
  • Backup and Recovery procedures: Automatically create the backup of data and restore data if required.
  • Data Independence: Separation of data structure of database from application program that uses the data is called independence.

Disadvantages of DBMS:

  • Cost of Hardware and Software: Processor with high speed of data processing and memory of large size is required.
  • Cost of Data Conversion: Very difficult and costly method to convert data of data file into database.
  • Database Damage: All data is integrated into a single database. If database is damaged due to electrical failure or database is corrupted on the storage media, then your valuable data may be lost forever.

Evolution has started with: Flat files – Word Processors – Spreadsheets- Simple Databases – DBMS/RDBMS





Data Models:
                    A data model is a conceptual representation of the data structures that are required by a database. The data structures include the data objects, the associations between data objects, and the rules which govern operations on the objects. As the name implies, the data model focuses on what data is required and how it should be organized rather than what operations will be performed on the data. To use a common analogy, the data model is equivalent to an architect’s building plans or Engineers build a model of a car to work out any details before putting it into production.

A data model is independent of hardware or software constraints. Rather than try to represent the data as a database would see it, the data model focuses on representing the data as the user sees it in the “real world”. It serves as a bridge between the concepts that make up real-world events and processes and the physical representation of those concepts in a database.


  • Describes structure of database.
  • Aim to support the development of information systems by providing the definition and format of data.

Classification:

 High- Level Model (Entity, Attribute, Relationship)
               Ensures data requirement of the users. Not concerned with representation, but it’s a conceptual form.

 Representation Model (Hierarchical, Relational, Network)
                Represent of data stored inside a database. Describes the physical structure of database.

Low-Level Model


Different Data Models :-




Hierarchical Database Model:

  • The hierarchical model was developed by IBM in 1968.
  • The data is organize in a tree structure (Parent- Child relation ship).
  • Each box is called a ‘Node’. A nodes represent a record type.
  • Since the data is organized in a tree structure, the parent node has the links to its child nodes.
  • If we want to search a record, we have to traverse the tree from the root through all its parent nodes to reach the specific record. Thus, searching for a record is very time consuming.
  • SYSTEM2000 is an example of hierarchical database.
  • Its suited for One-to-many relation ship but difficult to implement many-to-many relation ship.






Relational Database Model (RDBMS)

                        RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
  • The Relational Model is most common model.
  • Developed in 1970 by E.F.Codd, it became commercial in the 80’s.
  • E. F. Codd laid down 12 rules (known as Codd's 12 rules) that outline the minimum functionality of a RDBMS. A RDBMS must comply with at least 6 of the rules.
  • organizes data in the form of independent tables (consisting of rows and columns) that are related to each other. 




Who is the Father of RDBMS

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

Edgar Frank "Ted" Codd (August 23, 1923 – April 18, 2003) was a British computer scientist who, while working for IBM, invented the relational model for database management

E. F. Codd introduced the term “Relational Model” in his seminal paper "A Relational Model of Data for Large Shared Data Banks", published in 1970. In this paper and later papers he defined what he meant by relational. One well-known definition of what constitutes a relational database system is Codd's 12 rules.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

Codd's Rule

E.F Codd was a Computer Scientist who invented Relational model for Database management. Based on relational model, Relation database was created. Codd proposed 13 rules popularly known as Codd's 12 rules to test DBMS's concept against his relational model. Codd's rule actually define what quality a DBMS requires in order to become a Relational Database Management System(RDBMS). Till now, there is hardly any commercial product that follows all the 13 Codd's rules. Even Oracle follows only eight and half out(8.5) of 13. The Codd's 12 rules are as follows.

Rule zero

This rule states that for a system to qualify as an RDBMS, it must be able to manage database entirely through the relational capabilities.

Rule 1 : Information rule

All information(including metadeta) is to be represented as stored data in cells of tables. The rows and columns have to be strictly unordered.

Rule 2 : Guaranteed Access

Each unique piece of data(atomic value) should be accessible by : Table Name + primary key(Row) + Attribute(column).

NOTE : Ability to directly access via POINTER is a violation of this rule.

Rule 3 : Systematic treatment of NULL

Null has several meanings, it can mean missing data, not applicable or no value. It should be handled consistently. Primary key must not be null. Expression on NULL must give null.
Rule 4 : Active Online Catalog

Database dictionary(catalog) must have description of Database. Catalog to be governed by same rule as rest of the database. The same query language to be used on catalog as on application database.

Rule 5 : Powerful language

One well defined language must be there to provide all manners of access to data. Example: SQL. If a file supporting table can be accessed by any manner except SQL interface, then its a violation to this rule.
Rule 6 : View Updation rule

All view that are theoretically updatable should be updatable by the system.

Rule 7 : Relational Level Operation

There must be Insert, Delete, Update operations at each level of relations. Set operation like Union, Intersection and minus should also be supported.

Rule 8 : Physical Data Independence

The physical storage of data should not matter to the system. If say, some file supporting table were renamed or moved from one disk to another, it should not effect the application.

Rule 9 : Logical Data Independence

If there is change in the logical structure(table structures) of the database the user view of data should not change. Say, if a table is split into two tables, a new view should give result as the join of the two tables. This rule is most difficult to satisfy.
Rule 10 : Integrity Independence

The database should be able to conforce its own integrity rather than using other programs. Key and Check constraints, trigger etc should be stored in Data Dictionary. This also make RDBMS independent of front-end.

Rule 11 : Distribution Independence

A database should work properly regardless of its distribution across a network. This lays foundation of distributed database.

Rule 12 : Non subversion rule

If low level access is allowed to a system it should not be able to subvert or bypass integrity rule to change data. This can be achieved by some sort of looking or encryption.


Data Integrity

Data integrity ensures quality of data. It helps keep the data unchanged and unique. Data types ensure that the data accepted by the column is restricted to the type specified. For e.g. an integer data type cannot accept strings

The following categories of the data integrity exist with each RDBMS:

Entity Integrity: There are no duplicate rows in a table.

Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: Rows cannot be deleted, which are used by other records.

User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.


Terminologies:


Data Values: Alphanumeric raw data.


Column (Field):

Every table is broken up into smaller entities called Column. A column is a vertical entity in a table that contains all information associated with a specific field in a table.


Record or Row:

A record, also called a row of data, is each individual entry that exists in a table. A record is a horizontal entity in a table.


Table:

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.


Key:

Identifier (uniquely identifies a row in the table. It can be value of single or multiple column)


Users:

People who interact with the database. E.g. Application Programmers, End Users and DBA’s.


Software:

Lies between the stored data and Users. E.g. DBMS, Application Software, User Interface.


Hardware:

Physical device on which database resides. E.g.: Computers, Hard Disks


Network Database model
  • The Network model supports many-to many relations.
  • RDM (Raima Database Manager) Server is an example of a database management system that implements the network model.
  • Record relationship in the network model is implemented by using pointers.
  • Record relationship implementation is very complex since pointers are used. It supports many-to-many relationships and simplified searching of records since a record has many access paths.
  • DBTG Codasyl was the first network database.




Object-Oriented DBMS

           An object database (also object-oriented database management system) is a database management system in which information is represented in the form of objects as used in object-oriented programming.
  • The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data.
  • PostgreSQL is an example of an object oriented relational DBMS.
  • When you integrate database capabilities with object programming language capabilities, the result is an object-oriented database management system or ODBMS. An ODBMS makes database objects appear as programming language objects in one or more existing programming languages. 


Schema:

Logical structure of the database.

Classification:
  • Physical
  • Conceptual
  • External 


Instances:

Actual data contained in database at a particular point of time.


Difference between DBMS and RDBMS:

Database management system (DBMS)

Database management system is a software used to define, record, query, update and manage data in a database. DBMS stores data in a structured way (relational, hierarchical, flat files, objects etc), and executes operations requested by various users on these data. It interacts with user, receives commands, and runs it against the database. DBMS can interact with some other applications, if required. A fourth generation query language (SQL – Structured Query Language) is used to communicate with recorded data in Database management systems.

A few application areas of DBMS are Finance, Marketing, Sales, Banking, Manufacturing, Airlines etc. Have a look on the below image which describes the DBMS:





Relational Database management system (RDBMS)

Relational database management system is a type of database management system which stores its data in tables and columns with relationships. It is based on the relational model of Edgar F. Codd well known as E. F. Codd, an English computer scientist. Below image explains the RDBMS:




Relational Database management system (RDBMS)

    SQL Server Tutorial – Prerequisite


    SQL Server Tutorial – Prerequisite

    To start with SQL Server, you need to download and install SQL Server (Latest version recommended) from Microsoft and to do some hands-on throughout this tutorial and for assignments purposes, you need to download and attach AdventureWorksDatabase also. Below you have some links, which might help you out in these prerequisites.

    Prerequisite

    Install SQL Server – [Recommended – SQL Server 2014 or 2012]
    Download and attach “AdventureWorksDatabase”

    If you don’t have installed SQL Server yet, no worries, here are the links which may help you.

    To download SQL Server 2014;

    https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014

    For Step by Step installation of SQL Server 2014

    http://social.technet.microsoft.com/wiki/contents/articles/23878.installing-sql-server-2014-step-by-step-tutorial.aspx

    To download AdventureWorks2014 Database

    https://msftdbprodsamples.codeplex.com/releases/view/125550

    How to restore “AdventureWorks2014” Database

    Download the MS Word file from below link and follow the instructions mentioned in “Install Adventure Works 2014 OLTP database from a backup” section to restore the downloaded database.

    https://msftdbprodsamples.codeplex.com/downloads/get/880669

    What Is MSBI – Microsoft Business Intelligence?


    What is BISM & Types of BI ?


    What is Business Intelligence (BI) and it's benefits ?



    BI stands for Business Intelligence, refers to set of techniques which helps in spotting, digging out and analyzing best information out of huge data to enhance business decision making.

    Business intelligence (BI) is the set of techniques and tools for the acquisition and transformation of raw data into meaningful and useful information for business analysis purposes.

    BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies are reporting, Online analytical processing, analytics, data mining, process mining, complex event processing, business performance management, benchmarking, text mining, predictive analytics and prescriptive analytics.

    Business intelligence (BI) is a set of technologies, methodologies, processes, infrastructure and applications for gathering, storing, analyzing, and transforming large amounts of raw data into reasonable and useful information, helping enterprise users make better business decisions.

    It helps enterprise users to make better business decisions. BI applications involve the activities of decision support systems, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining.

    Business Intelligence (BI) is the process of transforming the business data into information/knowledge using computer based techniques thus enabling the users to take effective fact based decisions.

    Business Intelligence (BI) technology infrastructure for gaining maximum information from available data for the purpose of improving business processes.

    Business Intelligence (BI) Providing Right information at Right – Time in the Right- Format to the Decision makers.


                Typical BI infrastructure components are as follow: software solution for gathering, cleansing, integrating, analyzing and sharing data. Business Intelligence produces analysis and provides believable information to help making effective and high quality business decisions.


    The difference between data and information

    Data is row material for analysis. Data is always related to transactions or events. Once the data is analyzed it's considered to be meaningful information.




    Categories of BI:
                Business Intelligence is generally divided into four different categories which are comprised of different types of BI that business working with data should be aware of.
    1.    Reporting
    2.    Analysis
    a.    Spreadsheet Analysis
    b.    Ad-hoc Query
    c.     Visualization Tools
    3.    Monitoring
    a.    Dashboard
    b.    KPI (Key Performance Indicator)
    c.     Business Performance Management
    4.    Prediction
    a.    Data Mining
    b.    Predictive Modeling

    The Goal of BI is to allow for the easy interpretation of these large volumes of data. Identifying new opportunities and implementing an effective strategy based on insights can provide businesses with a competitive market advantage and long-term stability.


    Benefits of BI: - Making your Business Intelligent will always help in every field whether saving time, increasing revenue, to do forecasting, making profit, etc. There are endless benefits of BI, some of them are listed below:-
    • Helps in providing more accurate historical data by eliminating guess work. As analysis is mainly done on huge volume of data. So, accurate historical data will make sure that we get the correct result. 
    • We can analyze customer’s behavior and taste (i.e. what he thinks, what he likes the most, what he hates, etc.) which can enhance your business and decision making power. 
    • We can easily look where our customer needs more attention and where we dominates the market in satisfying client’s needs. 
    • Complex Business queries are solved with a single click and at a faster rate which saves lots of time. 
    • Improve Data Security
    • Reduce resources and Labor Costs
    • Help to Develop and Deliver better Forecasting
    • It removes Guess work
    • Gives you quicker responses to your business-related queries.
    • Gain a better understanding of your business past, Present and future.
    • Identification of problem areas.
    Improve efficiency using forecasting. You can analyze data to see where your business has been, where it is now and where it is going.

    Success factors of implementation
    Although there could be many factors that could affect the implementation process of a BI system. The following are the critical success factors for business intelligence implementation:
    • Business - driven methodology and project management.
    • Clear vision and planning.
    • Committed management support & sponsorship.
    • Data management and quality.
    • Mapping solutions to user requirements.
    • Performance considerations of the BI system.
    • Robust and expandable framework.

    Advantages of Business Intelligence
    Business Intelligence, when properly implemented and used, delivers many benefits. Some of the key advantages include:
    • Alignment of an organization around a consistent set of Key Performance Indicators (KPIs) and Metrics
    • Correct, Quicker, fact-based and Relevant decision making
    • Simplified graphical presentation of KPIs and metrics
    • Reliable presentation of information
    • Combination of multiple data sources (ERP, CRM, Spreadsheets, Budgets, etc.)
    • Faster collection and dissemination of information.
    • Easier and Quicker access to Information
    • Time Savings.

    Disadvantages of Business Intelligence
    Some of the major Business Intelligence disadvantages are:
    • Piling of Historical Data
    • Cost
    • Complexity
    • Muddling of commercial settings
    • Limited use
    • Time Consuming Implementation.
    The most common kinds of Business Intelligence systems are: 

          EIS - Executive Information Systems
         DSSDecision Support Systems
         MISManagement Information Systems
         GISGeographic Information Systems
       OLAPOnline Analytical Processing and multidimensional analysis
        CRMCustomer Relationship Management


    Steps involved in BI end to end Solution are:-

    Integration of data from different data stores using ETL (Extract Transform Load), on which analysis is to be done.
    Loaded data is then analyzed for BI engagement.

    Representation of the analyzed result in the form of reports, scorecards, dashboards etc.



    Business Intelligence:





    There are various BI vendors offering end-to-end solutions in the market:



    Other BI tools:


    ±SAP BusinessObjects: a complete BI platform

    ±IBM Cognos Series 10: strong in analytics

    ±JasperSoft: open source, now TIBCO

    ±MicroStrategy: strong in reporting & performance

    ±Oracle BI (OBIEE): a complete BI platform

    ±Oracle Hyperion System: tool for consolidation

    ±Pentaho BI: open source, now HDS

    ±Yellowfin BI: strong in dashboards

    ±Qlik: strong in Data Discovery

    ±SAP NetWeaver BW: data warehouse + cubes

    ±SAS BI: very strong in analytics

    ±Style Intelligence: a niche player

    ±Tableau Software: strong in Data Visualization

    ±WebFOCUS: a complete BI platform

    ±BizzScore: strong in performance management

    ±Board: strong in performance management

    ±Birst: a complete BI platform in the cloud