Kristin Ferrier's Microsoft BI Beginner's Blog

SQL Server vs. Oracle Differences Part 2 (Object Naming Differences)

2012-01-14T16:44:00.000-08:00

There are a couple of key object naming differences that I noticed when working with Oracle for the first time. The first is the max character difference. In SQL Server, an identifier (table name, view name, stored procedure name, etc.) can have a max of 128 characters. In Oracle, the max length is 30 characters, except for a few cases. Coming from SQL Server, the 30-character restriction in Oracle was quite noticeable. I especially noticed it when naming stored procedures. I've found myself having to abbreviate words when naming Oracle stored procedures instead of spelling words completely out.

Another difference with naming appears to be somewhat related to the GUI tools. With SQL Server, if you create table Orders, SQL Server Management Studio (SSMS) will display it as Orders. In Oracle, however, a table created as Orders will display in the GUI as ORDERS. This occurs because nonquoted identifiers, like Orders, are not case sensitive with Oracle. Oracle interprets them as uppercase. Thus you'll see the table you created as Orders listed as ORDERS when looking at the database through a GUI tool like Toad or SQL Developer. Quoted identifiers, such as "Orders" are case sensitive, but then you are required to include the quotes when referencing the object. Since nonquoted identifiers tend to be more popular when naming common objects such as tables, views, stored procedures, etc. I found this important to note.

Oracle's upper-casing interpretation of nonquoted identifiers has resulted in a somewhat common practice of using underscores when naming objects. For example a SQL Server stored procedure SelectProductsByCategory might be named SELECT_PRODUCTS_BY_CATEGORY in Oracle. In this case we have a 27 character name, which is just barely under the 30 character limit. If we wanted to rename SelectProductsBySubCategory to SELECT_PRODUCTS_BY_SUB_CATEGORY we would be at 31 characters which is 1 beyond the max. The use of underscores, in this case, is what pushes us above the 30 character max.

Fortunately, from a querying perspective, the casing of object names doesn't seem to be as relevant. That is, of course, dependent upon the settings. Assuming your SQL Server database is created with a Case Insensitive collation, SQL Server will not require specific casing when querying your objects. If you create table Orders, both "SELECT * FROM Orders" and "SELECT * FROM ORDERS" will be valid queries. With Oracle (using the default settings), "SELECT * FROM Orders"and "SELECT * FROM ORDERS" would both be valid, as well.

There are, of course, more details on SQL Server and Oracle naming rules than I've been able to put into my post. SQL Server's naming conventions are available here. Oracle's naming rules are available here.

SQL Server vs. Oracle Differences Part 1 (Cursors)

2012-01-10T19:01:00.000-08:00

Since my first job out of college, where I was working with SQL Server 6.5, I've been told that cursors are BAD, BAD, BAD! Cursors are evil! I was, eventually told the reason to be performance. In SQL Server world, cursors are still considered evil in most situations. I would submit that T-SQL code with a cursor would be considered "bad code" by many in SQL Server world, assuming there wasn't a code comment explaining the use of cursor. I'm not saying this is right or wrong, I'm just submitting what I would expect would happen.

Now get ready for it, with Oracle PL/SQL, cursors are often a BEST PRACTICE! This was, probably, the hardest best practice difference for me to grasp. Cursors are very much accepted as good practice in Oracle world. You'll even see them used quite often in Steven Feuerstein's Oracle PL/SQL Best Practices book that I found to be very useful.

SQL Server vs. Oracle Differences From a Developer's View

2012-01-10T19:00:00.000-08:00

Over the past 2 1/2 years, I've been doing a lot of software development with Oracle as the RDBMS. After a decade of only working on development and BI projects with a SQL Server back end, I found myself primarily working on a quite large, already existing web application that uses Oracle as its RDBMS. Don't get me wrong, I'm still involved with the world of SQL Server. I still see it at work and, even more, I'm President of our local PASS chapter. I also did two years of speaking on SQL Server at various events and user groups during this time period.

As a software developer, though, I've found working with Oracle to be a great opportunity to become familiar with another leading RDBMS. When I started my current job, I was told that I'd be able to pick up Oracle quite quickly, given my strong data background. I'd like to think that I picked things up pretty quickly, but there are A LOT of best practices differences and, also, some capability differences that I needed to become aware of as a developer. Since, I haven't seen a lot of resources that put these differences together into one place, I decided to start this blog post series.

