Do you want to make 4 thousand dollars a day? Well, I can’t help you with that but I can show you how to enable scale breaks in your SSRS reports. Below I will show you a lesser well known feature within SSRS that only takes 1 click to implement. As you can see from my sample report and data below that Devin and Brian have a significantly different number of fans/followers. Even though this is just sample data I’m sure most of you have had a similar experience when designing reports....
Read More
I had a head exploding moment this morning. While trying to install SQL 2012 onto a VM I recently created and installed all of the upgrades to, I realized that I had not allocated enough memory to it. Thinking that I would have to clone my vhd and set it up again, taking hours out of my night tonight, I was loading the bullet into the proverbial chamber. I created a separate vhd that I was just going to attach to the machine to give me more room, but while researching how to get the VM to recogn...
Pragmatic Works has now released version 3.5 of the award-winning BIxPress software ! For those not familiar with BIxPress it’s a tool that helps you develop SSIS/SSAS solutions faster, easily/quickly deploy SSIS packages, monitor performance SSIS packages and much, much more ! So what’s new with 3.5 ? In addition to now having SQL Server 2012 support (SSIS), you may have noticed in previous released that Reporting Services didn’t get much love. That all changes with this relea...
I had an "interesting" problem yesterday. When creating a shared dataset, I made a "noob" error and added a filter so that it would only select rows that had "null" in one of the columns (not knowing at first that you can't filter on Null). Unsurprisingly, when I ran the report, no rows were returned at all. After removing this filter from the shared data set, it still didn't work. My head received enough scratching for the next few months. Now, at this point, if I actually ran the report rather...
The reason for this blog post, is I recently had a query if it was possible to get data returned from an MDX Query and then insert the data into a SQL Server Table. And the reason for putting it into SSIS was that we could schedule the job to run on a schedule. I have inserted pictures from my own documentation to save me some time publishing this post. The reason that I also like using the ADO NET Source is that you can use Expressions for both your ADO NET Source and Destination. For my requir...
I had the pleasure of attending the Jacksonville SQL Server Users Group (JSSUG) meeting last night. The subject was “Indexing your Data Warehouse”, presented by Troy Gallant ( Twitter | Web ). It was a great presentation, and as someone only a couple months experience in the SQL community, very informative. Proper indexing of your data warehouse can help greatly reduce the time and resources it takes to properly query your database. Using clustered and non-clustered indexes, the DBA ...
I found two simple ways to dump MDX query output to a table and then dump the table data to excel file or text file as per need. The two methods are: 1. Using OPENROWSET Syntax : OPENROWSET('provider_name', 'datasource'; 'user_id'; 'password', {[ catalog. ] [ schema. ] object | 'query' }) Connect to the relational server using windows authentication(important!). Example: SELECT * INTO zzz_temp_table FROM OPENROWSET('MSOLAP','DATASOURCE=OLAPServername;Initial Catalog=Cube1_DEV;', 'MDX Query here'...
Many of you may now or in the future have the need to upgrade your SSIS packages to the new SQL Server 2012 SSIS. Luckily, upgrading from SSIS 2005 or 2008 isn’t nearly as painful as you may have experienced upgrading old DTS (SQL Server 2000) packages. If you’re considering the upgrade to 2012 here are some of the new features you have to look forward to: Usability fixes in the development environment (Resolve References, Undo, Zoom, etc…) New/Changed Tasks and Transforms (CD...
I use sharepoint 2010 to display the data analysis tool I have created in excel 2010 to manage the Microsoft licenses as well as to utilise the excel 2010 capabilities of slicers and dicers. This has been going pretty smoothly until yesterday when I stumbled with this error below. "Workbook is larger than the maximum workbook size" So I had to go through the Excel web services properties on the sharepoint server and increase the maximum file size. Here are the steps that I followed. Logged into ...
This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business i...
I often use Visio diagams for architecture, and not having a shape for a SSAS server was always annoying. I'd built one out previously, but never took the time to polish it (which mostly meant make the text editable....) Now that I needed one for Tabular mode servers as well, I took the 20 minutes to fix the editable text. Thought this might be useful. You can download the Visio Stencil here http://www.bidn.com/Assets/Uploaded-CMS-Files/OLAP_Servers-4b288bf1-3b87-4758-be85-140693b0034d.vss If y'...
With Sharepoint rapidly becoming the data store of choice for user driven and user built data capture systems, getting this data into your warehouse is becoming more and more important. There are three primary methods of doing this from SSIS. 1. Install 3rd party Sharepoint List Adapters. CodePlex even has a free version at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 2. Write your own adapter. Again, the CodePlex project is a great start 3. Create a script component to query Sha...
Another SQL Rally has wrapped up in Dallas, TX and it was great event. My favorite part about these larger events is being able to meet new people and see old friends that I haven’t seen for months. Unfortunately, I did not attend any of the precons this year but I did see a lot of great sessions. I made it a point to at this event to reach outside my comfort zone with the types of sessions I attended. Here’s a wrap up of my couple days in Dallas: Thursday I started my morning by att...
Aggregating over Lookups in SSRS SSRS 2008 R2 has a feature to do a lookup to another dataset, but no way to trivially aggregate the value. It’s a fairly simple solution: Use a small piece of code to do the summing. Now, you may have various LookupSets, so you don’t want to code a function for each, so we store the values in a dictionary lookup. Then when you query at the sum level, you just query the dictionary Dim dValueList As New System.Collections.Generic.Dictionary(Of String, D...
The problem: An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: “VIOLATION OF UNIQUE KEY CONSTRAINT ‘ Your Column’ . Cannot insert duplicate key in object ‘Your Table ’. The duplicate key value is ( the duplicated value ).” My solution: When loading ...
Thanks to everyone who attended my session at SQL Saturday #130 in Jacksonville, FL a couple weeks back. I apologize for posting this so late, but better late than never. To download my session materials, just click this link . In it you'll find my PowerPoint slide deck and the MDX script I used in the class. If you have any questions about what we went over or any questions regarding the materials, feel free to leave me a comment or shoot me an email. Thanks again to all those who attended... e...
In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is ?Can I duplicate an environment??. There is a move feature already included. But this moves the environment instead of copying. Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can scri...
Today I was working in SSAS Tabular Mode for SQL Server 2012. What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report: Microsoft .Net Framework 1.x Microsoft .Net Framework 2.x Microsoft .Net Framework 3.x Microsoft .Net Framework 4.x I would have one name instead: Microsoft .Net Framework I was using the DAX Left Function , and nested inside the Left Function I was getting the count of Characters to find out where the ".x...
One of my favorite expressions is a time string expression. I use it all the time when renaming files. In a recent MSDN question someone asked how to create a time string that used plain time, not military time (24 hour clock). I thought that would be easy and was going to tell the writer to find it for him/herself. But in trying to put it together, I found it rather challenging: (DT_WSTR,24)((DT_WSTR,8)((10000 * YEAR( GETDATE())) + (100 * MONTH( GETDATE())) + DAY( GETDATE())) + "_" + RIGHT(REPL...
Last week I looked at how you can configure the Expression property of an SSIS variable ( http://www.bidn.com/blogs/RussLoski/ssas/2688/configuring-variable-expression ). I could build a variable with an expression and then configure the formula that I use for that expression in an XML configuration file or even an SQL configuration. Why stop at this? Why not see what else can be configured? In this blog I examine how to put the expression used in a derived column into the configuration. Here is...
So far in my quest to become involved in SQL Server BI, I have been looking at my options in the field and becoming a BI architect seems quite appealing to me. Of course, being a relative newbie to SQL, I know I would have a lot of work to do to attain this goal but I'm not deterred at all. I believe that the best way to learn anything is knowing what you want and then creating a plan and goals around it, which is why I'm now looking into MS certification in BI. Yes, as a IT Pro I'm very aware o...
I've joked in the past that the data miner is to the BI analyst what the weather person is to the news reporter. One reports on the past, and the other predicts the FUTURE! Pretty cool when you look at it that way. So let?s do something meaningful today that can help our entire nation. Let's forecast the unemployment rate using the Microsoft SQL Server 2012 Data Mining Add-ins for Office 2010! I started by downloading and installing the FRED Excel add-in which you can find here . I pulled the un...
In the fast paced world of business intelligence, there are many tools available to the user manage one’s data and make the moves necessary to stay ahead of his or her competitors. It’s safe to say that these tools did not materialize out of the ether, and they certainly were not created by some cyborg master program. They were all created by people with years of programming experience. However, a part of the development process that doesn’t often receive enough praise is the Q...
Edit: This is not new content. I've reposted this with corrected image links. In my previous blog post, I showed a row-by-row solution to creating an excel workbook dynamically using SSIS and populating one worksheet per row of data. Then "studio72" asked if this method could be adapted if the test data "users" had more than one row of data in the resultset as follows: Name | Age | Job John 25 IT John 34 ENG Anna 21 HR Anna 24 IT Mary 20 MFG I want the SSIS to create one excel file with sheets f...
I am in reporting team and once had this requirement. We had one table where Seconds were stored as INT and in report we wanted to convert the seconds to HH:MM:SS format. We already had 1 solution ready with us. It was something like: --------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT SET @Seconds = 3800 SELECT CONVERT ( VARCHAR (10),@Seconds/3600) + ':' + RIGHT( '00' + CONVERT ( VARCHAR (2),(@Seconds%3600)/60),2) ...