This week has been an exciting and memorable week for me. The last PASS Summit I attended was in 2009 and I missed it so dearly. I meet a lot of people that I have only chatted with online but had never meet and others that were completely new to PASS and our community. For those of you that have never been to PASS I’m hoping hearing about my experience will get you excited to come next year. With obligations at home unfortunately I wasn’t able to stay through the final day of the ev...
Read More
The latest project I worked on had a heavy dose of PerformancePoint and one of the requirements was to launch Analysis Services Actions from PerformancePoint dashboard objects. I knew this was possible but did not no all the limitations that exist when you want to do this. Most of the available post reference PerformancePoint 2007 so I thought it was time to update the topic. I mentioned there are a lot of caveats to doing actions in PerformancePoint so I came up with this quick flowchart to fir...
I apologize ahead of time on this post today because it will require lots of pre explanation and screenshots to set the stage for what I want to show you! Often when building a report with multiple row groupings I like to change the default way SSRS places the fields on the report. Default multiple row groups: While this behavior is fine it can lead to a lot of useless white space when browsing the report. . So what I like to do is still have the three groups by eliminate all that dead space. Of...
PASS Summit is quickly approaching and while many of you are registering I wanted to remind you of some of the great Pre Conference seminars that are available (more specifically mine!). On Tuesday, October 11, 2011 Brian Knight and I will be presenting on building a BI Solution from the ground up in our seminar called ?Building a Microsoft Business Intelligence Platform?. Brian and I have presented several times together on the topic and we would love to have you join us for a full ...
Recently while working on a different solution for loading inferred member solution in our fact table load I ran into this article on SQL Server Central article. This article explains very well what an inferred member is. http://www.sqlservercentral.com/articles/Data+Warehouse/71233/ This is a great article and I started using it but found the need to have some modifications. The client I was working with rewrote the script component to fit these needs so with his permission I thought I would sh...
Today I will be presenting in a webinar, which by the way is recorded for later viewing, on Commonly used DAX Expressions. You can register (or if you miss it live watch the recording) for this webinar here . I thought it would be great if I could push out the code that I plan to show ahead of time so you could review it immediately following or even during the webinar. Also, if I run out of time you can see what I planned to cover! Some of these have a short description while others are self ex...
Probably one of the most anticipated additions to the latest version of PowerPivot is the ability to create KPIs. PowerPivot has made what was once a task that was done purely done by developers now something that anyone can do now. KPIs (Key Performance Indicator) are an executive favorite that help provide a quick at a glance look at how one area of business is doing. Typically they output some kind of indicator like a red, yellow, green light of some kind that can tell the report viewer quick...
PowerPivot – Perspectives Many times when developing larger Analysis Services cubes you may find it necessary to use Perspectives to logically split up your cube so users can find what they want faster. A perspective is similar to a TSQL view because it will take a larger object (the cube) and filter out objects that you want to split up. It cannot however perform business logic or filtering of data like a view can. So the case for perspectives would be if you have a cube for your entire company...
Picked up this nice script from a client last week. Use this script below to create a Date Dimension for a data warehouse that is based off of computed columns. The only column that needs to be populated is the CalendarDate column. CREATE TABLE [DimDate]( [DateKey] [int] IDENTITY(1,1)NOT NULL, [CalendarDate] [datetime] NULL, [CalendarYearNumber] AS (datepart(year,[CalendarDate])), [CalendarYearName] AS (CONVERT([varchar](4),datepart(year,[CalendarDate]),(0)))PERSISTED, [CalendarQuarterNumber] AS...
Hierarchies are one of the long awaited features that will now be available in the latest version of PowerPivot. Hierarchies are useful because it allows a single click for a user to bring in all the fields that exist in a natural table hierarchy. Normally the user would have to drag in all fields individually so this can be a time saver especially if it is an 8 level hierarchy. Hierarchies are very simple to create and easy to interact with in the reporting layer as well. To get started you mus...
A little over a year ago Patrick LeBlanc wrote a blog post about inserting an unknown row into a dimension . This is a great script and i've used it dozens of time, but this week I made a few modifications to it that I thought I would share. First I changed how the Column names are generated with the coaleasce function. Here's that section of the code: Declare @columns varchar(max), @schema sysname, @table sysname Set @schema = 'dbo' Set @table = 'DimCustomer' SELECT @columns = coalesce(@columns...
For those of you in the Tallahassee, FL area on August 9th at 6:00 pm I will be speaking virtually for the Tallahassee PASS user group. Several of the groups leaders attended SQL Saturday in Jacksonville this year and after speaking with them it sounds like they have a fanatics group that they've been building. Be sure to sign up for their group news to get the info on their future meetings http://tallahassee.sqlpass.org/Home.aspx In August I'll be speaking at a very high level of Business Intel...
The new “Denali” PowerPivot has a lot of fanatic new additions that I’ve really been looking forward to. Many of my biggest complaints from the first version of the product have been addressed. One of these complaints was the lack of a visual representation of object relationships. You had to rely on the little window shown below (the Active column is new) to create and manage relationships. In the latest version of PowerPivot you will now have the ability to view these relationships in a diagra...
There are a lot of fantastic new features in the latest CTP 3 release of PowerPivot, which you can download here . This is a huge change for PowerPivot and really shows how the product is maturing into become a tool that can solve a variety of problems that it could not handle in its initial release last year. You can read up on all the change that in the latest release of PowerPivot here , but I thought I would write a series of blogs detailing each individually. In this post I’d like to introd...
Tonight I took my first crack at getting SQL Server Denali up and running on my machine. From what I had read so far there are no longer issue with installing a side by side instance of Denali with an existing SQL Server install so I went that route. The previous CTP caused issues with BIDS because the old CTPs still used Visual Studio 2008. Now in CTP 3 all Business Intelligence development is done is Visual Studio 2010 removing that previous flaw. Some initial thoughts about the installation p...
Yesterday I was thrilled to find out that my session "Common Analysis Services Design Mistakes and How to Avoid Them" was accepted for SQL PASS this October. This will be my first solo PASS session I have ever presented so I'm really excited! The last time I attended PASS was in 2008 when I co-presented with some guy I meet while in Seattle named Brian Knight . PASS was truely one of the best experiences I had my my career at that point and that is why I'm so excited to be returning this year. T...
For many Analysis Services developers building dimensions with many attributes and hierarchies is something that comes as second nature to them. However, I’ve found for those that are new to Analysis Services changing the KeyColumns property can be particularly difficult to understand why and when it is necessary. In a previous post I discussed the need for defining attribute relationships in a dimension. The side effect of have attribute relationships is that you will often, depending on your d...
Wednesday I will be presenting for the PASS BI/DW group a session on Data Mining in Excel. This is a fun topic that shows how business users can perform data mining on their own inside of Excel. As usual this is a free event to attend so be sure to attend at 12 pm. http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=618
Yesterday was the first day of the SQL Rally conference in Orlando, FL. It was a great first day because I was able to be part of delivering a full day pre conference seminar. Patrick LeBlanc, Adam Jorgensen, Mike Davis and I did an End to End BI workshop that covered: Data Modeling SSIS SSAS SSRS SharePoint BI I know it’s a lot of material but the goal was to give the attendees a taste of the entire BI stack while solving a specific business problem. It was our own special form of Infotainment ...
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...
Often when building dimensions there is a need to order you dimension attributes by something other than the NameColumn or KeyColumn properties. An example of this may be an accounting structure that is preferred to be viewed in a certain order. The data doesn’t represent that order well so you create a sort column that does it for you. The sort column can be in a table or just in a SQL Server view if you prefer. Once you’ve created this field in the table or view you can utilize it in you dimen...
Thursday, May 12 BI DBA DEV PD / WILD CARD 8:30 - 9:30 SQL Server Parallel Data Warehouse - Under the Hood Brian Mitchell The Dirty Dozen: PowerShell Scripts for the Busy DBA Aaron Nelson A data-logic approach to EF4 Mattias Lind Iceberg, Dead Ahead! - Lessons from real life disasters for the database professional Mike Walsh 9:40 - 10:40 Performance Management Scorecards - Building a process to achieve top performance Scott Schledwitz Troubleshooting Performance Problems by Reading the Waits Edd...
Normal 0 false false false EN-US X-NONE X-NONE For the 4th year SQL Saturday comes to Jacksonville, FL this year on April 30, 2011. Many of you already know about these events but just to remind you it is a totally FREE all day event that brings top speakers to you. I hope everyone already realizes this, but our industry is one that you can never stop learning. To always be considered a valuable resource to your company or maybe another you must self educate. Technology, especially SQL Server, i...
I had a client recently show me a neat little tip that I thought I would share. In the cube he was designing he had the need for measures to show under multiple display folders. For example, he had a Sales Amount measure and he wanted it to be in two display folders for his users: Dollars and Key Metrics. The way this is accomplished is by typing both folder names separated by a semicolon. This images shows a measure that lands under multiple displays folders, but this can be done with dimension...
SSIS provide several methods for passing values into your packages at runtime. The benefit of doing so allows you to change the results of the package without having to even open the package in the development environment (BIDS). The typical way of doing this is to create variables inside your package that other SSIS tools can interact with from outside of a package. The tools described in this post will be Configurations, SQL Agent Job, and DTEXEC. There are other methods as well but these are ...