Friday, September 28, 2012

SQL Saturday in Charlotte...

One of the neat things about the SQL Server community is ability to share knowledge and advice through community events. SQL Saturday is a neat concept where in on a Saturday SQL Server related topics are presented and discussed in a city. This gives the ability for the folks who tight on budget to attend the SQL Saturday events and get good information and also network with othe SQL Server Professionals. BI has grown tremendously over the years and the number of folks working with SQL Server BI is increasing and growing. On Oct 27,2012, sql server saturday BI edition is going to be held at Location: Central Piedmont Community College (CPCC), Levine Campus, 2800 Campus Ridge Road, Matthews, NC 28105 (located in South Charlotte). Please see link for additonal details about the event and the schedule of topics/speakers.
http://www.sqlsaturday.com/174/schedule.aspx
Hope folks who are attending get benefited from the above event.




Monday, September 24, 2012

Import Data into SQL Server From SAS...

In the current business environment, there are lot of data sources being used and constantly data is being pulled into one DataMart/DataWarehouse from another data source. In certain environments I have seen SAS being used very heavily. One of my blog readers had a question about how to import data from SAS into SQL Server. One of things to be taken into consideration is what environment is SAS running currently. SAS runs on large UNIX environments and also PC windows based environments. In case SAS is running on windows, Here are the options available: The following Link talks about Bulk Loading For ODBC:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002105630.htm,
Here is a reference to:
SAS/ACCESS Interface to Microsoft SQL Server

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002299545.htm.

The above link mentions about how to export data from SAS to SQL Server. The other approach is to use SAS and export data to a file location in a flat file/.csv format and then use ETL tools to pull data from the file location.

There are cases where we would want to use SSIS to pull data from SAS. First one would need the SAS Providers for OLE DB, please use the link below:
http://support.sas.com/demosdownloads/setupcat.jsp?cat=SAS+Providers+for+OLE+DB

Please refer to the blog by Rafi Asarf: titled loading SAS files with SSIS.
http://sqlserverjedi.wordpress.com/2012/02/08/loading-sas-files-with-ssis/

The above link provided detailed information on how to import data from SAS using SSIS. Also read the comments section where in certain error situations have been discussed.

Hope all of these provide enough information on working with data from SAS.

Thursday, September 20, 2012

Database Modelling...ER Studio...

One of the important aspects of database design is to model the database. There are two types of models one is logical model and the other is a physical model. It is important to model the data that needs to be analysed or reported. Modelling provides a abstract view of the tables present in the database, here in modelling terms we talk about entities and relationships. With good modelling one can understand the value of data beneath it and also provides a better view of the business that we are trying to capture in terms of the Data. Most of the times the models (ER) are reverse engineered, rarely do models are generated first and then database is designed. Sometimes one would have a fair idea of the entities that would be present in the database through a model. I recently started working on a data sourcing project and one the requirements was to have a ER model of the data that we are receiving from the source. In order to generate/create the ER model I decided to use the ER/Studio Data Architect Version 8.5.3 from Embarcadero technologies.
http://www.embarcadero.com/.
The install was pretty straightforward. Once I launched the ER Studio, I realised that there lot of options available in this tool. There are 3 different options for creating a model:
Draw a new data Model: There are 2 options Relational,Dimensional
Reverse Engineer an existing Database: Here one would connect to a database and choose the tables that need to be modeled. This is one of the most used option.
Import Model From: This can be done on ERX file,SQL File and External MetaData.


I choose the option of Reverse Engineer an existing database, this is done by connecting to a sql server database which is set up using the ODBC setup option in the Tools menu in the main screen of the ER Studio.

Once I connected to the database I choose from the list of tables I needed to model and the ER Studio takes you through a couple of screens and finishes up generating the ER Model. In the Screens there are options to choose relationships based on Foreign Keys. Overall there are lot of features that are available in the ER Studio which makes it a full fledged modeling tool.



Monday, September 17, 2012

Database Monitoring...

