Tomorrow (4/30/2011) is SQL Saturday #74 in Jacksonville Florida and once again, I am giving a talk with other well known SQL Server Guru’s. Register for the event right now…Pack up your computer tomorrow morning and head over to UNF Campus at 8:00 AM tomorrow. You will get some awesome free (YES I SAID FREE) training from well seasoned presenters like Andy Warren, Scott Gleason, Tim Radney, Kendall Van Dyke, Kevin Boles, Mike Mollenhour and nearly ALL of the Pragmatic Works team. My session tom...
Read More
Hello everybody! I wanted to show you how you can quickly sort your column headers when you bring back data that has the Month Name in your column groupings and your product name in your row groupings. Pop quiz….When I mention row and column groups, am I referring to a Tabular or Matrix style report? Wait for it….wait for it….wait for it….OK…. you got it, a Matrix style report. So what I am trying to produce is a report that will display the Products and how much were sold on a monthly basis. I ...
Since I started working at Pragmatic Works I have noticed a definite increase in the adoption of SharePoint by many of our clients. One of my goals for this year is to really get deep into SharePoint and all the BI tools it offers. The two technologies I’ve really been focusing on the last few months have been SharePoint and SSIS, thanks to some really great internal project I got to be involved with. It’s one of the great things about working for Pragmatic Works, not only do I get to learn abou...
Chris Webb has spoken about "implementing common business calculations in DAX" during SQL Server Day 2010 in Belgium. Here is the link for uploaded video. Implementing Common Business Calculations in DAX DAX is Microsoft's new multidimensional calculation language. At the moment it's only used in PowerPivot, but in the future it's likely to have an important role in other corporate BI products from Microsoft, so it's something that all BI consultants need to learn now. Relevant Links: DAX Functi...
While working on-site with a client recently they informed me of an issue they had recently encountered. The issue was that they had created a series of reports that used an SSAS cube as a data source in there reports. The reports were deployed to production but were still pointed to QA and the data was so close to production they didn’t realize this until a month later. Fortunately for them another department was in charge of changing the data source so they could defer the blame a little but t...
When I came to work for Pragmatic Works, I was naturally given the opportunity to use their (well our now) tools. Of all of them I think BI Documenter is my favorite. Boy is this thing complete. Of course, like some of the other SQL Server documentation packages it will do a great job of reverse engineering an existing relational database. BI Documenter will output either HTML or a compiled help file (CHM) file. It places your database structure into a drill down tree, with all the expected bits...
SQL Saturday 74 in Jackonville is coming up this Saturday, April 30th. It's a whole day of free SQL Server training given by an amazing community. It will be held at UNF and is open to everyone that wants to attend. Did I mention it's free? There is a range of lectures all throughout the day on a myriad of topics ranging from beginner to advanced topics. I would encourage anyone to attend even if you are just curious about SQL Server. Honestly, it was the local users' group and SQL Saturday that...
Here is list of all DAX Functions described in my posts. Please click on links to reach DAX Functions post: Time Intelligence Date and Time Statistical Filter and Value Logical CLOSINGBALANCEMONTH DATE AVERAGE ALL AND CLOSINGBALANCEQUARTER DATEVALUE AVERAGEA ALLEXCEPT FALSE CLOSINGBALANCEYEAR DAY AVERAGEX BLANK IF OPENINGBALANCEMONTH EDATE COUNT CALCULATE IFERROR OPENINGBALANCEQUARTER EOMONTH COUNTA CALCULATETABLE NOT OPENINGBALANCEYEAR HOUR COUNTAX DISTINCT TRUE DATEADD MINUTE COUNTBLANK EARLIE...
SQL Saturday #74 Jacksonville April 30, 2011 SQL Saturday Jacksonville is this Saturday at UNF. It’s a great venue with a lot of great speakers. Don’t forget it’s FREE and includes a FREE lunch. Come on out for a great time and improve on your skill set. See you there. For more Info click here.
There will come a day, if it has not already come and gone, when you will want to search through your stored procedures because you don't remember which one did what you want to do. Did that make sense? Because I don't know if it did. Anyway, here is what we have... You A bunch of stored procedures Something you are looking for inside one or more of your stored procedures A long night ahead opening each one and using CTRL + F Luckily there are a couple of super easy solutions. Below are two such...
Imagine you have spent the last 9 months developing a sales Data Warehouse and an Analysis Services cube. Your end users love what you have built for them so far but it’s never good enough right? You have a new requirement to allow your users to enter in projected or target sales for future months. You think about your options for a while and narrow it down to 3 possible ways to solve the problem. Option 1 : Have the users enter the targets into an excel spreadsheet and then import the data into...
We use SQL Agent jobs for a lot of different types of tasks. Some do heavy lifting and ETL, some run and email basic reports, some simply manipulate data according to a schedule. However with all these jobs there is an occasional need to find object references in the jobs to make sure changes we are making in a database are not going to cause a series of problems and bad data. In order to facilitate this search I came up with a very simple script to search the command text for all the steps to a...
I recently encountered a problem (again) where I have a query for a report that has two different levels of aggregations of numbers. I know, tisk..tisk..tisk, but in my world this happens with a fairly high frequency. Dealing with these different levels of aggregations in a report is a real problem, with no good solution within SSRS, at least that I have found. But, since this is something I bump into from time to time, I thought I should figure out a way to deal with it that I can use over and ...
If you’ve followed my blog, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out . Anyways, on to the puzzle! As always, these MDX queries were written against the Adventure Works DW. Here are the requirements: Rows: Calendar Year Columns: Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist) First Date in Year Hints: Take a gander at this . And here’s a sneak peak at my results: If you t...
On a recent reporting project I was assigned to create a Project Management Dashboard. Part of the specs for the report was to be able to click on the Project Managers name in the dashboard that would launch Office Communicator (OCS). In order to achieve this functionality a couple of items needed to be determined. 1. You need to know the SIP of the Project Manager 2. You need to have the SIP stored in your data somewhere. Or at the very least the emails of the Project Manager so that way you co...
Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or...
KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the proper level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube and trend at the month level...
KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is above or below a goal. Managing the goal numbers can be a headache because they made need to be updated often. By placing the goal numbers on a table you can dynamically keep the goals up to do by simply updating the table. The following example uses the adventure works data warehouse. You will need to create a new table. Here you can see one named KPI Goals and the data on it. This table con...
Here at Pragmatic Works, we are not just a bunch of great consultants! We create software too! Some that helps organizations with migrating packages from DTS to SSIS . We also have some pretty wicked software that saves time documenting packages, cubes and databases. Furthermore, we have components and tasks that add functionality to SSIS and a robust auditing and notification framework . All of which are great investments for any organization that uses the Microsoft BI stack of products, but ch...
Did you know that Pragmatic Works is giving away free training for SSIS or SSRS? And no I’m not talking about our webinar series but our actually 4 day virtual training class. That’s a $800 value. Check out some of our upcoming classes here. The next question is how do I get the free training? The answer to that is easy. All you have to do is buy 1 of our software products by the end of April 2011. Check out our Products that make your job easier here. Again, all it takes to get 1 seat (1 seat p...
In PerformancePoint, sometimes you want to override the date selected for the time intelligence formulae : for instance, you’re doing calculations for a rolling 3 months, and you want to change it to be rolling 3 months as at a certain date. So this is definitely possible, if not entirely intuitive. The key is to override the current date time. NB: This technique can’t be used when overriding hierarchies in rows or columns To start off, lets create a data connection to Adventure Works. Make sure...
As you've probably noticed, Pragmatic Works is once again running another promotion. This promotion is a little different though in that it's probably the best deal we've ever had on our software/training bundle. If your company purchases at least a single, lonely license to any of our incredible software, inlcluding Task Factory , BI Documenter , BI xPress , and/or DTS xChange , and you get a free seat in either our SSIS or SSRS class. The really great thing about these classes is that you can ...
This week, there is a great promotion at Pragmatic Works. If you purchase any of our software, you can get a free seat in either a SSIS or SSRS class, and I know how much you would love to attend one of my SSIS classes. This is an outstanding value that is too good to pass up! I encourage you to check out the Pragmatic Works products page . We have software that that will simplify every aspect of the BI stack for you. Everything from building your SSIS packages for you, to custom components to s...
My Favorite BI Tool Gets Better I recently had to make a significant number of changes to some connection managers and was glad to be able to use the new Package Configuration Wizard tool in BIxPress from Pragmatic Works . If you have worked with configurations for any time you know that it can be a little cumbersome to make sure you get everything right. And then there are the inevitable changes that have to be made that always seem to break your configurations. The new Package Configuration Wi...