Kasper de Jonge PowerPivot Blog

The MS gemini team posted a new blog with a overview of Gemini, what was new to me was the following part:

Sharing Gemini Applications

While many workbooks are built for personal use, some are worthy of being shared across a workgroup. Here again, Gemini works the way Office users do. Since Gemini data is stored within an Excel document file, any way to move that document – through file shares, emails, publishing to SharePoint, etc. – transport the Gemini contents along as well. Users without the Gemini addin can browse the data, those with the addin get the full experience. Just as Excel and Gemini light up together, Gemini also extends SharePoint capabilities in several ways.

Report Gallery

For the more visually-inclined amongst us, a flat SharePoint list leaves something to be desired. File names, data last updated and by who are useful but only tell part of the story. Gemini provides Silverlight based skins that present different views on document libraries. These views show snapshots of the contents of documents. In the example below, we see two workbooks with two spreadsheets within them:

These snapshots are also live links in that clicking on a thumbnail of the a worksheet will take users directly into ECS with the worksheet loaded.

Scheduled Data Refresh

The Gemini model embedded within the spreadsheet keeps information about where data came from. Once published to SharePoint, users can specify schedules for the data refresh operation so the workbooks use the resources of the server to stay fresh.

And what was even more impressive:

Using Gemini Applications as Data Sources

Once published to SharePoint, Gemini models embedded within workbooks appear as an Analysis Services databases! This means any AS client tool – Excel, Report Builder, etc. – can connect to this database as if it were on just another AS server. The only difference for these clients is use of a URL to the document stored in SharePoint instead of a server name. Gemini services running on SharePoint handle loading the right database, managing its lifetime, and transparently redirecting client queries to the right database on the right server.

This gives some incredible new options, but i wonder what it will take of server perfomance (memory ?) when you have 20 of these models on your server.

Read the entire preview here:

http://blogs.msdn.com/gemini/archive/2009/08/24/overview-of-gemini-features.aspx

UPDATE:

after posting the question about the performance on the original msdn blog post i got an answer from one of the Gemini team members:

You’re correct, the Gemini embedded data engine loads data into memory. However this is only while the models are in use. Gemini’s SharePoint services manage the lifetime of these models and move these in and out of the SP content database transparently from the end users, the only realization users might have is the first time they connect to a model it takes slightly longer because we’re extracting the workbook then extracting the AS database and loading it up in memory.

Again another great blog at Teo Lachev’s Weblog, this time he creates some relative dates implementation in an Analysis Services cube, like the default time dimensions in the Cognos Powercubes. He also found a great “Translating Cognos PowerPlay Transformer Relative Date Functionality into SQL Server Analysis Services (SSAS)” whitepaper by David Greenberg. It’s a different aproach than my own using time sets to filter the data. Theo uses a Dimension where he fill the values with MDX query’s, when I have the time i’ll try this one my self, it looks great.

Read his approach at: http://prologika.com

I’m creating a SSIS package that will call a console application, and as a argument i want pass the current connection string. As i’m using a shared XML config file over multiple packages that sets my connection, i am unable to let the xml config file fill a variable.

You can use a Script task to get the connection string directly from the connection manager into a variable:

public void Main()
        {
            ConnectionManager conMgr = Dts.Connections["Miss"];
            //No connection string available, fail this step.
            if (String.IsNullOrEmpty(conMgr.ConnectionString))
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
            else
            { //Else fill the variable and return succes
                Dts.Variables["ConnectieString"].Value = conMgr.ConnectionString;
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }

Microsoft has relase a standalone version of  Report Builder 3.0, August CTP, you could start it throught the report manager already.

A small stand-alone MSI for Report Builder 3.0, August CTP, is now available for download here: http://go.microsoft.com/fwlink/?LinkID=160384

Download packages for SQL Server 2008 R2 August CTP are available here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=e19689bd-38dd-46c4-8645-f58ca4d61d1f

Microsoft released a BI update to SQL Server 2008, aside from Gemini it has some other great new functions like the Lookup Function in reporting services. I was waiting for this one, you can lookup the first matching value for the specified name from a dataset that contains name/value pairs. This gives you the opportunity to join 2 datasets and whats best from 2 different cubes!

One mayor drawback is that you can only join on one key and a SSAS dataset usually doesn’t have a single key since you have data on an x and y axel, but with some custom work we can fix that.

In the following sample I have put data of 2 adventureworks datasets on one tablix. Each dataset contains year and Sales Territory Country and a measure. Lookup uses 2 keys to match the data from 2 datasets, I made a new unique key for each dataset combining the the uniquename of the x and y axel and added it as a calculated field to each dataset, added a field to each dataset with value “Fields!Sales_Territory_Country.UniqueName + Fields!Calendar_Year.UniqueName”.

Add the value of one dataset to a tablix

Then add a new column with the following expression to add the Internet_Order_Count value from the other dataset to the tablix:

=Lookup(Fields!CombKey.Value,Fields!CombKey2.Value,Fields!Internet_Order_Count.Value,”DataSet2″)

and thus adding the value from the 2nd dataset to the tablix, works like a charm. I even tested putting a filter on dataset 2 to make sure there aren’t an even amount of values and that works too, it just returns a null value.

The Microsoft World Wide Partner Conference 2009 is just finished and loads of BI news, i made a small linkflood:

MS BI wpc round up for microsoft bi
Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!
What’s new for Analysis Services users in Excel 2010?
First impression of Excel 2010

Some great stuff in here !  I will try to get a hold of Office 2010 Tech preview to try it out myself !

Setting up security for SSRS in SharePoint integrated mode can be a bit tricky, particularly if you want to set up some of your users to only be able to run reports, but not to be able to modify or change them. I found a great post explaining how to create a Reporting group in SharePoint:

http://agilebi.com/cs/blogs/jwelch/archive/2009/07/10/view-permissions-for-reporting-services-in-sharepoint-integrated-mode.aspx

I regulary have to load some Excel sheets into a datawarehouse, what happens a lot is that some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.

This appears to be a strange behavior of the Excel ISAM driver, when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found at ms support: http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

UPDATE: another great i found at http://developers.de/blogs/nadine_storandt/archive/2006/12/05/SQL-2005-SSIS-Excel-Source-Insert-NULL-Values-for-all-Records.aspx and you could do the following:

To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:

Excel 8.0;HDR=YES;IMEX=1

The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.

Works great !