I hope to make this blog post series a resource for developers who are switching between T-SQL and PL/SQL. This is not intended to be a "which RDBMS is better?" conversation. I personally believe that there are multiple RDBMS solutions that have the capability to meet a variety of business needs. I hope this series will help developers shorten their learning curve between the two products. Note: At the writing of this post, SQL Server 2008 R2 is the latest officially released version of SQL Server. I have been keeping up with the upcoming T-SQL features for SQL Server 2012, though.

SQL Saturday Oklahoma City, August 27th

2011-08-15T20:03:00.000-07:00

I am very excited to be on the Steering Committee for SQL Saturday Oklahoma City! We're sold out with 250 registered, but we're hoping to be able to include those from the wait list.

We've got DBA, Developer, and BI tracks planned. I look forward to seeing those registered. As the Speaker Chair, I very much hope everyone enjoys the sessions:)

For those wondering where I've been on the blog front, I've been spending most of my community time with regional community involvement. OKCSQL assisted with three SQL Server Boot Camps in 2010. We're, also, still meeting monthly at the OKCCoCo. Plus, I've been helping plan SQL Saturday OKC. As for speaking, I'm currently on sabbatical. My last speaking engagement was at Tulsa Tech Fest 2010.

I hope to see you guys at SQL Saturday OKC!

Tulsa Tech Fest 2010 Presentation

2011-08-15T19:59:00.000-07:00

Back in November I had the opportunity to give my latest presentation Intermediate SSAS 2008 - Developing Time Calculations which is available here.

SSIS Scripting

2010-07-14T18:56:00.000-07:00

Tim Mitchell did a great job presenting on Scripting in SQL Server Integration Services this past Monday to OKCSQL. A video of his 90 minute presentation is available here.

T-SQL Intersect and Except

2010-07-14T18:21:00.000-07:00

During the SQL Server 2008 Database Development Boot Camp in February, several T-SQL features introduced in SQL Server 2005 and 2008 were covered. I wanted to blog about the one that most caught my eye. SQL Server now has both Intersect and Except which follow the below syntax.

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Examples from the AdventureWorks database for SQL Server 2008 follow:

SELECT COUNT(*) FROM Sales.Customer
(Returns count of 19185)

SELECT CustomerID FROM Sales.Customer
INTERSECT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 19119 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in BOTH Sales.Customer and Sales.SalesOrderHeader)

SELECT CustomerID FROM Sales.Customer
EXCEPT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 66 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in Sales.Customer that are not found in Sales.SalesOrderHeader)

I've heard debate as to whether or not INTERSECT and/or EXCEPT are faster than other options. Thus, I'll leave it up to you to try them out in your own systems to determine their potential value. I will mention, though, I have heard of a real-word case where an INTERSECT query similar to the one in the above example took seconds or minutes instead of hours. In that specific scenario the table on the left side of the INTERSECT had just a few thousand records and the table on the right side of the INTERSECT had millions if not billions of records.

June 2010 OKCSQL Presentation

2010-07-14T17:17:00.000-07:00

After multiple weather postponements, I was able to give my Introduction to Visual Studio 2010 Database Functionality presentation to the Oklahoma City SQL Server Developers Group. With the meeting taking place not on our standard meeting day, I had some minor concerns that no one would show. To my delight we had a good turnout and one of my colleagues from a previous employer showed and brought some of his colleagues too.

Most important, though, is that we didn't have another serious weather event. I knew some people impacted from the first postponement, which was caused by tornadoes. The second postponement came from floods.

SQL Saturday Dallas 2010

2010-07-14T16:34:00.000-07:00

I had a great time speaking at and attending SQL Saturday Dallas 2010. The North Texas SQL Server User Group did an excellent job organizing the event! The event was amazing with a 500 person sellout, 42 sessions, and lots of great networking. You would have never known that this was their first SQL Saturday to put on.

While there, I had the opportunity to give two presentations.

I debuted my Visual Studio 2010 Database Functionality presentation to about 40 people. I had hoped to first present this topic at OKCSQL in May, however, we had some issues due to inclement weather the day of the May meeting.

