Ella Maschiach's BI Blog

Panorama now integrates with PowerPivot and SharePoint 2010

A while ago, I saw Panorama was advertising a webcast detailing how Panorama gives a complete solution for the entire Microsoft BI platform, including PowerPivot. I have to admit, just seeing Panorama and PowerPivot in the same sentence seemed odd to me, as PowerPivot seemed like a completely full solution for its purpose. I didn't see what Panorama had to add to the equation. And so I began asking Panorama a few questions...

So where does Panorama offer any added value?

Well, if your organization is considering using SharePoint 2010 Enterprise Edition along with PowerPivot. If you are going to deploy your PowerPivot workbooks to SharePoint 2010, and you'd like to look at them on the SharePoint platform (and not download it to your desktop), then you'll probably be using Excel Services . This would probably be the case if your organization hasn't moved completely to Office 2010. For instance, your organization may decide to integrate Office 2010 on the IT department workers desktops first and only later move the rest of the organization to Office 2010. Actually, something similar happened in the municipality with Office 2007 and so, I know that can and will happen...

In any case, if your user will be looking at the workbook through Excel Services, then he's going to get a very thin client, which does not have all the capabilities of Excel on the desktop. This means you may be able to use the slicers and dicers defined for you in the workbook, but not add new ones. You will not be able to add formulas and exceptions. You will have a viewer to use, but not to contribute to, unless you open the Excel workbook on your PC.

Even more so, one of the drawbacks of PowerPivot on SharePoint is that you can only define security on the file level.  You cannot, for instance, define that a worker will be able to see information on a certain department in the organization mentioned in the workbook. You can only grant your user to the whole organizational data in the workbook. Role security and visual totals are actually possible in SSAS.

So how does Panorama help you out on those things?

First of all, once a workbook is deployed to SharePoint, Panorama can connect to it, just as it can to any other SSAS cube. You can use the Panorama SharePoint webpart to create a view on the PowerPivot cube. The view can include a graph or a pivot with any slicers and dicers your users would like to add or remove, along with exceptions and formulas on the data etc.

In terms of security, through Panorama, you'll be able to define slicer security on the data, and enable the users to use the view with the slicer security, without having rights to view the workbook (with all the data) which was published to SharePoint.
Basically, you'll be able to do all that through Panorama and SharePoint 2010, without upgrading all of your organization to Office 2010 (and if your organization is currently on Office 2003, then that makes for quite a move).

I would also add that the UDC (Universal Data Connector) will also connect to PowerPivot published to SharePoint. So now, if you'd like, you can create a Panorama dashboard which incorporates data from SAP BW, SSAS, PowerPivot and a relational data source which can all connect through a joint slicer, (for instance, time).

What's more is that Panorama can also connect to Google Apps. Though I understood it is not scheduled for the first release, I can't help but think of a PowerPivot workbook which was published to SharePoint and then viewed through Panorama in a Google Apps. Probably not what Microsoft had in mind...

So, if your organization is considering SharePoint 2010, but not necessarily Office 2010 (and PowerPivot) for everyone, you may be interested in looking more into Panorama's offer to connect all of your data sources. In that case, you can contact Panorama support for further information.

Microsoft Business Intelligence Roadshow: פרספקטיבת האנאליסט ניתוח נתונים אד-הוק עם PowerPivot ואקסל 2010

Microsoft Business Intelligence Roadshow: פרספקטיבת ה-IT מחסני נתונים, ניהול ומבנים אנאליטיים

בניית מודל ב MDM היא לדוגמא בעזרת ERD - Entity Relationship Diagram. מאפשר הבנה של קישור בין הישויות והתכונות שלהן.

Microsoft Business Intelligence Roadshow: פרספקטיבת צרכן המידע בינה עסקית בשירות עצמי

Microsoft Business Intelligence Roadshow: בינה עסקית מטרות, מושגים והפלטפורמה

המשך ההרצאה מדבר עלהמעבר מ Organizational BI ל Self – Service BI. הדוגמא שהוא מביא היא בנושא אפליקציות שהמשתמשים שלנו יוצרים על אקסל לניהול נתונים, ביצוע חישובים וניתוחים. חוסר השליטה שלנו כ IT על מאגרי המידע הפרטיים הללו והניהול שלהם. כאן נכנס Office 2010. Self Service BI הוא חלק חשוב מה BI העתידי והשילוב שלו הוא ב SharePoint 2010 כחלק מ Office 2010.

Free Datasets, Data Warehouse and OLAP Databases for your use

Whether you're doing demos, trying different aspects of applications or just looking for something new, you may want to have a broad and free selection of data to choose from. This post is here to help you out on that.
I've collected a number of free data source for you to use:

From the PowerPivot blog:
Data on the District of Columbia http://ogdisdk.cloudapp.net/DataCatalog.aspx

From Andrew Fryer's blog:
Data from the UK Government: http://data.gov.uk/data/
Datasets for neighborhoods in the UK (with Geospatial data) http://www.data4nr.net/resources/all/

From Chris Webb's blog:
The Guardian Data Store
Data from the Greater London Authority http://data.london.gov.uk/

Also, following a post from Mr. Webb on the Dallas Project, I made a post detailing the use of Dallas in SSRS via an Excel spreadsheet. There is also a post from Mr. Webb on connecting to Dallas through Excel well worth checking out.
The data from Dallas includes datasets from NASA, Associated Press and more all for free (for the time of writing). Dallas requires you sign up for it.

From Russell Christopher's blog:
The new large DW and OLAP sample databases of Contoso for SQL Server 2008, SQL Server 2008 R2.

And obviously there's the old trusted Adventure Works DW and OLAP sample databases for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure.

Once you have downloaded the data, you can use it in the appropriate server platform and also use it in PowerPivot.
I will keep updating this post so tag it for future use!
Start querying your new free data to find something interesting.

Update 14th of February 2010: if you’re interested in a free data source in Dutch based on a SSAS 2005 cube and presented in Panorama, then you can look up:

https://www.werk.nl/portal/page/portal/werk_nl/cwiencijfers/meerweten/arbeidsmarktinfo/landelijkearbeidsmarktinformatie#ID78Unemployed
Which shows data about vacancies and job seekers. Obviously, you can also look at it also to get an idea about styling your Panorama report. Thank you Marco for your update!

SQL Server 2008 R2 Release Date

Start the countdown…  

Free Virtual Conference on PowerPivot with Donald Farmer

• 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

Please check for further details on the virtual conference and registration on SSWUG’s PowerPivot Expo Event page.

Can't see all the rows in your drillthrough report in report builder?

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.

Using the Dallas Project in Excel and an SSRS Report

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.

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.

SQL Server 2008 R2 November CTP

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.

Performance Point integration in SharePoint 2010

Recursive Sum in a Matrix in SSRS 2008

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:

 

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:

Panorama Nova View 6 Reviewed

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.

• 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.

• 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.

SQL Server 2008 R2 CTP1

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.