TechEd North America 2011 wrapped up last week with big news about PowerPivot and SSAS. Just in case you haven't read it already somewhere else, I'll give you a very short re-cap. In SQL Server 2005, the UDM was supposed to encapsulate the solution for modeling your project. In SQL Server 11, codename Denali, it's going to be replaced by BISM - the Business Intelligence Semantic Model. All the reporting tools in the Microsoft BI Stack will work against this model. In Denali, you will be able to use BISM both for tabular model in PowerPivot and for cubes in SSAS. Furthermore, cubes developed in earlier editions of SSAS automatically become BISM models. SSAS cubes will still have storage in form of MOLAP or ROLAP and queries running against them in MDX. PowerPivot will have VertiPaq or DirectQuery with DAX queries running against them. Crescent, the new reporting tool, uses only DAX at the moment and so will only work against PowerPivot.
Ok, so that was a very short summery of a very important post made by T.K. Anand. I strongly recommend you read his post to get a fuller view of what to expect in Denali, both for PowerPivot and SSAS. Additional links and data can be found on Teo Lachev's post on BISM.
I guess I would also add the most important point made in Mr. Anand's post:
"Another limitation in the upcoming CTP release is that models built using the multidimensional project will not support DAX queries (and thereby Crescent, which uses DAX to retrieve data from the model). We recognize that removing this restriction is very important for customers with existing Analysis Services solutions to be able to upgrade to SQL Server "Denali" and leverage Crescent. The product team is actively working on it and is committed to making this functionality available to customers." Which hopefully means we'll be able to move easily between tabular projects and SSAS cubes!
Currently, when I open a new project in BIDS, I have to make a choice in advance - do I want a SSAS project or a Report Model 1.0? Granted, I can make that choice as before I start developing I have already decided in my system analysis stage what I need to develop. Will it have a Parent Child Dimension? Many to Many Relationship? A need for a complex calculation? If one of those exists, I will use a cube. But if the solution is simple enough, it's up to me to choose between Report Model 1.0 and a SSAS cube. Hypothetically speaking, I could use the same DSV for both projects (providing the tables and the relation between them are simple enough). But, as I mentioned, I do need to decide early on what it is that I'm developing.
The ability to move from one solution to the other seamlessly would be amazing! So many projects start off simple, and then, as time passes, get more and more complex... the thought that I wouldn't have to begin development in the BIDS from scratch if I need to move from a Report Model 1.0 to a cube (or rather from PowerPivot to a cube) is fantastic. And I admit that that may finally put an end to me developing in Report Builder 1.0 and move to PowerPivot. After all, I remember when Ariel Netz talked to me (in not so many words) about Project Crescent as a mean to move developers from using Report Builder 1.0. Crescent does seem to have the functionality that Report Builder 1.0 has (and Report Builder 2.0 and 3.0 lacked). But it only works at the moment with DAX queries against a PowerPivot model. If it could work against a SSAS cube as well - it would be the ultimate solution for me. (Especially as Report Builder 1.0 against a cube works quite poorly). That would be like the final seal for me to know that I need to move my models from Report Builder 1.0 to PowerPivot.
I really feel like the solution is near. First of all, I feel like Microsoft already sees it that way if the SSAS team blog switched from being called "The SSAS Team blog" to "The SSAS and PowerPivot Team Blog". I don't take those semantics lightly and I'm sure nobody else in Microsoft does either. Secondly, we know that a tabular PowerPivot model, when deployed to SharePoint, can be viewed as a cube (which also enables Panorama NovaView to work against it). So, I am hoping the solution for is close. Maybe even in SQL Server 2011?...
In the meanwhile - the future is BISM!
Hey blog, how are you? I know I haven't been here for quite some time now... I guess I needed a break. Some time for myself. And I know you were waiting here for me all that time. Being patient and understanding. Knowing that one day, I'll come back. Thank you. I missed you. I think this time, I'm here to stay.
So, the topic for today is how to not work automatically and adding a table to your database. About a year ago, I created a report in SSRS for a user of mine on the distribution of workers of the municipality in different cities in Israel. A year had passed and she needed the same report, but this time she needed to group the cities by areas. My first instinct was to ask her to give me an Excel file containing the cities and the areas they belong to. I was about to import that data to a table I would create for that in my DB. Boris, our trusted DBA, gave me another suggestion. Instead of adding a table and keeping it updated for a query that's usually run just once a year (for the yearly report), he suggested I keep it all in Excel.
That means, I get an Excel file mapping the cities to the areas, download my own Reporting Services report to the same Excel workbook and then use a Vlookup to map the areas to the cities. Last but not least, I created a pivot table on the new data with the total of workers per area and per city.
Needless to say, it didn't take me a lot of time. My customer was delighted to get such a quick response (and I was happy to be done with it so quickly :)). She was eager to know how I managed it so quickly and was interested in learning it herself (as before she planned on doing all the mapping of cities to areas by hand). I told her it wouldn't be a problem to teach her how to do the same herself.
Excel is a powerful and popular tool and Vlookup is a strong function within it - use it in your work and it'll benefit both your work and your customer's!