Tuesday, June 12, 2012

ETL Performance Auditing - Part 1: Introduction to ETL Auditing by Frank A. Banin - SQL ServerCentral

Greetings,

I would like to share this great post by Frank A. Banin at SQL ServerCentral. It's a 3 Part series and only the first 2 parts have been posted so far. I'm yet to read the second part but if the first is anything to go by, it's definitely worth a read. 

This is not a posting of the entire article but I'm merely spreading the gospel. :) So here is the link to part 1ne of the article: ETL Performance Auditing - Part 1: An Introduction to ETL Auditing.

P.S. There's a lot of great SQL(BI) Material on SQL ServerCentral so please look around the website and read a little, you could even join in and be a member. I promise you, you won't regret it. If you do, fine. I probably could do much to brighten you mood anyways...

Anyways, that's it for now. Have a gr8 1ne!!!
 

Monday, May 21, 2012

SSRS: To Stored Procedure or To Embedded T-SQL? That is the question!

Hi Everyone,

I've always had this question and being a staunch believer in the way I do things, I thought its best that I put my views out there. I know that someone out there may disagree with me, just like my colleague who always argues a contrary view to my logic, that's fine, I was never the star in my class anyways but I reckon my reasons are legitimate and a lot of other SQL experts agree with me when it comes to this 1ne. I've taken some of the points from other bloggers and have added them to this post. I'll add their links at the end, so as to not infringe on the copyright issue. This is will be in point form so as to not bore you with long "paragraphic" reading. Anyways, enough about what people think, let's get to the business part of this blog, but before we do, let's go back to the question again; is it better to use a Stored Procedure for your SSRS Report Dataset or should 1ne stick with Embedded SQL instead?

Stored Procedures vs Embedded SQL

Stored Procedures as a Dataset Source for an SSRS Report
Pros
  • Performance:  the ability for the stored procedure to reuse a query plan (stored procedure cache)
  • DBA can tune more effectively, if needed
  • Permits the DBMS to secure the object, if needed
  • Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
  • Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
  • Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account
  • Ability to query system tables to find usage of tables & columns (which may help with change management)
  • For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed
  • Refactoring the database is easier
  • Unit testing of the code is much easier. You can easily build tests to just call the stored procedures. While possible via the web service interface, it's much harder to test the reports directly and requires a different skill set
  • It allows the UI to be built by one person and the stored procedures to be built by another
  • Benefits on procedure cache efficiency
  • The same stored procedure may be used on multiple reports.

Cons
  • Harder to manage security
  • Need “Create” permissions on the source database or need another person to create the stored procedure for you
  • Slightly more knowledge is required to create a stored procedure than a simple select statement
  • Can clutter up the database with quite a few simple queries and/or redundant queries
  • Additional handling is needed to parse multi-valued parameters in SSRS
  • Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently
  • Additional testing of a changed stored procedure & the effect of the change on the report (which may take slightly more time since they are separate)
  • May require additional personnel / time / coordination of efforts if the stored procedures are maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)
  • Report may break when schema changes
  • Difficult to make changes to embedded TSQL
  • Causes procedure cache to bloat

Embedded SQL in SSRS Dataset
Pros
  • Easy (less syntax for a beginner to learn)
  • No “Create” permissions needed on the source database
  • One-step deployment (unless you are using a Shared Dataset, which is stored outside of the RDL)
Cons
  • For large datasets, may not perform as well as a stored procedure
  • Greater possibility that individual report queries are redundant or handling logic in different ways (a great way to combat this is to use Shared Datasets, a new feature in SQL Server 2008 R2)
  • SSRS Query Designer doesn’t retain formatting well
  • SSRS Query Designer removes comments (a big shortcoming in my opinion)
  • SSRS Query Designer renames aliases in some circumstances
  • The report developer may need additional permissions to database objects to construct the queries (i.e., if direct table access is being utilized instead of views)
  • Need to open the report in BIDS (or Report Builder) in order to make a change
  • Much more difficult to monitor the database objects being accessed by these queries (i.e., not as easy as querying the system tables with a stored procedure)

Please note that this is not the be all and end all to this design decision but it should provide one with valid reasons why they would choose one way over the other. I do however advise a combination of the 2, that is, were 1ne wants to populate the report's parameters, for example, they would rather use embedded T-SQL instead of a Stored Procedure and were 1ne wants to populate the report's main dataset, they would a use Stored Procedure over embedded T-SQL.

That's it for this session but before we go, here's a list of the references for this article. There are some clever people that write about the wonders of SQL and I've gained a lot from reading their blogs. So go ahead and have a look at what they do.

References:

Wednesday, April 11, 2012

Time to start blogging again!

Good Day Everyone,

I've recently been having numerous conversations with my housemate (@kulilegobingca) about blogging, or in our case not blogging. These conversations were not IT related by any means but they reminded me of the reasons for starting this blog site in the first place. In fact, more so than anything, he made me realize the selfishness that goes with NOT publishing/sharing information that may, OR may not, be relevant to the masses (even if it is just 1ne* person). In fact it is the cardinal sin many of my fellow beings fall victim to. This was further perpetuated when a stumbled upon the quote, which my African friends my relate to; which says: "Oppression can only survive through silence!"


I've therefore taken an oath not to fall victim to this sickness again, I shall blog, blog, blog; and will continue to encourage others to do the same. 

My Focus
I'll be focusing on most things BI, that is Business Intelligence (that's my field by the way), if I stray a bit off the topic, please don't blame me. I'm still a young man and would like to keep my blog interesting.

I'll also do my best to post articles I read from other sources. I PROMISE NOT TO PLAGIARIZE, BUT I WAS NEVER THAT GOOD AT ADDING REFERENCES, THAT STEMS FROM MY HIGH SCHOOL DAYS and remember I'm still a young man. 



Note
*1ne = one (This is how I Like to write my ones!!! ;)

Monday, May 31, 2010

Whats New in SQL Server 2008

Greetings guys,

Welcome to my first post. It's not really mine actually but i thought I'd share this valuable article with you.

This article will highlight some of the new features and benefits found in SQL Server 2008. Follow the links below.

What’s new in SQL 2008 Part 1
What’s new in SQL 2008 Part 2
What’s new in SQL 2008 Part 3

Till next time!!!

Thursday, February 25, 2010

Welcome

Greetings Guys,

I must say I've always had the urge to start my own blog but have never actually got down to doing it before. Then again there's lots of things I've set out to do but never got around to doing them and I reckon that makes me a normal guy but never mind that's a story for another day.

So Mr Quillz would like to Welcome you to all to his blog site... Ngegama Elihle leNkosi uJesu (and my friends keep on telling me that i'm not religious). Namkelekile!!!!!