One of the important tasks of DBA is to monitor Databases and check how the sql servers are performing. As one would expect there are lot of aspects involved in SQL Server Database monitoring. With the advent of bigger applications and systems requiring quick response times, Database performance has increased in importance. When a company has lot of servers a good tool would certainly come in handy. One of the tools recently i came across was SQLMonitor developed by RedGate. I have loved products from RedGate especially the backup database utility they have. The SQLMonitor tool is a web based database monitoring tool. It is avaialble for download and test at the following link:
http://www.red-gate.com/products/dba/sql-monitor/
One of the neat things about the SQL Monitor is that it has got a web based interface. There are several other features offered by this product. The features are Centralized Alerts,StraightForward Views,Real Performance Data,Top 10 expensive queries list, Built in Analysis,User Roles. The below image is taken from the Red Gate Software.
The Real time Performance Data feature is pretty neat it contains lot of useful information about Cluster,CPU,Disks,Network and System Processes. For More Information Please check out the red gate site at http://www.red-gate.com/products/.

Monday, September 10, 2012

SQL Server 2012-Database Partitioning...

One of the issues that we frequently face in databases is how to query large amounts of data in a table. One of the suggested methods is to use partitioning of tables and indexes. In case of data partitioning the data in the table is partitioned horizontally and all the partitions of the tables need to be reside in the same database.
In SQL Server 2012 the number of partitions supported has increased to 15,000, earlier it was limited to 1000.  One of the benefits of using partitioning is transfer or access parts of data efficiently. One of the scenarios were this would help is when loading OLAP systems from OLTP databases. There are a some concepts which are central to table partitioning. They are:
Partition Function: A database object that defines how rows of data are partitoned based on a partitioning column.
Partition Scheme: This is helps map the Partition of a partition function to a set of filegroups. This helps perfomance of backup operations of filegroups.
Partitioning column: The column or index of a table that is used to partition a table or an index.
One of the common scenarios where partitioning is implemented is the sliding window scenario. Please take a look at the following link (though it is for SQL Server 2005) it gives us a good idea of how to implement the partition in SQL Server:
http://msdn.microsoft.com/en-us/library/aa964122(SQL90).aspx

Let us say if we wanted to create a partition on Sale Date and have data stored for every month, one of the ways to do it is:
(Please note: Partition Function can only be created in Enterprise Edition of SQL Server, only Enterprise Edition Supports Partitoning).

create partition function pfMonthly (datetime)
as RANGE RIGHT for values('2012-01-01','2012-02-01','2012-03-01','2012-04-01')

CREATE PARTITION SCHEME pfMonthly as partition pfMonthly all
to ([primary])

One can use the following objects to check the partition and the range of values for partition:

select * from sys.partition_range_values
where function_id in (select function_id
from sys.partition_functions
where name in ('pfMonthly'))

Tuesday, September 4, 2012

DBVisualizer-Database Management Tool

When it comes to Database Management there are lot of tools out there. Database Management is one of the key activities performed by Database Administrators, Developers and Architects (depending on the Situation). With respect to SQL Server there is SQL Server there is SQL Server Management studio, now there are times situations where one need to connecto to multiple data sources. This would hold true when one is working on ETL/DataWarehosuing/Data Sourcing projects. One of the tools I recently started using (since I am working on  multiple Data Sourcing projects) is Database Visualizer. This product is available from http://www.dbvis.com/, DbVis Software. In the web site mentioned before there is a DBVisualizer Personal Edition Download available for 21 days free. The Installation of this software is pretty straightforward, one of the activities that need to be completed after installing is to make sure the drivers for the data sources are installed as well. When the software is launched we get the following screen:


In order to create a database connection , click the + icon on the toolbar, then we have screen to enter the database connection information. One of the options available is Database type as seen in the screen below.


When it comes to Database type there are bunch of options available like the ones listed below:  there are also options for SQL Server as well.

Once the database connection has been created, there options available for SQL, Scripts and other Database Management functions. One of the features that appeals to me is wide variety of connectivity to different data Sources.