FIX: A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Most often we may come across issues like below when tried to connect Analysis Service Data Connection:
"A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)"

To fix this follow below steps:
Step 1: Navigate to Programs|SQL Server XXXX|Configuration tools|SQL Server Surface Area Configuration

Step 2: Change the service account to local system as shown below:

How to shrink database and database logical files

1. Execute the below command to find the logical file names and their location in local folder:

USE <DB_NAME>
Execute SP_Helpfile

E.g., Use AdventureWorks
         Execute SP_HelpFile

2. Command to shrink a database:

    DBCC SHRINKDATBASE (AdventureWorks)

3. Command to Shrink Logical files (.MDF, .LDF):
    If the MDF logical file name of AdventureWorks database is AdventureWorks_Data
   Then the command is,

   DBCC SHRINKFILE (AdventureWorks_Data)

MDF, NDF and LDF Files in DQL Database

Primary data files: MDF FilesPrimary data file is the starting point of the database. It points to the other files in the database. Therefore, every database has one primary data file. Also, all the data in the database objects (tables, stored procedures, views, triggers.. etc.) are stored in the primary data files. The recommended and the most common file name extension for primary data files is .mdf.

Secondary data files: NDF Files
You can only have one primary data file for a database. Rest made up by secondary data files. But its not necessary to have a secondary data file. Therefore some databases may not have any secondary data file. But its also possible to have multiple secondary data files for a single database. .ndf is usually recommended to denote secondary data files. It’s also possible to store the secondary data file in a separate physical drive than the one which primary data file is stored.

Log filesL LDF files
Log files in SQL Server databases hold all the log information. Those information can be later used to recover the database. Size of the log file is determined by the logging level you have set up on the database. There must be at least one log file for each database. But it is also possible to have more than one log file for a single database. The recommended file name extension for log files is .ldf.

SQL to get First, Last and Largest Transaction based on Transaction Amount and Date

The below query helps an user to get more details of transaction like first, last transaction, largest transaction based on transaction amount, etc.

1. Consider a table dbo.TransactionSummary as shown below:

TransactionID	AccountID	TransDate	TransType	BranchID	TransAmount
1001	11234	2000-01-01	Direct	1	1200
1002	11235	2001-03-02	Direct	1	2000
1003	11234	2001-05-12	Direct	2	1345
1004	11234	2002-05-23	Direct	1	2050
1005	11234	2002-07-23	Indirect	2	1000
1006	11235	1999-09-12	Indirect	1	2000
1007	11235	2010-09-17	Indirect	1	3000

Now, I want to get the First Trans Date, First Trans Amount, Last Trans Date, Last Trans Amount, Largest Trans Date (Date on which largest transaction took place) and Largest Trans Amount for each AccountID grouped by TransType, BranchID.

The below query helps to get those details:

WITH TransData AS (
 SELECT  AccountID
                 , TransType
                 , BranchID
                 , MIN(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [FirstTransactionData]
                 , MAX(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [LastTransactionData]
                , MAX(CONVERT(BINARY(10),TS.TransAmount)+ CONVERT(BINARY(10),TS.[TransDate])) [LargestTransactionData]
FROM dbo.TransactionSummary
GROUP BY  AccountID
                      , TransType
                      , BranchID)
 SELECT  AccountID
                  , TransType
                  , BranchID
                  , SUBSTRING(@FirstTransactionData, 1, 10) AS FirstTransDate
                  , SUBSTRING(@FirstTransactionData,CHARINDEX('*',@FirstTransactionData)+1, 20) [FirstTransAmount]
                 , SUBSTRING(@LastTransactionData, 1, 10) AS LastTransDate
                 , SUBSTRING(@LastTransactionData,CHARINDEX('*',@LastTransactionData)+1, 20) [LastTransAmount]
                , CONVERT(DATE,SUBSTRING(@LargestTransactionData,11,10)) [LargestTransDate]
                , CONVERT(MONEY,SUBSTRING(@LargestTransactionData, 1,10)) [LargestTransAmount]
FROM TransData

Define Data Warehouse

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.

Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, "sales" can be a particular subject.

Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.

Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept. For example, a transaction system may hold the most recent address of a customer, where a data warehouse can hold all addresses associated with a customer.

Non-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.

Different Data Models in Data Warehouse

There are three types of data modelling used to construct a data warehouse, namely:

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

Before going in depth, let us consider some importatnt objects created while designing a database:
Entity Names, Entity relationships, Attributes, Primary and Foriegn keys, Table names, Column names and Data types.

The below chart explains about the difference between the data models based on the database objects:



Conceptual Data Model:

Conceptual data model describes about highest level of relationships between entities. It includes only the entities names and their relationships. No table names, primary keys, foreign keys and column names are referred in this model.

Logical Data Model:

Logical data model provides more information on database structure, it include entities and their relationships, Primary and forign keys and provide more possibilities of handling normalization at earlier stage.



Physical Data Model:

A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Denormalization occurs based on user requirements in this stage.

We can see that the complexity increases from conceptual to logical to physical. This is why we always first start with the conceptual data model (so we understand at high level what are the different entities in our data and how they relate to one another), then move on to the logical data model (so we understand the details of our data without worrying about how they will actually implemented), and finally the physical data model (so we know exactly how to implement our data model in the database of choice). In a data warehousing project, sometimes the conceptual data model and the logical data model are considered as a single deliverable.

SQL Agent Error: Unable to Create or Edit Step in SQL Jobs

In SQL Server 2008\R2 we may come cross this error. The user may not able to edit or create new steps due to the below error :

“Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)..”


The above error can be solved by executing below command in Command Prompt:

C:\Windows\System32>regsvr32 “c:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTS.dll”