tag:blogger.com,1999:blog-17177457826329806492023-11-16T06:23:00.101-08:00Quilz BlogsAquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-1717745782632980649.post-60935583253951812702020-04-07T12:31:00.001-07:002020-04-07T12:38:18.402-07:00<span style="background-color: black; color: white; font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;">Good Day Everyone,</span><br />
<span style="background-color: black;"><span style="color: white;"><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;"><br /></span><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;">I thought I would share this article, published on <a href="http://www.itweb.co.za/">www.itweb.co.za</a> on the 3rd of March 2020, which speaks to what so many of us BI professionals have and continue to </span><span style="font-family: "roboto" , "arial" , sans-serif;"><span style="font-size: 18.4px;">encountered in our work-spaces. A situation that we must do all we can to change. The Ralph Kimball Group writes about this and seeks to eradicate this issue. Some how another decade of maturity in BI has passed but we are still faced with the same issue. </span></span></span></span><br />
<span style="background-color: black;"><span style="color: white;"><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;"><br /></span><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;">Here is a quote from the article: </span></span></span><br />
<span style="background-color: black;"><span style="color: white;"><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;"><br /></span><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;">“As BI professionals we expect people to be as comfortable with data as we are. We had done all the hard work in building a data warehouse, getting the right tools and dashboard and making these available to users. But we didn’t ask them if they knew what it meant or what they had to do with it. </span></span></span><br />
<span style="background-color: black;"><span style="color: white;"><span style="font-family: "roboto" , "arial" , sans-serif; font-size: 18.4px;"><br /></span><span style="font-family: "verdana" , sans-serif;"><a href="https://www.itweb.co.za/content/8OKdWqDYrzJvbznQ">https://www.itweb.co.za/content/8OKdWqDYrzJvbznQ</a></span></span></span>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-33902782489087174682015-05-28T10:58:00.000-07:002015-05-28T11:07:57.387-07:00Webinar - June 4, 2015: ANALYTICS DASHBOARD DESIGN by Senturus<a href="http://www.senturus.com/events/analytics-dashboard-design/?mkt_tok=3RkMMJWWfF9wsRonuqjIdu%2FhmjTEU5z16ugsXaK2gIkz2EFye%2BLIHETpodcMRcJnN6%2BTFAwTG5toziV8R7PCLs153dAQUhPn" target="_blank"><span style="font-family: Verdana,sans-serif;"><b>The Science Behind Effective Dashboard Design</b></span></a><br />
<br />
<span style="font-family: Verdana,sans-serif;">
</span><span style="font-family: Verdana,sans-serif;">Learn how to strategically design analytics dashboards to bring
actionable insights to light. During this webinar, Senturus Co-Founder
John Peterson will explore the science behind creating effective
dashboards, including the challenges, opportunities, and human factors
that come into play. Using this science as the basis, John will give
his top 13 tips to optimize dashboard visualizations.</span><br />
<span style="font-family: Verdana,sans-serif;">
</span>
<br />
<h5>
<span style="font-family: Verdana,sans-serif;">PRESENTER</span></h5>
<span style="font-family: Verdana,sans-serif;">
</span><span style="font-family: Verdana,sans-serif;"><b>John Peterson</b><br />CEO and Co-Founder<br />Senturus, Inc.</span><br />
<br />
<span style="font-family: Verdana,sans-serif;">John is the company's thought leader and visionary. John directs the
delivery of all projects with Senturus, providing the bridge of
technical and business understanding. </span><br />
<span style="font-family: Verdana,sans-serif;"><br /></span>
<span style="font-family: Verdana,sans-serif;"><br /></span>
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 10pt;">Thursday, June 4, 2015</span></span>
<br />
<div style="margin-bottom: 0; margin-top: 0;">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: 10pt;">10 AM PT/1 PM ET<br />90 minutes</span></span></div>
Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-52213892891880217832015-05-09T01:01:00.001-07:002015-05-09T01:01:20.087-07:00SSRS: Can Reporting Services handle more than 210 columns??? YES IT CAN!!!<span style="font-family: Verdana,sans-serif;">Hi Everyone, this is not one of my more interesting posts but it certainly answers a question that's bothered me in recent weeks. Can SSRS handle rendering over 210 columns??? Yes, 210! </span><br />
<br />
<span style="font-family: Verdana,sans-serif;">Reluctantly, I went about doing this enormously tedious exercise. But what worried me was whether Reporting Services "could actually do it, whether it could handle it". So I began searching on the net on any such limitations or someone who had encountered such an issue. Interestingly, I couldn't find anyone who had posted a similar kind of question. Maybe, I was the first, I thought. So I was forced to knuckle down and do the did. </span><br />
<br />
<span style="font-family: Verdana,sans-serif;">Lo and behold, Reporting Services can do it!!! It can handle a report with over 210 columns. I don't know about anything greater than 211, but I can certainly tell you that it handle 211!</span><br />
<br />
<span style="font-family: Verdana,sans-serif;">So to my fellow SSRS Developers out there, see if you can beat that!!! :) </span><br />
<br />
<br />Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-82740022923958138242015-04-23T02:05:00.001-07:002015-04-23T02:05:40.694-07:00Kimball University: Three ETL Compromises to Avoid<span style="font-family: Verdana, sans-serif; font-size: small;">Greetings,</span><br />
<br />
<span style="font-family: Verdana, sans-serif; font-size: small;">It's been a few years since I've posted on this blog (certainly something I'm not proud about). But I've been quite busy since then, have grown up a little, had my first kid (his name is Quilz too) and have moved to a different city, province and to a different company all together. I guess you can say things have for changed for Quilz in a major way :). </span><br />
<br />
<span style="font-family: Verdana, sans-serif; font-size: small;">Talking about moving companies, a colleague of mine has been charged with implementing SCD 2(s) on one of the first dimensions in our </span><span style="font-family: Verdana, sans-serif; font-size: small;"><span style="font-family: Verdana, sans-serif;">Enterprise Data Warehouse (EDW). This is after we, and I've include myself in this even though I joined the company after the initial EDW project roll out, have completed the first phase of our EDW roll out. </span>He describes what he's going through as a "painful" and a "nightmare". So with out wasting anymore of your time let's get into the "meat" of these compromises that could be </span><span style="font-family: Verdana, sans-serif; font-size: small;"><span class="strong black">the undoing of your EDW initiative. </span></span><br />
<br />
<span style="font-family: Verdana, sans-serif; font-size: small;"><span class="strong black"><b>Please note, I've copied this article as it is, from the <a href="http://www.kimballgroup.com/2010/03/three-etl-compromises-to-avoid/" target="_blank">Kimball Group</a> website, so as not to dilute what the experts say.</b> I've added the above paragraph as some sort of testimony to what the experts say. So here goes...</span></span><br />
<ul>
<li><div class="meta-item">
<span style="font-size: small;">By <a href="http://www.kimballgroup.com/author/bob/" rel="author" title="Posts by Bob Becker">Bob Becker</a></span></div>
</li>
<li><div class="meta-item">
<span style="font-size: small;">March 1, 2010</span></div>
</li>
</ul>
<span style="font-size: small;">© Kimball Group. All rights reserved.</span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Whether you are developing a new dimensional data warehouse or
replacing an existing environment, the ETL (extract, transform, load)
implementation effort is inevitably on the critical path. Difficult data
sources, unclear requirements, data quality problems, changing scope,
and other unforeseen problems often conspire to put the squeeze on the
ETL development team. It simply may not be possible to fully deliver on
the project team’s original commitments; compromises will need to be
made. In the end, these compromises, if not carefully considered, may
create long-term headaches.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">In my last article on <a href="http://www.informationweek.com/news/software/info_management/223101036?queryText=%22kimball%20university%22" target="_blank">“Six Key Decisions for ETL Architectures,”</a> I
described the decisions ETL teams face when implementing a dimensional
data warehouse. This article focuses on three common ETL development
compromises that cause most of the long-term problems around dimensional
data warehouses. Avoiding these compromises will not only improve the
effectiveness of your ETL implementation, but will also increase the
likelihood of overall DW/BI success.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><strong>Compromise 1: Neglecting slowly changing dimension requirements</strong></span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Kimball Group has written extensively on <a href="http://www.informationweek.com/news/software/info_management/223101036?queryText=%22kimball%20university%22" target="_blank">slowly changing dimension (SCD) strategies</a> and
complementary implementation alternatives. It’s important that the ETL
team embrace SCDs as an important strategy early in the initial
implementation process. A common compromise is to put off to the future
the effort required to properly support SCDs, especially Type 2 SCDs
where dimension changes are tracked by adding new rows to the dimension
table. The result is often a total rework disaster.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Deferring the implementation of proper SCD strategies does save ETL
development time in the immediate phase. But as a result, the
implementation embraces only Type 1 SCDs, where all history in the data
warehouse is associated with current dimension values. Initially, this
seems to be a reasonable compromise. However, it’s almost always more
difficult to “do it right” when you have to circle back in a later
phase. The unfortunate realities are that:</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><ul>
<li><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Following a successful initial implementation, the team faces
pressure to roll out new capabilities and additional phases without time
to revisit prior deliverables and add the required change-tracking
capabilities. Thus, the rework ultimately required to support SCD
requirements continues to expand.</span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Once the ETL team finally has the bandwidth to address SCD, the ugly
truth becomes apparent. Adding SCD Type 2 capabilities into the
historical data requires rebuilding every dimension that contains Type 2
attributes; each dimension will have to have its primary key rekeyed to
reflect the new historically appropriate Type 2 rows. Rebuilding and
rekeying even one core conformed dimension will unavoidably require
reloading all impacted fact tables due to the new dimension key
structures.</span></span></li>
<li><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Facing a possible rebuild of much of the data warehouse environment,
many organizations will back away from the effort. Rather than
reworking the existing historical data to restate the dimension and fact
tables in their correct historical context, they implement the proper
SCD strategies from a point-in-time forward. By compromising the
implementation of proper SCD techniques in the initial development
process, the organization has lost possibly years of important historic
context.</span></span></li>
</ul>
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><strong>Compromise 2: Failing to Embrace a Metadata Strategy</strong></span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">DW/BI environments spin off copious amounts of metadata. There is
business metadata, process metadata, and technical infrastructure
metadata that all needs to be vetted, captured and made available. The
ETL processes alone generate significant amounts of metadata.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Unfortunately, many ETL implementation teams do not embrace metadata
early in the development process, putting off its capture to a future
phase. This compromise typically is made because the ETL team does not
“own” the overall metadata strategy. In fact, in the early stages of
many new implementation efforts, it’s not uncommon for there to be no
designated owner of the metadata strategy.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Lack of ownership and leadership makes it easy to defer dealing with
metadata, but that’s a short-sighted mistake. Much of the critical
business metadata is identified and captured, often in spreadsheet form,
during the dimensional-modeling and source-to-target mapping phases.
What’s more, most organizations use ETL tools to develop their
environment, and these tools have capabilities to capture the most
pertinent business metadata. Thus, the ETL development phase presents an
opportune moment — often squandered — to capture richly described
metadata. Instead, the ETL development team only captures the
information required for their development purposes, leaving valuable
descriptive information on the cutting room floor. Ultimately, in a
later phase, much of this effort ends up being redone in order to
capture the required information.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">At a minimum, the ETL team should strive to capture the business
metadata created during the data-modeling and source-to-target mapping
processes. Most organizations find it valuable to focus initially on
capturing, integrating, flowing, and, ultimately, surfacing the business
metadata through their BI tool; other metadata can be integrated over
time.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><strong>Compromise 3: Not Delivering a Meaningful Scope</strong></span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">The ETL team is often under the gun to deliver results under tight
time constraints. Compromises must be made. Reducing the scope of the
initial project can be an acceptable compromise. If, for example, a
large number of schemas was included in the initial scope, one
time-honored solution is to break that effort up into several phases.
It’s a reasonable, considered compromise assuming the DW/BI project team
and sponsors are all fully, if not grudgingly, on board.</span></span><br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">But it’s a problem when the ETL team makes scope compromises without
proactively communicating with the DW/BI project team and sponsors.
Clearly, this is a recipe for failure and an unacceptable compromise.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">This situation is often a symptom of deeper organizational
challenges. It can start innocently enough, with shortcuts taken under
pressure in the heat of the moment. In retrospect, however, these
compromises would never have been made in the full light of day. In an
effort to achieve overly ambitious deadlines, the ETL team might fail to
handle data quality errors uncovered during the development process,
fail to properly support late arriving data, neglect to fully test all
ETL processes, or perform only cursory quality assurance checks on
loaded data. These compromises lead to inconsistent reporting, an
inability to tie into existing environments, and erroneous data and
often lead to a total loss of confidence among business sponsors and
users. The outcome can be total project chaos and failure.</span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;"><strong>Make Compromises Openly and Honestly</strong></span></span><br />
<br />
<span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">
</span></span><span style="font-size: small;"><span style="font-family: Verdana,sans-serif;">Compromises may be necessary. The most common concession is to scale
back an overly ambitious project scope; but key stakeholders need to be
included in this decision. Other, less intrusive changes can be
considered, such as reducing the number of years of back history used to
seed a new environment, reducing the number of dimension attributes or
number of metrics required in the initial phase (while being careful
about SCD Type 2 requirements), or reducing the number of source systems
integrated in the initial phase. Just keep everyone informed and on the
same page. The key is to compromise in areas that do not put the
long-term viability of the project at risk.</span></span><br />
<span style="font-size: small;"><br /></span>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-15038765140984707332012-06-12T02:37:00.002-07:002012-06-12T02:37:30.472-07:00ETL Performance Auditing - Part 1: Introduction to ETL Auditing by Frank A. Banin - SQL ServerCentral<span style="font-family: "Trebuchet MS",sans-serif;">Greetings,</span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">I would like to share this great post by Frank A. Banin at <a href="http://www.sqlservercentral.com/" style="font-family: "Trebuchet MS",sans-serif;" target="_blank">SQL ServerCentral</a><span style="font-family: "Trebuchet MS",sans-serif;">.</span> 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. </span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">This is not a posting of the entire article but I'm merely spreading the gospel</span>. <span style="font-family: "Trebuchet MS",sans-serif;">:) So here is the link to part 1ne of the article: <a href="http://www.sqlservercentral.com/articles/ETL+Performance/90322/" target="_blank">ETL Performance Auditing - Part 1: An Introduction to ETL Auditing.</a></span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">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...</span><br />
<br />
<span style="font-family: "Trebuchet MS",sans-serif;">Anyways, that's it for now. Have a gr8 1ne!!! </span><br />
<span style="font-family: "Trebuchet MS",sans-serif;"> </span>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-32035732451645293832012-05-21T09:38:00.001-07:002012-05-21T09:38:38.680-07:00SSRS: To Stored Procedure or To Embedded T-SQL? That is the question!<div style="font-family: "Trebuchet MS",sans-serif;">
Hi Everyone,</div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br /></div>
<span style="font-family: "Trebuchet MS",sans-serif;">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; <b>is it better to use a Stored Procedure for your SSRS Report Dataset or should 1ne stick with Embedded SQL instead?</b></span><br />
<div style="font-family: "Trebuchet MS",sans-serif;">
<br /></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<b>Stored Procedures vs Embedded SQL</b></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br />
<u>Stored Procedures as a Dataset Source for an SSRS Report</u></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
Pros</div>
<ul style="font-family: "Trebuchet MS",sans-serif;">
<li><span style="font-size: x-small;">Performance: the ability for the stored procedure to reuse a query plan (stored procedure cache)</span></li>
<li><span style="font-size: x-small;">DBA can tune more effectively, if needed</span></li>
<li><span style="font-size: x-small;">Permits the DBMS to secure the object, if needed</span></li>
<li><span style="font-size: x-small;">Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them</span></li>
<li><span style="font-size: x-small;">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)</span></li>
<li><span style="font-size: x-small;">Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account</span></li>
<li><span style="font-size: x-small;">Ability to query system tables to find usage of tables & columns (which may help with change management)</span></li>
<li><span style="font-size: x-small;">For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed</span></li>
<li><span style="font-size: x-small;">Refactoring the database is easier</span></li>
<li><span style="font-size: x-small;">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</span></li>
<li><span style="font-size: x-small;">It allows the UI to be built by one person and the stored procedures to be built by another</span></li>
<li><span style="font-size: x-small;">Benefits on procedure cache efficiency</span></li>
<li><span style="font-size: x-small;">The same stored procedure may be used on multiple reports. </span></li>
</ul>
<div style="font-family: "Trebuchet MS",sans-serif;">
<span style="font-size: x-small;"><br /><span style="font-size: small;"><span style="font-family: "Trebuchet MS",sans-serif;">Cons</span></span></span></div>
<ul>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Harder to manage security</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Need “Create” permissions on the source database or need another person to create the stored procedure for you</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Slightly more knowledge is required to create a stored procedure than a simple select statement</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Can clutter up the database with quite a few simple queries and/or redundant queries</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Additional handling is needed to parse multi-valued parameters in SSRS</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">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)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">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)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Report may break when schema changes</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Difficult to make changes to embedded TSQL</span></li>
<li><span style="font-size: x-small;"><span style="font-family: "Trebuchet MS",sans-serif;">Causes procedure cache to bloat</span></span></li>
</ul>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br />
<u>Embedded SQL in SSRS Dataset</u><br />
<span style="font-family: "Trebuchet MS",sans-serif; font-size: small;">Pros</span></div>
<ul>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Easy (less syntax for a beginner to learn)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">No “Create” permissions needed on the source database</span></li>
<li><span style="font-size: x-small;"><span style="font-family: "Trebuchet MS",sans-serif;">One-step deployment (unless you are using a Shared Dataset, which is stored outside of the RDL)</span></span></li>
</ul>
<div style="font-family: "Trebuchet MS",sans-serif;">
Cons</div>
<ul>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">For large datasets, may not perform as well as a stored procedure</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">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)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">SSRS Query Designer doesn’t retain formatting well</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">SSRS Query Designer removes comments (a big shortcoming in my opinion)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">SSRS Query Designer renames aliases in some circumstances</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">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)</span></li>
<li style="font-family: "Trebuchet MS",sans-serif;"><span style="font-size: x-small;">Need to open the report in BIDS (or Report Builder) in order to make a change</span></li>
<li><span style="font-size: x-small;"><span style="font-family: "Trebuchet MS",sans-serif;">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)</span></span></li>
</ul>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br /></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
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.</div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br /></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
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.</div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<br /></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
References:</div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<a href="http://jahaines.blogspot.com/2009/11/ssrs-should-i-use-embedded-tsql-or.html" target="_blank">Demystifying SQL Server</a> </div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<a href="http://sqlserverpedia.com/blog/sql-server-bloggers/pros-and-cons-stored-procedures%E2%80%93vs-embedded-queries%E2%80%93vs-views-in-an-ssrs-dataset/" target="_blank">Pros and Cons: Stored Procedures–vs- Embedded Queries–vs- Views in an SSRS Dataset</a><br />
<a href="http://sqlblog.com/blogs/greg_low/archive/2008/03/11/sql-server-reporting-services-avoid-t-sql-in-reports.aspx" target="_blank">THE SQL Server Blog Spot on the Web</a><br />
<a href="http://www.blogger.com/goog_1554026660">SQL Chick</a></div>
<div style="font-family: "Trebuchet MS",sans-serif;">
<a href="http://www.blogger.com/goog_1554026657"></a><a href="http://blogesh.wordpress.com/2007/10/20/reporting-services-best-practices-and-tips/" target="_blank">Reporting Services: Best practices and tips</a></div>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-75576404690374112582012-04-11T08:40:00.000-07:002012-04-11T08:40:05.291-07:00Time to start blogging again!<div style="font-family: Verdana,sans-serif;">
Good Day Everyone,</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<span style="font-family: Verdana,sans-serif;">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!"</span><br />
<span style="font-family: Verdana,sans-serif;"><br /></span><br />
<span style="font-family: Verdana,sans-serif;">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. </span><br />
<br />
<span style="font-family: Verdana,sans-serif;"><span style="font-size: large;">My Focus</span></span><br />
<span style="font-family: Verdana,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">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. </span></span></span><br />
<br />
<span style="font-family: Verdana,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">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. </span></span></span><br />
<span style="font-family: Verdana,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><br /></span></span></span><br />
<br />
<span style="font-family: Verdana,sans-serif; font-size: large;">Note</span><br />
<div style="font-family: Verdana,sans-serif;">
</div>
<div style="font-family: Verdana,sans-serif;">
*1ne = one (This is how I Like to write my ones!!! ;)</div>
<br />Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0Cape Town, South Africa-33.9248685 18.4240553-34.346497500000005 17.7923413 -33.5032395 19.055769299999998tag:blogger.com,1999:blog-1717745782632980649.post-35798402137048510812010-05-31T08:17:00.000-07:002010-05-31T13:42:42.130-07:00Whats New in SQL Server 2008<span style="font-family:trebuchet ms;">Greetings guys, </span><br /><span style="font-family:Trebuchet MS;"></span><br /><span style="font-family:Trebuchet MS;">Welcome to my first post. It's not really mine actually but i thought I'd share this valuable article with you. </span><br /><span style="font-family:Trebuchet MS;"></span><br /><span style="font-family:Trebuchet MS;">This article will highlight some of the new features and benefits found in SQL Server 2008. Follow the links below. </span><br /><span style="font-family:Trebuchet MS;"></span><br /><span style="font-family:Trebuchet MS;"><a href="http://www.databasejournal.com/features/mssql/article.php/3691821/Whats-new-in-SQL-2008-Part-1.htm">What’s new in SQL 2008 Part 1</a></span><br /><span style="font-family:Trebuchet MS;"><a href="http://www.databasejournal.com/features/mssql/article.php/3697056/Whats-new-in-SQL-2008-Part-2.htm">What’s new in SQL 2008 Part 2</a></span><br /><span style="font-family:Trebuchet MS;"><a href="http://www.databasejournal.com/features/mssql/article.php/3702381/Whats-New-in-SQL-Server-2008-Part-3.htm">What’s new in SQL 2008 Part 3</a></span><br /><br /><span style="font-family:trebuchet ms;">Till next time!!!<br /></span>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com0tag:blogger.com,1999:blog-1717745782632980649.post-705695176159218552010-02-25T01:26:00.000-08:002010-02-25T02:40:16.344-08:00Welcome<span style="font-family:trebuchet ms;font-size:85%;">Greetings Guys,</span><br /><span style="font-family:trebuchet ms;font-size:85%;"></span><br /><span style="font-family:trebuchet ms;font-size:85%;">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.</span><br /><span style="font-family:Trebuchet MS;font-size:85%;"></span><br /><span style="font-family:Trebuchet MS;font-size:85%;">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!!!!!</span><br /><span style="font-family:trebuchet ms;font-size:85%;"></span><br /><span style="font-family:trebuchet ms;font-size:85%;"></span><br /><span style="font-family:verdana;font-size:85%;"></span>Aquila Hanisehttp://www.blogger.com/profile/10430117569643694457noreply@blogger.com2