This week's puzzle used a couple functions we learned about a few weeks back . To solve the Sixth Sense puzzle, you should have used the TopCount function (or the Head and Order functions) and the Generate function . So lets start off with a basic query that gives us the Internet Sales for the five states with the highest Internet Sales Amount by using the TopCount function . SELECT [Measures].[Internet Sales Amount] ON COLUMNS, TOPCOUNT( [Customer].[State-Province].CHILDREN, 5, [Internet Sales ...
Read More
SQL Server Analysis Services - 2 day Workshop - January 19th & 20th 2011 Register before December 31 st for an Early-Bird Discount! Microsoft and Pragmatic Works would like to invite you to a special, two-day workshop on SQL Server Analysis Services taught by SQL Server MVP Brian Knight – January 19 & 20th . In this event, you will learn about cube development, MDX, data mining, and many more topics intended to help you get the most out of Analysis Services. We are pleased to be able to ...
After earning my MCTS certification a few months back, I jumped right into prepartion for passing the 70-452 certification exam (MCITP). Shortly thereafter in mid November, I earned my MCITP certification. The 70-452 exam builds upon the technical skills tested on in the MCTS exam. Basically the MCITP exam validates that you have the skills necessary to perform the role of a database administrator or enterprise messaging manager. According to Microsoft, the MCITP candidate can "make the design a...
In a previous article I showed how you can create dynamic security in SSAS. This is a great way to make all security table based using Active Directory accounts associated with the items that the user is allowed to see. If you decide that your security model is pretty basic and it is not necessary to implement the table based security then you should be aware of a side effect of having multiple Analysis Services roles. The side effect I’m referring is having a user that falls under multiple role...
This week's MDX puzzle builds on one we had a few weeks back , so check it out. I know it's been a while since I gave away a prize for a correct answer, but this week I'll be giving away another Kick'n SSAS t-shirt! Now on to the puzzle! Rows: State/Province Products Columns: Internet Sales Amount Filters: None Hints: Show the top 5 states or provinces with the highest Internet Sales Amount. Show the top 10 Products with the highest Internet Sales Amount for those 5 states or provinces. Check th...
For everyone still working today, scheduled to work tomorrow or even over the weekend, and even those lucky enough to be off today. I wish you a safe and joyous holiday season filled with family, friends, and fun.
For those of you who are using sql server 2005 the service pack can be downloaded from the microsoft site . Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP4 . You can use these packages to upgrade any of the following SQL Server 2005 editions: Enterprise Enterprise Evaluation Developer Standard Workgroup SQL Server 2005 SP4 includes SQL Server...
Hi All, Was working on the main consulting page for pragmaticworks.com , and I had to design a control , which will rotate and display images and bios of principle staff at PW. Just did some research to find a Devexpress Ajax control, which worked exactly in the manner I wanted it to work. So let me just begin with the defination of Ajax for all the newbies in the field of Development "Ajax (shorthand for Asynchronous JavaScript and XML ) [ 1 ] is a group of interrelated web development methods ...
I have found often when teaching SSIS to others that it can be extremely confusing when you first encounter SSIS data types. At first glance they seem to be nothing like SQL Server data types you love and know. That's why I've provided below a conversion chart of SSIS data types to SQL Server data types. This information is readily available on MSDN but it always seems difficult to find. Hope this helps! SSIS Data Type SSIS Expression SQL Server single-byte signed integer (DT_I1) two-byte signed...
I was looking for a way to combine text from multiple rows into a single text column. There are several ways I found to do this but here is a way using FOR XML PATH. First the reason: My source data comes from a transactional DB that users enter text descriptions in multiple transactions. Here is mockup: Text TextFamily TextGroup TextSequence This is a sample text line. C 1 1 This is another line which A 1 1 is followed by the next line that A 1 2 may roll over to the next, but A 1 3 should be r...
So if you know me via Twitter or my blog you probably know that as of about a month or two ago I joined the fantastic team at Pragmatic Works . This transition was rather significant for me as I would be moving from the world of administration to the development side of SQL Server in being a BI consultant. This series will be a kind of chronicle of my personal journey through the transition from a DBA (with a social media complex) to a BI developer in one of the top BI shops around. My Backgroun...
This is one that gets me just about every time I start new project... I'll have a table that I created days before, and wouldn't ya know it, I left out a column or two. Then I right-click on the table and select "Design" and insert the column. And I when I want to save my changes, I get the following message: The Management Studio default is to not allow you to make changes to a table that require you to drop and re-create that table. So all you have to do to disable this setting is, go to "Opti...
This one may seem like a no-brainer, but it's probably the most asked question I get while I'm on web meetings with clients. I hear it so often that it makes me wonder why it's not enabled by default. And that question is, "how do I get line numbers on my query windows?". Well, it's just one simple setting... Start with the "Options..." box ...and open the "Text Editor" tab and select "Transact-SQL". There, you will have to click the check box next to "Line numbers" Click OK, and then... Voila! ...
In this blog post I am going to attempt to show how to create a quick and dirty C# class library as a simple way to provide end-user customization across reports. The sample I will use will be to facilitate Name formatting but this can extended to suit whatever needs you have. For example, we also use it for address formatting, custom encryption/decryption algorithms, .ToUpper/.ToLower functionality and HTML character stripping, to list a few. The first step will be to launch BIDS and create a V...
I have seen this post by Brian K McDonald about Being Postive. This post has inspired me to write this post about all the good that happened to me in 2010 in the database and BI world. Here is a list that I put together. This is what I consider the top most as obviously I cannot list everything. The SSIS webinar that I attended by Patrick LeBlanc because of which I have registered onto BIDN on 23rd Feb 2010. I have started learning a lot reading the blogs and articles on BIDN of Brain Knight , A...
The dreaded table lock can occur and cause your SSIS packages to fail. A popular request I receive asks “How can I get rid of these table locks?” This blog will show you how to build a package that will kill any SPID’s that are running on your system that could be locking a table. Note: Be careful using this technique, you could kill a critical process. In this package you will have five variables. objSpids = Holds the data from sp_Who2 strDatabase = Name of the database to look in Spids strSpid...
SQL Stadium will be crowded with anticipation very soon in Tampa. Come watch as Adam Jorgensen takes on a challenger from the SQL community in a BI Head to Head showdown! Iron chef was born here last year and this event is a very special homecoming for those who have waited all year to see it again. Make sure to come check it out at SQL Saturday 62 in Tampa FL on Jan 15th! See you there!
On January 15 th , 2011, SQL Saturday is once again coming to Tampa Florida. This is going to be a tremendous event and you don’t want to miss it. There are also two all day sessions by Denny Cherry and Stacia Misner the day before. Regrettably, I am not able to make the full day sessions, but I will have an opportunity to get in front of you all to talk about SQL Server Reporting Services 2008. Although I am unsure what time that I will be presenting, here is the abstract that I submitted for m...
If you’re anything like me, you enjoy constantly striving to new levels of knowledge. This new level of knowledge brings satisfaction, which in turn breeds new goals and objectives to meet. This always moving forward mentality can be very difficult, but the satisfaction gained can be well worth it. One thing is for sure… if Pragmatic Works becomes the place for you…you’ll push yourself harder than you’ve ever been pushed. Many moons ago, I enjoyed being one of the Few, the Proud, the Marines. An...
I'm going to be making a concerted effort to blog more going into the new year, so I will begin doing small little posts when I come across something I think others might find useful. The backstory: We have several reporting projects to organize reporting across various functional areas of our product suite. Today, I came across a situation where I wanted to use a report from a different project as a subreport. The problem: The drop down box for the report selection will only allow you to pick f...
Come see two Database Powerhouses go Head to Head in an ETL challenge. Mike Davis and Aaron Nelson Square off in the SQL Smackdown. Mike Davis will use SSIS, Aaron Nelson will use Powershell Both men will show what pros and cons of each method in ETL Who will win??? Who will get hit with a steel chair??? Come see them at SQL Saturday Tampa on January 15, 2011.
I spent way too much time today troubleshooting a deployed report that was failing. It worked just fine when I ran it in Visual Studio. Normally when I run into a problem like this, the first thing I do is to check permissions. That is what I did today, but it was a simple query, so I checked everything manually. Big mistake. I was confident that I had checked permissions and security issues, and I proceeded to pursue other possible issues and resolutions. Unfortunately I was confidently wrong. ...
I got interested in the title of this webinar and hence thought that all of you might be interested in this webinar event as well. Below are the details Date / Time Date: 12/20/2010 Start Time: 12:00:00 PM End Time: 1:00:00 PM Timezone: (GMT-05:00) Eastern Time (US & Canada) Short Description Building dimensions using the Slowly changing dimension wizard in SSIS is simple and quick. However its performance and flexibility is questionable. Even further, when trying to perform incremental load...
Great day, more cool stuff. I'm begining to see a glimpse of the capabilities of SSRS, and I can see many ways to directly affect the bottom line of a business, using these technologies. My previous impression of "reporting services" was limited to the types of reports / views / groups, that I see in my contact management system for my business. The following script has been excecuted and will be reexecuted daily as a stored procedure, from all of us in Foundation Class to all at Pragmatic Works...
Reporting Servicing I learned an expression today, =iif(rownumber(nothing) mod 2, "Tan", "White") That's used to alter colors on the rows. We drilled on writing statements today and running reports from our stored proc. This help on cover many of our weaknesses, Great job Sherri M. on helping use putting it all together, when doing this with style. I hope to have time to ask about converting function, this is also an area where I hope to improve in, thanks Brain, good looking out.