I, also, presented my Introduction to SQL Server Analysis Services 2008 OLAP presentation to 55+ people.

I look forward to attending future events put on by the North Texas SQL Server User Group.

Note: Presentation links are pointing to presentations given at the OKCSQL meetings. The OKCSQL 90 minute presentation slots provided the opportunity to include more content than at SQL Saturday Dallas.

Thoughts on February SQL Server 2008 Boot Camp

2010-06-02T20:24:00.000-07:00

Wow!!! Things have been so hectic the past few months, that I'm just now getting to blog about February's free SQL Server 2008 Boot Camp taught by Mike Hotek. Over the course of 9 evenings in Oklahoma City, Mike Hotek (18th SQL Server MVP) taught a FREE 32 hour Microsoft SQL Server 2008 Certification Boot Camp 70-433 to 40 professionals. Professionals ranged from an Informatica specialist with an Oracle background all the way to very experienced SQL Server DBAs.

The course was coordinated over the course of just a few weeks by just five individuals. They were Rob Sullivan (VP of OKCSQL), Mike Hotek (our instructor), Lily Liu (amazing volunteer who coordinated with OUHSC to get us the free location), Rao Velvadapu from OUHSC, and myself. The course was planned very last minute, so we just had a few weeks to get everything together. As a result, neither myself nor Rob had the opportunity to visit the location beforehand. Thus, I remember showing up on night one only 10 minutes before the class (I had been lost on the OUHSC Campus for 30 minutes) and walking into a SUPER crowded classroom. There was almost not a place for me or any other later arrivers to sit. So lesson one learned, always visit the location of an event before the event, if you're a key coordinator. The second night we started rearranging the table layout before and after each class.

The course was to focus on SQL Server 2008 Database Development. Since, I'd been working with SQL Server for over a decade, I didn't think there would be a lot of new stuff for me to learn on the database development side. The DBA-side would have been another story, since I've been very developer/BI focused in my SQL Server experience. Boy was I WRONG about how much I would learn. I learned a ton!!! Even more, I have lots of notes related to topics to blog on. Hopefully, I'll get to blogging on them at some point.

What many of us commented about liking so much about the class was Mike's teaching style combined with his vast knowledge base. Mike would start a topic at near beginner level and would eventually bring it to an advanced level. I expect Mike is able to do this given his international speaking experience and his experience working with more than a thousand clients over 20 years.

We got a lot of great feedback on the course and even had at least one guy skip a NBA game for which he had tickets in order to not miss a specific session.

With a small classroom of 40 people dedicated to advancing their SQL Server 2008 skills over 9 evenings for a total of 32 hours, the class even started to become like a family. The experience was great and led to a second class which Mike is currently teaching about SQL Server 2008 Database Administration. Hopefully, I can blog about that class soon.

Free Microsoft SQL Server 2008 Certification Boot Camp 70-433

2010-01-25T15:47:00.000-08:00

Throughout the past year, I've had the opportunity to work with Mike Hotek first at a client site and then later on when he spoke at the OKC SQL Server Developers Group for several meetings. He is one of the most knowledgeable SQL Server experts that I have personally met. Even more, he has been very supportive of several local SQL Server groups, including the OKC SQL Server Developers Group.

One very gracious offer that he put forward was the willingness to provide free SQL Server training in the evenings, if someone would provide the location. Back in July 2009, Integris hosted a free two evening SSRS 2008 class.

This February the OU Health Sciences Center has been very gracious to provide space for 40 people to take a FREE Microsoft SQL Server 2008 Certification Boot Camp 70-433 class taught by Mike Hotek. Mike used to teach these classes for $4,000 per student. He now teaches them for very nominal fees or for free.

Today, we opened registration for this class that begins Monday, February 8, 2010. Registration is open at http://sqlbootcamp.eventbrite.com/.

We opened registration at noon CST and I've literally been watching tickets nearly sell out before my eyes. As of this posting there are 11, wait another one just went, 10 free tickets left. If you're interested in this class, register quickly!

Update (02-Feb-2010): This class sold out in less than 20 hours! We look forward to a great class next week!

Introduction to SSAS 2008 Video

2009-12-17T17:56:00.000-08:00

