Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2. They give you the ability to monitor Analysis Services usage and metadata. MSDN articles on SSAS DMVs – http://msdn.microsoft.com/en-us/library/ms126079.aspx Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamicall...
Read More
In a previous post I wrote about how you can use embedded custom code to extend the capabilities of Reporting Services. This week I will show you another method of using custom code but this time with external assemblies. Ideally when using custom code you would choose to do so using external assemblies. External assemblies help developers manage code from outside Reporting Services and share the exact same code across multiple report. Here’s a few of the pros and cons of using external as...
This is my wish list of things I’d like to cover in todays session at 11 AM EST https://www1.gotomeeting.com/register/935071481 but seeing that I know I’ll only get to a third of these thought I would post these for everyone. If you’re seeing this late the sessions are recorded and can be viewed on www.pragmaticworks.com/Resources/webinars . Next week look for a while paper I’ll be releasing on the same topic but of course with more details and screenshots. This post is p...
It has been a quiet past few months while my family has adjusted to having two new additions (twins!), but I have slowly been working my way back into several speaking events. Having said that January is a busy month of events and I thought I would share them in case you are interested in attending any of them. Date: 01/12/2012 Group: South East Michigan SQL Server User Group Topic: Creating Dashboards with PerformancePoint Services This is a user group that I will be speaking virtually for but ...
When developing reports in Reporting Services you will often use the built-in expression language to make report data and formatting dynamic. The expression language can do a lot to within Reporting Services but any gaps in functionality that it has can be filled with custom code. There are two ways to implement custom code in Reporting Services. The first way, which I will show in the post, is with embedded code. The other method is to create an external assembly that can be imported into a rep...
Display folders are an often used tool in Analysis Services to help end users navigate through a cube or dimension they could find what they need to build a report. Imagine you develop a cube that has dozens of measures. For an end user to find what they need they would be forced to dig through every measure. If an end user gets lost while trying browsing the cube they are a lot less likely to continue using it. This is where display folders come in handy. They make it easy for end users to find...
The Analysis Services Deployment Wizard is an often under utilized but important tool when moving your OLAP database throughout your environment. Often I see developers deploy directly from BIDS to all Development, QA, and Production environment by changing the project properties to the different instances. For multiple reasons this is a bad practice. With a sloppy developer like me I’m likely to accidently deploy to the wrong server. You could unintentionally deploy over Production securi...
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...
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