Jessica M. Moss

When someone is querying a new Analysis Services cube, there is one mistake that I often see made: trying to use a dimension that does not relate to the desired measure group. For example, when looking at the Adventure Works cube, if you try to use any of the Reseller measures with the Customer dimension, you will receive the same value across the Customer members. Because there is no relationship defined in the cube, the MDX query will use the default member for that particular attribute, which, in this case, is the ‘All’ member. It would look something like this: This type of situation typically goes away once a user has learned the new data model, but Management Studio 2008 provides a neat dropdown list to help initially avoid the rogue query. On the Metadata tab, under the label Measure Group, you can select the measure group you are targeting. The GUI refreshes to only show the dimensions and measures that are associated with that measure group. If we select the ‘Reseller Sales’ ...

This will be my first year attending the PASS Summit as a speaker rather than an attendee. I will be joining Rushabh Mehta to present a spotlight session entitled "Building an SSIS Management Framework". The PASS Summit is a great conference that focuses on SQL Server and Business Intelligence. Because it is in Seattle, there will be a ton of people from the SQL Server Developer Team and SQLCAT. If you have any questions about SQL Server, this is the place to be. You still have a few weeks left to sign up. Solid Quality Mentors is also offering a discount code applicable to the cost of the conference. You can get that code from here: http://www.solidq.com/na/pass.aspx . So now you have no excuse left to not sign up!

In a previous post , I showed how to dynamically limit the number of rows shown in a table or matrix. This methodology used a filter on the table to only show the desired number of rows. The disadvantage with this method is that all rows are returned, even if only a small subset of rows is actually desired. To alleviate this problem, you can use a query parameter to restrict the number of rows returned at the query level. This does introduce a different problem. Every time the report is run with a different Top N parameter, the query will be rerun to bring back the correct number of rows. You will need to determine which method is optimal for your situation. Here are the steps to create a sample report to show this new method. 1. Add a report parameter named “NumberPpl” of data type integer. 2. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT TOP(cast(@TopN as int)) DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResell...

I have an exciting week coming up! On Wednesday evening, I will be speaking at the Rockville .NET User Group on SSIS Tips & Tricks for both SQL Server 2005 and 2008. If you're in the area, I hope you'll stop by. On Friday night, the Northern Virginia Girl Geek Dinner is having their inaugural dinner. Hosting by Viget Labs , sponsorship by Microsoft , and a presentation by Dr. Joanne McGrath Cohoon will make for a great evening! I will be there, helping to ensure everything runs smoothly. The sign up deadline was just extended through Wednesday, so sign up now at http://novagirlgeekdinner.eventbrite.com/ !

Extensibility in SQL Server Integration Services and SQL Server Reporting Services is achieved by writing custom code inside your package or report. SSIS provides a great interface by using Visual Studio for Applications (2005) or Visual Studio Tools for Applications (2008), lightweight versions of the Visual Studio IDE. When you create a script task or component, you can write code using intellisense and error squiggles. SSRS, on the other hand, does not provide any IDE for writing custom code. If you want to create VB functions, you have a notepad-like window without colors, intellisense, or any error handling. In comes SnippetCompiler, an application that allows you to write and compile snippets of code. You can download the executable here: http://www.sliver.com/dotnet/SnippetCompiler/ . The current version allows code in both VB.NET 2.0 and C# 2.0. A .NET 3.5 version is in Alpha release and can be downloaded from the same location. Keep in mind that if you minimize the app...

In SQL Server Reporting Services, you can dynamically limit the number of rows that are displayed in a table or a matrix. Here are the steps to create a sample report showing this. 1. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query: SELECT DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount FROM DimEmployee INNER JOIN FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey WHERE (FactResellerSales.OrderDateKey = 20011001) GROUP BY DimEmployee.FirstName, DimEmployee.LastName 2. Add a report parameter named “NumberPpl” of data type integer. 3. Add a table to the body of the report with the data fields of firstName, lastName, and salesAmount. Sort by the column salesAmount from ‘Z to A’. 4. Set the Filters option of the table as shown below. (This is where the real work is happening!) 5. Preview the report, modifying the value for the parameter NumberPpl to see the number...

Those of you who visit my blog directly may notice a new image labeled "WorldMaps" on my right-hand side bar. WorldMaps , introduced to me by Andrew Duthie , stores statistics about visitors to any website. By signing up for an account and adding the tracking device on your page, you can keep track of the number of hits, different IP addresses, visitor locations, and more. For an example of what WorldMaps can provide, click on my world image, which will link to a page with my statistics. For other statistics of interest, I also use Feedburner . While I'm happy with the information they've provided, there is something about seeing my visitors’ locations visually that speaks to my Business Intelligence side ;) I definitely recommend you take a look at WorldMaps!