Microsoft just announced SQL Server 2008 R2 release date and it’s May 2010 – not too far ahead…
SQL Server 2008 R2 along with SharePoint 2010, Excel 2010, PowerPivot and Report Builder 3.0 are all strengthening Microsoft’s hold in the BI Market, covering more and more ground in aspects of Self Service BI and empowering the users.SQL Server 2008 R2 November CTP is still available for download and the development team is still interested in your feedback about it.
Start the countdown…
Just in case you haven’t heard of them till now, SQL Server Worldwide Users Group (or SSWUG) hosts a great deal of information about all sorts of platforms – be it SQL Server, Oracle systems, MySQL etc. They also have a forum for your questions and a script library for your use. So why have I been talking on SSWUG? Because though they are hosting an event on PowerPivot (which I’ll talk about shortly), I think you should also consider them for additional information.
And now – to PowerPivot! You’ve probably heard, read and watched demos about PowerPivot (formerly Gemini) by now. The new application on Excel 2010 and SQL Server 2008 R2 should empower your users and give them self – service BI that they can share with the rest of the organization through SharePoint 2010. SSWUG is holding a virtual event with Donald Farmer from the Microsoft Business Intelligence team. Most chances are that the first demo you ever saw about PowerPivot – was given by him. The event will cover the following topics:
-
Getting started with PowerPivot
-
Insight and oversight: the IT perspective on PowerPivot
-
Power up your Pivots! Building sophisticated applications with PowerPivot
-
Beyond the desktop: PowerPivot in your business infrastructure
The virtual conference is also in a price you can afford – it’s free! ;)
Please check for further details on the virtual conference and registration on SSWUG’s PowerPivot Expo Event page.
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.
Microsoft just released SQL Server 2008 R2 November CTP. The November CTP is "feature complete" so this should be close to the real thing, before they ship SQL Server 2008 R2 in 2010.
I guess you could tell from the news coming from PASS that this would be in the wings. First of all, there was word that the next CTP would be delivered during November (which translates to: now!) and secondly, projects that before would be code named - now have their formal name. Madison is now Parallel Data Warehouse and boasts scalability for the 100+ terabyte DWH and Gemini is now PowerPivot.
So what's new in comparison to the August CTP?
For Reporting Services I think the highlights are:
- New Data Visualization Report Items - Data Bars and Indicators just as you may know them from the conditional formatting in Excel 2007 and also sparklines.
- Shared report items and datasets - taking them from the server for re-use in your own report.
- BIDS support for SQL Server 2008 and SQL Server 2008 R2 in the deployment of reports and report projects.
- Aggregate on aggregates - giving you the ability to run, for instance, an average on a sum. I think this is really brilliant as this comes pretty close to calculations I would previously do in MDX.
For more details, please look at Robert Bruckner's post, giving you all the details for SSRS in this CTP.
Better support for the SharePoint Integration - In Reporting Services that translates to a query designer for SharePoint lists as a data source and support for RTL text in the SharePoint UI (which could be relevant if you're living in Israel or the neighboring countries ;)).
In PowerPivot this means that there is a Management Dashboard data on queries executed and hardware resource utilization for PowerPivot service and workbooks.
On a side note, I would again stress how SharePoint is becoming more and more a part of BI within Microsoft's strategy.
PowerPivot now supports more data sources and enables you to edit more parameters in the connection string to your data sources. And there are a lot of ways for you to learn PowerPivot.
There is more news to this release including Master Data Services, but I leave it to you to continue reading on the SQL Server 2008 R2 CTP3 and download it.
Microsoft had released not too long ago, the last Service Pack for the standalone version of PerformancePoint Services - PerformancePoint Server 2007 SP3.
From here on out, all development for PPS is going to be part of SharePoint Server. This is again part of Microsoft's strategy of empowering SharePoint as a product, as it also connects to Excel Services, Reporting Services (including Report Builder) and Visio Services.
So what's new to PerformancePoint 2010?
- SharePoint Server 2010 as the repository - meaning PerformancePoint Services stores data sources in document libraries and all other dashboard content in lists.
- PerformancePoint Services uses SharePoint Server 2010 to manage user credentials and to secure access to dashboard content and its underlying data sources
- A KPI details report which works as a webpart to show relevant metadata about the KPI to the user
- Enhanced Analytics reports
And as always - with every good there is a bad, as PPS no longer supports Trend Charts, PivotTable reports, PivotChart reports and 32-bit server architecture to name a few.
You can read more details about PerformancePoint 2010 and also take a look at how Microsoft sees all the capabilities of BI in SharePoint 2010 (highly recommended, as a picture is better than 1000 words).
I read a few months back a post by Robert Bruckner saying that the InScope function isn't relevant anymore, when you’re creating a report with Tablix.
I remembered that when I wrote about a recursive sum for a group in a matrix in SSRS my example was only about SSRS 2005, as my VPC with SSRS 2008 crashed… well I finally downloaded SSRS 2008 R2 and installed it. So what did I do (after checking out the new maps feature)? Well, I wanted to see how simple creating a recursive sum in a matrix had actually become. I created a report in Report Builder 3.0, using again the Adventure Works 2005 Sample DB. I used the Employee Full Name against the Product's Product Line and the Sales Amount. I set up the recursive part by defining the row group on the FullName row group which groups on the EmployeeKey:
and the parent group (ParentEmployeeKey) to that on the advanced tab:
And that was actually it!I didn't bother creating a subtotal anymore. Just the totals for each attribute (be it the Product Line or the Full Name of the Employee). Didn't need to define InScope as an expression for the sum. Nothing! Creating a recursive sum in a matrix in SSRS 2008 is as simple as creating it in a table. I have to admit I was amazed.
So first of all I have to take my hat off to everybody at the SSRS team who are making the creation of reports more and more like child's play. You're getting the same report you got with SSRS 2005, with less trouble. That also means that your subtotals are again just for the employee inside the matrix, (and not for the employee and the workers who work under him in his team – see Abbas Syed again as an example).
That problem has not been solved, but creating the report in general has become simpler. If you still need it, InScope still exists in the function list:
So if you're trying to create a recursive sum in Reporting Services 2008, you may have discovered this – InScope is dead, long live SSRS 2008!
I’m very glad to say that not too long ago I got to see a demo on Panorama Nova View 6 with the rest of the BI team, enabling me to further give you detail on it.
The demo covered quite a few of the major products:
NovaView Analytics – which existed in previous version though it’s UI has changed
NovaView Flash Analytics – which is one of the new features in this version
NovaView Smart ReportingNovaView Dashboards
NovaView Executive DashboardsNovaView Universal Data Connector – which is another major new feature in this release
-
Looking at the new Flash Analytics, I really liked the way the new dynamic title of the view looks. In the previous versions, the title could be quite confusing to understand as it would give you both the attributes which were on the grid and the members you sliced on all in one long title (unless you gave your view a static name). The new title, on the other hand, gives you just the attributes on the grid. Below the title, you get a subtitle with the relevant attributes and the members you selected for them. This is much easier to understand and also makes for easier manipulation, as you can click the member in the subtitle and choose to change it from there (and not just from the slicers pane).
-
If in previous versions you would apply each and every slice in one step, you can now apply several slices in one step! No more waiting for each and every step to finish, you get all the manipulation you need on your data in one step.
-
The Flash Analytics doesn’t have anymore right click on it. Every action is possible is given with a simple click.
-
If you created a calculated measure and it’s shown in your chart, you can click that calculated measure and choose to see it with a second Y axis. You can also choose to see it in a different way than the other measures. For instance, “sales price” and “cost” will show in a column chart, and you calculated “profit margin” can be shown as a line above them with a second Y axis.
-
You can filter on the name of reports to find all your “sales” reports in a group of reports (for instance).
-
Advanced styling abilities, both for the grid and the graph. The user can style both the grid and the chart according to his own taste. His definitions may be: deleted on his first exit from the view, or applied to the view next time he enters it, or applied to all of his views. The one of 3 options that will be applied will depend on the permissions I, as a Panorama Administrator, had given him.
-
The user no longer needs an existing view to build a new view. He can now build a new view from scratch.
-
When moving from the Analytics to the Flash there is no need to rebuild the views – all the views should open as they had in the Analytics, only this time with the better Flash UI.
So much for the Flash Analytics – let’s get to the other major feature in this release – the Universal Data Connector.
The UDC allows us to create a connection to SQL tables and more, to create a sort of DSV on them. You get an editor for your T-SQL query and after you created the DSV, you can define a model on top of it: giving different names to the columns than those that exist in the DB, defining hierarchies etc.
The model you defined can be public (and then edited by other workers) or private. If the model you created in the UDC was defined as private, then the view you created on it will also be private just for you.The model you define will be a loaded in – memory every time you go into the view based on that model, (and if the term of “in – memory” sounds familiar, maybe it’s because you’ve been reading about Gemini or QlikView). You can later define MDX calculations on your model, just like with every other view in Panorama. Panorama also promises that the installation of Panorama software and hotfixes have become simpler. So many goodies, you have to be wondering – what’s the catch? Queue the problems:
-
Dynamic grouping is not fully supported. The Panorama Nova View Analytics (desktop and web viewer) used to allow you to choose several members and create for them a dynamic group with its own name. However, trying to use this feature for our cubes would give us errors. In the meeting today we understood dynamic grouping is not really a fully functioning feature.
-
The Flash Analytics (for the time of writing this post) still does not have all the capabilities of the Analytics. Specifically, you cannot apply Panorama Nova View drillthrough or cube actions. Both are expected to be added by the end of September 2009.
-
The Flash Analytics still does not have exceptions in it as the Analytics has. It can show the exceptions you defined in the Analytics, but you cannot edit them, apart from changing the value of the parameter for which the exception is shown. You cannot define a new exception in the Flash Analytics or choose to change the parameter on which it is shown.
-
The connection in the UDC is done with an SQL user (and not your NT user).
-
Though the UDC can connect to different data sources (RDBMS, Excel, existing SQL Reporting Services etc), when you create a model – it can only be based on one data source and cannot incorporate in it a few data sources.
Panorama was still working on adding additional features to Nova View 6 when we got the demo, (such as adding the drillthrough action to the Flash), so things may have changed since then. For more details, you can watch the webinar on Panorama Nova View 6 or contact Panorama for your own demo.
Microsoft has released today SQL Server 2008 R2 CTP1 (formerly known as Kilimanjaro) to the general public after it was available for two days already to TechNet and MSDN subscribers.Except for adding more features to help DBAs and developers work against the databases, SQL Server 2008 R2 CTP1also offers Report Builder 3.0. Yes, it’s true, you just blinked and already Microsoft has released a new version to Report Builder. The enhanced visualization from Dundas gages and charts is still there and the added bonus is that Report Builder 3.0 now offers support to geospatial visualization (maps) and faster report processing.The official blog is offering you to sign up to be one of the SQL Server 2008 R2 Customer Evidence program.
In the meanwhile you can download SQL Server 2008 R2 CTP1 from the official site, and if you encounter any problems please refer to the SQL Server 2008 R2 Setup and Upgrade forum.
Via Dan English’s blog, I’ve learned that Pass is offering a day of BI webinars for free! The Pass Convention itself is scheduled for November in Seattle. As this year there will no dedicated BI conference from Microsoft, Pass is the conference for you to get updated on all your BI needs. This is also why I was very happy to hear they’re also offering a day of free sessions with very well known speakers.
The sessions are mostly SQL Server 2008 oriented, but I believe you’ll find more than enough information there even if you’re using SQL Server 2005. Session topics vary from SSIS, SSAS, SSRS and more. Though the session time is given for GMT, when you register for the sessions they will be sent to your Outlook calendar with the correct time in your region. The site doesn’t specify if the sessions will be available afterwards for download.
So go check out the 24 hours of free SQL Server training Pass is offering you – you’re bound to find something you like.
A user of one of my projects had developed a very nice view in Panorama Nova View 5.5 and we decided to integrate it into the views for all the users. So how do we take a view that was saved in a personal book of a user and add it to the general book in Panorama?
Well, first of all you take the user ID. Let's say it's "x123". You go to the Panorama Server and go to Program Files > Panorama > E - BI > Users > x123 > Private Book. You actually went to the physical folder that holds the user's views on his private book in Panorama. Copy the relevant view and paste it into you own Private Book (located also in Users folder, but obviously under your user ID).
Now, open Nova View Desktop. Connect to the relevant book, where you'd like to add the new view. When you connect to a book, the default is that you only see the web briefing book. You can add to that your own private book. Go in the top toolbar to Book > Show Private Book and click it.
You can now see your private book with the user's view. Right click it to copy it and then paste it into the Web Briefing Book. And that's how you can move your user's private view to Panorama's web briefing book without having to rebuild it.
A colleague of mine ran into a peculiar problem with her cube. She deployed it just fine and didn't get any error messages. Still, there was a problem. She could see the dimensions in her SSAS project just fine. She could see her cube as well. But she couldn't see any of her measure cubes.
So what was the problem? She was missing the calculate statement from her cube. If you look in the BIDS at your cube on the Calculations tab with a Script View, you'll be able to see that the first row in there is "Calculate;"
The calculate statement appears by default in the calculations tab of your cube. The calculate statement tell your cube to aggregate the fact data from the lowest level of granularity to the higher levels.
So, if you can't see any of your measure groups or all your measures return null value, then maybe you should check that the "Calculate;" statement exists in the Calculations tab in your cube.
Last but not least, I've recently learned that Dubi Lebel is leaving Microsoft Israel. Whenever I got to see Dubi, he would talk to me about something in SQL Server with the most genuine excitement. And his excitement is very contagious!
Dubi, I wish you the best of luck in your new path.
Here is the presentation I gave on the topic of the Parent Child Dimension at the BI User Group meeting. I tried to summarize most of what I could find on the subject and I hope it helped you learn something new about how this sort of design can aid you in your work.
You can download the presentation here: The Parent Child Dimension PDF
For more details on future meetings and presentations that were already given, please check Ronen Chenn's blog, Yossi Elkayam and Eran Sagi's blog or the Israeli SQL Server site.
It was a pleasure seeing you all and I hope to see you again.
I'm lecturing at the coming BI User Group meeting on Wednesday, June 24th 2009. My lecture will be on the Parent - Child Dimension: the flexibility in design and the cost in cube performance. I'll be going over the basic Parent- Child Dimension and show how we can gradually enhance it. Mostly, I hope you can learn from the lecture how the Parent - Child Dimension may be relevant to your work.
The program for the evening is:
17:30 - 17:45 Gathering
17:45 - 18:45 Ella maschiach - Analysis services ,The Parent - Child Dimension - best practices.
18:45 - 19:15 Q&A
19:15 - 19:30 Break
19:30 - 20:30 Pini Yakuel - The CVM - An Innovative model for customer Lifetime Value maximization ( Extreme Data mining Session)
20:30- 21:00 Q&A
About the lecturers:
Ella Maschiach - a BI Project Manager in the Tel Aviv Municipality.
Pini Yakuel, M.Sc. Engineer, CEO and CO-Founder of Mobius Solutions, an Israeli company that supplies innovative analytical solutions, which enable companies to implement a customer-centric business approach.
I hope to see you in the coming BI User Group meeting.
My blog is 2 years old. Yeah, I know! He's already started walking and talking a bit. It's so strange to see him grow...
Still, I admit that coming to write this post, I couldn't help but think to myself that this time around I don't want to talk about my blog, even though it is his birthday (please forgive me blog). Instead, I'd like to talk about the man who made me start all of this (and a lot of other things as well) - my big brother, Adlai Maschiach.
My brother is 7 years my eldest. Which I guess means (at least) 2 things:
- He got to experience a lot of things and learn a bit more than I did, for a certain point in time.
- I'm his kid sister ;)
I'll elaborate on point number 2 before I address point number 1.
One of my favorite photos of us both is when I was just a baby. You see my brother sitting on the sofa in the living room looking lovingly at a big white ball that he's holding in his hands. That big white ball is me. Little did my brother know that that innocent looking white ball would grow up to be an annoying little sister! Ha, ha, ha, ha, ha!...
As a kid, I saw it my life mission to annoy my brother. It didn't have to be in new ways all the time. Sometimes, even using old tricks that would annoy him would be enough to be filed under "fun" for me.
Luckily enough I grew up. And then we simply became good friends. And as good friends, I also got to hear his view on things, his opinion on different subjects and get some advice.
A very good advice he gave was to open this blog. Then, he gave me yet another good advice - to keep writing posts in this blog (which, BTW, is harder than just opening a blog!). Though it required quite a bit of nudging on his part at the beginning, I admit I now enjoy writing and keeping this blog more than I have ever imagined. It has opened me to new people and places in so many ways.
Adlai, I think I am constantly in awe of your knowledge in so many fields - you know ASP .Net, VB .Net, Reporting Services, Integration Services, Silverlight, MOSS, InfoPath, WCF, SQL Server Management, ESB and Biztalk (and if I missed something it's quite simply because I can't keep up with you!). You have already gotten recognition from Microsoft for all the help you've been giving at their Visual Studio forum. Quite a few people have already approached me and told me (knowing that I am your kid sister) of how your webcasts have helped them learn and evolve in their work. You manage to keep two blogs (and another one) with a world of knowledge and data in them, and in between you even managed to put a project on Codeplex! I think I have something else I need to learn from you - time management :)
Adlai, I hope I can, in my little corner, do as much and help as many as you have in your work and day to day life.
Adlai - Thank You, from your kid sister.
Oh and... Happy Birthday Blog :)
More Posts
Next page »