I've been looking a lot at the MSDN SSAS forums lately and noticed a lot of questions about processing, partitioning, and perspectives. I discuss some of these topics at the end of the my 90 minute presentation Introduction to SQL Server Analysis Services 2008 OLAP that I gave back in July to the OKC SQL Server Developers Group. These topics begin at minute 64:30.

I have given this presentation several times since, however, this is the only presentation that was video-taped. In addition, with many presentation slots being only 60-75 minutes, the July meeting was the only time where I had time to discuss the aforementioned topics.

I hope you find this video helpful in getting up to speed with the basics of SSAS 2008 OLAP. If you have any questions, feel free to post a comment here or go to the aforementioned MSDN SSAS forums.

Microsoft BI Introduction Videos

2009-10-29T16:21:00.000-07:00

We've been very fortunate to have a great start to the OKC SQL Server Developers Group this year. We started in April and have been averaging 25 attendees per monthly meeting.

With family obligations and other time constraits, some developers just don't have the time opportunity to attend developer groups and/or technical conferences. I've found great benefit to attending meetings in person, but am aware that many can't take advantage of these opportunities.

Fortunately, we have been meeting a the OKC Coworking Collaborative that has been video taping our meetings. I personally, find the videos to be of pretty good quality with respect to both sound and video. Below are links to two presentations given by Mike Hotek the 18th SQL Server MVP. Currently, additional videos are available via links on the OKC SQL Server Developers Group website.

Introduction to SQL Server Integration Services 2008 by Mike Hotek (May 2009)

Introduction to SQL Server 2008 Data Mining by Mike Hotek (June 2009)

Tulsa SQL Server Group

2009-10-29T16:06:00.000-07:00

Back in September I had the opportunity to speak at the Tulsa SQL Server Group. I presented a shortened version of the Introduction to SQL Server Analysis Services 2008 OLAP presentation that I did for the OKC SQL Server Developers Group back in July. Thanks to the 20+ people who came out in sheets of rain. On the way up, I was wondering just how many people would brave the downpour.

I enjoyed catching up with old friends and colleagues and meeting new ones.

Speaking at Tulsa Techfest 2009

2009-10-29T16:04:00.001-07:00

I'll be presenting Introduction to SQL Server Analysis Services 2008 OLAP at Tulsa Techfest 2009. The event is taking place at OSU/Tulsa on Friday, November 6th. More details are available here.

SSAS 2008 Presentation Slides

2009-07-21T17:08:00.000-07:00

Thanks to everyone who came out to the July meeting of the OKC SQL Server Developers Group to see me present on Introduction to SQL Server Analysis Services OLAP. We had about 25 people come out, including some former colleagues of mine. For those who missed the presentation or saw the presentation and would like the slides, a slightly modified version is available here at Google Docs. (Update: In some cases 2 tries has been necessary for the Google Docs link to work)

For anyone interested in joining the group, check our website out at http://www.okcsql.org.

Speaking this Week

2009-07-12T20:35:00.000-07:00

For those interested in learning more about SQL Server Analysis Services, I'll be doing a 90 minute presentation called "Introduction to SQL Server Analysis Services 2008 OLAP" at the July meeting of the Oklahoma City SQL Server Developers Group. The meeting is from 6:00 p.m. - 8:00 p.m. at the Oklahoma City CoCo. More details are available at http://www.okcsql.org. I hope to see you there.

Oklahoma City SQL Server Developers Group

2009-02-22T16:38:00.000-08:00

There's been talk in recent years of getting an Oklahoma City SQL Server Developers user group started in Oklahoma City, Oklahoma. If you live in the area and are interested in more information about the group, please, e-mail sqlokc at gmail dot com. I'm working with some others to get this group started.

ProClarity Dashboard 6.2 and Firefox, Cube could not be found error

2009-02-22T16:09:00.000-08:00

I've still got users that are quite happy using either ProClarity Desktop Professional or ProClarity Dashboard Server 6.2 as their reporting tool for SSAS 2005 cubes. For those still using ProClarity Desktop and ProClarity Dashboard as reporting tools, I thought I'd post a few things that I've learned over the past few months.

One item I learned, is that ProClarity Dashboard 6.2 does not work with some if not all versions of Firefox.

I was on the phone with a user who was getting the below message in their web browser:

