I'm developing a report model in Report Builder 1.0 at the moment. I felt like it was finished and started testing the model with some sample reports I knew my client needed. And then I got the oddest thing…I would get a report saying something like:
Gender Marital Status Amount of Sales
NA NA 865
Male Single 30
Female Single 36
NA was what I chose to put in case I was using referential integrity. I was looking at the sales table and the employees table and if I found a sale which didn't have an employee, then I created a fictitious employee for that sale.So, I wanted to see all the 865 sales that had a fictitious employee for them. I clicked on amount of sales and only got 20 records :(… where did it all go?
I asked our trusted DBA to run a trace in the SQL Server Profiler to see what went wrong with the query in Report Builder. He got the query, ran it, and got the 865 rows! All I got was just the first row from all the sales. I couldn't understand what I did wrong… I looked at the Employee entity I had in the report model as the clickthrough report was being run on that entity's DefaultAggregateAttributes. The IdentifyingAttributes collection included Employee ID and Sales ID. The DefaultDetailAttributes collection also included Employee ID and Sales ID so I should have seen my fictitious Employee with all his sales. Alas, that wasn't the case.After a lot of searches on the web (which didn't amount to anything), I finally got an idea from a colleague (thank you Yaniv!) to change my DSV. In my DSV I had only defined Employee ID as the Logical Primary Key for the Employee table. I thought that in Report Builder what matters is just what you define in the model itself (the .smdl file). I thought that the DSV doesn't mean much just like it doesn't in a SSAS project (where what matters is not necessarily the connections between dimension tables and facts in your DSV, but rather the dimension relationships you define in the Dimension Usage tab for the cube).
So I did update the Logical Primary Key for Employee table in the DSV to be Employee ID and Sales ID. I ran Autogenerate on the report model, deployed it and ran the same report. Clicking Amount of Sales this time got me a drillthrough report of 865 records – what a relief!
So, if you're wondering why you're not getting all the records you should be seeing in your clickthrough report, check your DSV – it could prove to be the answer you're looking for.
I read a while ago in Chris Webb's blog about project Dallas which really got me interested. Quoting from the site itself: "Microsoft® Codename "Dallas" is a new service allowing developers and information workers to easily discover, purchase and manage premium data subscriptions in the Windows Azure platform."
So what does it mean? Basically that you have a public data source in the cloud that you can connect to and use for your own applications.
And why am I so psyched about this? I have to admit that I am constantly looking for data I can use for demos. Yes, we all have Adventure Works to use, but sometimes you find you need something more. Dallas offers a great variety of information for you to use, from various sources like: infoUSA, Associated Press, NASA and many more.
We could go into what this means strategically for Microsoft with its cloud services, but for the moment, I'd like to talk about it on a more practical stand point – as in what you and I may gain from this. Dallas was planned to be used against PowerPivot, if you'd like to perform any analytics against it. Now I have to admit I have yet to install Office 2010 Beta (including SharePoint 2010 and Excel 2010 with PowerPivot added to it), but I really hoped that didn't mean I couldn't use the data on Dallas for an SSRS 2005 report.
So I went into my Dallas account, and created a query using the values given for example on the various fields connecting to the infoUSA database.
I decided to preview the data in its raw form in the form of a table. Dallas also enables you to consume the data in Atom 1.0 form.
Now, you can connect to XML as a data source in SSRS, but that means you also have to build a query against it in your dataset. Looking at XQuery language, made me think I had better use something friendlier… so I decided to use Excel 2007 instead.
I invoked the query as Atom 1.0, got my result and saved it on my PC.
Your Excel 2007 (also in 2003) enables you to connect to an XML source and open it.
I grant you, except for the data columns, you also get the data type columns.
So what I did was simply copy, paste and format the columns I needed from the main sheet to a new one.
Specifically, I needed geographic coordinates so I could check a third party surface chart report component for SSRS 2005.
Defining an excel file as a data source is also quite simple going from your Start > Control Panel > Administrative Tools > Data Sources (ODBC) and then continue with connecting to excel as a datasource for your SSRS report. And Voila! You have Dallas data to use in your SSRS 2005 report.
If I managed to get you curious about Dallas, then I really encourage to sign - up for it, as it's free now, while it's CTP1.