OLAP PivotTable Extensions is a really cool free add-in for Excel that’s been around for quite a while now. It allows you to have some exposure to what’s going on under the covers (Among many other features) while you browse a cube using an Excel PivotTable. This weekend there has been an updated version and you should check out the features and download it now! http://olappivottableextend.codeplex.com The new features in this release are: Support for Excel 2013 Upgrading to the AS20...
Read More
Recently, another colleague came to me again with an issue where BIDs would not open a report in design mode. Instead, the following block of text greeted the developer: <html><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"><title></title><style>.ErrorStyle { font-family: tahoma; font-size: 11 pt; text-align: left}.DetailsStyle { font-family: tahoma; font-size: 10pt; text-align: left;text-indent: 0; word-spacing: 0; line-height: 100%; ...
SELECT * FROM…when administrators see that from developer code, we generally tend to cringe. Why? In a nutshell, it’s terrible on a few fronts. First, typically that SELECT * FROM that gets written (a lot of the times) lacks a WHERE clause. What’s the problem? Well, that pulls back every single row from the table. Sure, that may not be too bad on a table with a few hundred rows but what about on a table with millions? That could cause a lot of performance problems since you&rs...
I recently decided it was time to figure out how to set up a Data Tap, one of the new features of SSIS 2012. I found it wasn’t difficult to do at all so I thought I would share what I learned with you. First of all, let’s review just what a Data Tap is. A Data Tap is an output of the data from one of the data flow paths in your package. Data Taps are a lot like Data Viewers. You can add Data Viewers in design mode while working with the package inside Visual Studio (VS*) as a debuggi...
This totally killed me for longer than I'll ever admit. I sat with DBAs running profiler traces, looking at execution logs, and running various tests. Turns out it's Reporting Services 101 (someone else's words, not mine.) As soon as my colleage said the words "Interactive Page Size" I slapped myself in the forehead. Anyway, I wanted to share this so no one else goes through what I went through. Symptoms: Report takes too long to render or never renders. Data retrieval through stored procedure o...
Color expressions in SSAS allow you to build an MDX expression to control the color of text displayed in a calculation. This property can be found in the Calculations tab of the cube editor when working in BIDS. Simply select a calcuation and look for the section labeled Color Expressions between the Display Folder and Font Expressions in the Additional Properties. Simply enter a condition in the box as shown above when the results of the condition being the colors you would like the text to be....
When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together. Those functions were the Lookup Function , LookupSet Function and the MultiLookup Function . My general recommendation for these function is not to use them when you have two dataset from the same data source. They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my s...
As you work with Integration Services, you may encounter a situation where a little organization in your SQL deployed packages would be helpful. One of the easiest problems to mitigate in the BI world is an unruly mess of packages. An easy way to organize this process would be to create new folders on the same level as the MSDB folder. This is done by modifying the xml in the configuration file. This will bring you from here: To here: There are two ways to do this: You can either create a comple...
I hope that you have had a chance to see a demo or two of 2012 SSIS. While this is a major overhaul of SSIS, it is not a completely new product as we experienced with the replacement of DTS by SSIS. You won't have to start from zero again. With 2012, there are many features that will make developing, deploying, monitoring and troubleshooting your SSIS packages much easier. But you might have a big issue affecting many shops: for whatever reason, you won’t be upgrading to 2012 any time in t...
The preferred T-SQL syntax for joining tables is by using JOIN. The alternate syntax, the comma join syntax, is still being used out there. In my opinion, we should all forget about that old method and embrace the JOIN syntax. Here is an example of the comma join syntax: SELECT pers.FirstName, pers.LastName, p.Name, p.ListPrice FROM Sales.Customer AS c, Sales.SalesOrderHeader AS soh, Sales.SalesOrderDetail AS sod, Production.Product AS p, person.Person AS pers WHERE p.ProductID = sod.ProductID A...
Blog: http://hardik-bhavsar.blogspot.com/ When creating an efficient and robust solution for Extract, Transform and Load Process, Lot of things do matter especially when you are working with huge size of databases. Challenge is not only to meet functional requirements Also to look at the performance requirements. Here, I would like to cover most of the part on extracting and loading process, I will not discuss (Buffer usage, Execution tree, Parallelism etc.) You will find those details here: htt...
My Blog: ( Link ) Errors can occur at any stage when you extract data from source and load it into destination. You must include error handling method for your package to test and troubleshoot. There are several ways to handle it at different levels during your process. Either you can use default features provided by SSIS or you can develop your own method to handle the error. Ex: at a control flow level, if an error occurs, you can add a failure constraint and redirects the workflow to an alter...
August 4th, SQL Saturday in New York City. 48 amazing sessions by some of the most respected people in the SQL Server community. I have my ticket, hope to see you there. http://www.sqlsaturday.com/158/eventhome.aspx
In Part 6 of this Master Data Services blog series we will look at how we enforce quality standards and ensure accuracy in our master data by implementing business rules. In the prior parts to this series, we have spent time reviewing important Master Data concepts and MDM architectures. We also looked at configuring MDS before learning about the model, entities, attributes and members. In our last post we looked at derived and explicit hierarchies before being introduced to collections. Series ...
If you follow anything outside the regular SQL Server world coming from Microsoft it should be Office and more importantly SharePoint. The two follow the same release cycle and as of today (July 16th actually) they have released a public beta for Office and more importantly SharePoint 2013! If you are interested in the SharePoint 2013 preview you can head over here ( http://technet.microsoft.com/en-US/evalcenter/hh973397.aspx?wt.mc_id=TEC_121_1_33 ) and grab a copy of it. The download weighs in ...
The preview for Office 2013 is live!! You can hop over to the Microsoft website and grab it here: http://www.microsoft.com/office/preview/en/try-office-preview . Naturally I am eager to see all the BI features in both Office and SharePoint. If you have ever used PowerPivot then you know how cool the technology is. Well, with Office 2013 it gets even better. If you recall, PowerPivot WAS an add-on for Excel 2010 available for download at PowerPivot.com. Well, in Office 2013 that all changes; Powe...
I’m not exactly sure when this happened, but it looks like Microsoft has moved past the PowerPivot 2008R2 and PowerPivot 2012 model and is now just offering PowerPivot on their website. Previously there were two options as I just mentioned with the 2012 version being labeled RTM. Now that the 2013 previews of the office suite are out there it looks like Microsoft is officially pushing PowerPivot 2012 on everyone and no longer offering the download for 2010. Ultimately this is a good sign a...
If you are using Change Data Capture (CDC) it can be very useful to look at the CDC State table to understand what is happening. I am writing this in the context of using SSIS 2012 CDC components; in this case the CDC Control Task. The CD State can be very helpful to determine why data isn’t getting loaded right after an initial load for instance, which is when I decided to dig into the state shown in the state table. The CDC state is used to maintain the state of the loading process. It i...
Change data capture was introduced in SQL Server 2008 and has been pretty popular ever since. The basic concept behind it is that all changes (inserts, updates, deletes) are captured and the details made available in a consumable manner. This means ultimately that all changes will be written to a table you can query to see everything that has been going on. In SSIS 2008 it was a little cumbersome to maintain and get data out of the CDC tables. SSIS 2012 has made that much easier with the additio...
It’s about that time again, PASS Summit 2012 is right around the corner in Seattle, WA. It will be held at the convention center from November 6-9. This year will feature more than 170 sessions across 6 tracks from 90 speakers in addition to 2 days of pre-cons; and that doesn’t even include the sessions from Microsoft! I have spoken at a lot of SQL Saturdays, code camps and done a handful of webinars for Pragmatic Works over the last couple of years and decided to take a shot and sub...
With the Office 2013 announcements today I was excited to download and get my hands on the tools. Because this is still a customer preview we’re bound to find things here and there that aren’t functioning as expected when experimenting with the Office tools. The things I had on my list to test first were Power View and PowerPivot from Excel (Yes, Power View is now available in Excel). In this post I’ll focus on helping you get started with PowerPivot. With Excel 2013 you no lon...
Crystal Reports has a special field name ?DataDate?. This date shows the last time the data on the report has been executed. A recent client asked for the similar functionality in SSRS when a snapshot is run. They wanted to see the actual date for to be used as a print date but they wanted to have the date the data was pulled for the report. This can be accomplished with the build in field in SSRS named ExecutionTime. This will return the date the report ran last even when the snap shot is pulle...
This has been reposted from a guest blog post I wrote on the Microsoft Business Intelligence team blog . Fulfilling User Needs Developing a Power View report has been touted as being so simple that within a handful of clicks you can add rather impressive elements, like effective visualizations, to a report. This isn’t always the case with other tools in the market. The goal of this post is to walk you through Power View as if you were designing a report for the first time, and show you how...
One of the features of SQL Server 2012 is the Sequence Object. This can be used to generate sequential numbers, in some ways similar to an Identity column. One big difference is that the Sequence Object is an object that can be used to provide values to multiple tables. You don’t associate the object with tables, the Sequence Object is just a container, or a bucket, and you can do whatever you need to with the numbers you pull from it. You use the Next Value For function to pull the number...
I have several speaking events coming up, and I would love to see you at one or all of them! First of all, I'll be speaking at SQL Saturday #122 in Louisville next week. I will be involved in three sessions: Features of 2012, 2012 T-SQL Features, and the Women in Tech Panel. Louisville is such a great location if you are in the eastern half of the country so think about joining us! On August 4th, you can catch me at St. Louis Days of .Net speaking about 2012 T-SQL Features. This event has grown ...