"The selected page could not be opened because the cube could not be found. Please choose a different page."

The error message suggested that the cube was either not available to anyone or that the user's security was not setup properly. Thus, I checked both the user's cube and ProClarity Dashboard security. The user's security appeared to be setup properly in both cases.

I then checked the ProClarity Event Log on the server holding the ProClarity Dashboard. There was an error with a description like the one below:

"Information. Accessing page NameOfPage ....
from book NameOfBook ..."

Since, the Dashboard page was accessible to others, this message wasn't, especially, useful.

After all this research, I went down to the user's pod to see how she was accessing the ProClarity Dashboard and saw that she was using Firefox. I had her switch to IE, and then the ProClarity Dashboard started working for her.

I have been unable to find official documentation as to whether or not Firefox is supported with ProClarity Dashboard Server 6.2, however, from my experience Firefox does not work well with this product.

FYI...There are a variety of other possible causes for a "Cube could not be found" error in the ProClarity Dashboard. Many are documented at the ProClarity Team Blog.

Microsoft Acquires DatAllegro

2008-07-25T13:07:00.000-07:00

The big buzz right now in BI seems to be Microsoft's announcement yesterday of a deal to purchase data warehouse appliance vendor DatAllegro. There are tons of blog posts and articles out there. So here's a quick summary and then a link.

According to Doug Henschen's artcle, Microsoft claims that "the DatAllegro acquisition will accelerate our data warehousing roadmap and quickly take us into high-scale deployments in the hundreds of terabytes."

Doug Henschen has a good article here explaining both the aquisition and the MPP architecture that DatAllegro expoits.

So, if your company is using SQL Server and starting to reach the 10's of terabytes of data, this may interest you.

SQL Server 2008 Launches

2008-02-27T14:31:00.000-08:00

Launches for SQL Server 2008, Visual Studio 2008, and Windows Server 2008 have started around the nation. They will be going on for the next several months.

There are two kinds of launches.

Microsoft hosted launches: http://www.microsoft.com/heroeshappenhere/register/default.mspx
Community hosted launches: http://www.heroescommunitylaunch.com/

Hopefully, those in the US can find a launch in their area.

I'll be at the launch hosted by the Oklahoma City Developers Group on April 7, 2008. I am currently President of the OKCDG. As of right now, registration is open at http://heroes.okcpro.net. I'll be there, along with my fiancé, and several others from surrounding areas. We would love to have you come.

Nigel Pendse's BI Survey 7

2008-02-11T15:05:00.000-08:00

Interesting post by Chris Webb discussing his summary of Nigel Pendse's BI Survey 7. If you're interested in knowing how well SSAS is being selected as an OLAP tool for not only SQL Server data sources but also Oracle, Sybase, and other database vendors, I highly recommend this article.

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1589.entry

The 404 page document survey is available for purchase at http://www.bi-survey.com/

Free ProClarity Training

2008-02-05T15:49:00.000-08:00

For those using ProClarity, there is now free on-line training available at the following link:

http://www.microsoft.com/bi/products/proclarity/proclarity-training.aspx

ProClarity Analytics Server and Authoring
Professional End User Client I
Professional End User Client II
Web Standard Client End User
Integrating ProClarity Analytics into a SharePoint Portal
Creating and Managing Business Logic with ProClarity
ProClarity Dashboard Administrator
ProClarity Dashboard End User

BIDSHelper 1.2 Release is Out

2007-11-29T15:52:00.000-08:00

Recently BIDSHelper 1.2 was released. I just now had a chance to find out and download it. You can download it here.

I found out about BIDSHelper from Chris Webb's blog several months ago and have found it to be very, very useful. I use the Deploy MDX, Edit Aggregations, and Visualize Attribute Lattice functionality quite a bit. For those not familiar with it, I've met several people who aren't, it's a community project BIDS Add-In. Even more, Katmai appears to have some functionality similiar to some of the features found in BIDSHelper.

IBM is buying Cognos

2007-11-12T11:16:00.001-08:00

For those that haven't heard yet, IBM is acquiring Cognos, who had been one of the largest remaining independent companies in the BI software space.

http://www.businessweek.com/technology/content/nov2007/tc20071112_678294.htm?chan=top+news_top+news+index_businessweek+exclusives