Basic Key Field question

I'm not sure if this is an SSAS question or an SSIS question.....I'll start here with basic theory/SSAS question.....

Simple design:

dimPriceBasis

PriceBasisKey (PK,int,not null)

PriceBasisCode (int,null)

PriceBasis (varchar(50),null)

dimTime

TimeKey (PK,int,not null)

AltDateKey (datetime,null)

factOrders

OrderDateKey (FK,int,not null)

ShipDateKey (FK,int, not null)

PriceBasisKey (FK,int, not null)

OrderID(bigint,not null)?

Amount (float, null)

Price (money,null)

I am having trouble conceptualizing key field set up. I had this cube up and running yesterday but then I started thinking about it and now I'm lost. Foreign keys in the fact table are supposed to be integer (counters) that point to integer primary keys in the dim tables. If you then combine the FK fields to serve as the primary key of the fact table I am confused when I go to load data as to how I'll generate integer counters based on the data in my OLTP Order table that will provide unique links to my dim tables and provide a unique key for the fact table. (OrderID is the OLTP table primary key.) All the data is basically coming out of one table for now.

My next question would be how to generate surrogate keys but that would be an SSIS question.

"If you then combine the FK fields to serve as the primary key of the fact table " - not sure why - it's not always applicable. For example, in your case, couldn't there be multiple orders with the same PriceBasis/OrderDate/ShipDate combination?

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian

Backward compatibility with AMO clients

We are an ISV whose application uses AMO (and ADOMD.NET) for manipulating SSAS cubes. I tried running our app against SQL Server 2008 (June CTP) and it failed (as I expected) because it is bound to the version of AMO that comes with SQL Server 2005 (9.0.x.x), and the assembly version numbers were bumped to 10.0.0.0 for 2008.

I installed the XMO libraries from the 2005 feature pack and tried those. The assemblies loaded, but when AMO tries to connect it gets the error:

An error occurred creating the OLAP database: Microsoft.AnalysisServices.ConnectionException: Cannot connect to Analysis Services version '10.0.1019.17'.
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
at Microsoft.AnalysisServices.Server.Connect(String connectionString)
...

I tried adding binding redirects from the 9.0 version to the 10.0 version of the libraries, and that seemed to work (at least for the minimal testing I've done so far). Of course, I have no idea how compatible the 10.x and 9.x libraries actually are.

I'm curious what Microsoft's general philosophy around compatibility between 2005 clients and 2008 clients is going to be. What will be the officially recommended approach for supporting both platforms (assuming it is supported)?

Hi,

The problem with AMO version 9 being unable to connect to servers 10+ will be fixed in a next cumulative update for SP2.

Meanwhile, the work-around is the one you use: .config files to redirect from 9.0 to 10.0.

AMO version 10.0 is backward compatible, you can use it to connect to AS2005; BI Development Studio version 10.0 also connects to AS2005.

Thank you for posting the feedback,

Adrian Dumitrascu

|||Thanks Adrian.

Will there be a refresh for the Feature Pack components that include that fix? Or can rollups be applied to feature pack installs?

|||

The AS2005 Feature Pack will not be refreshed. The AMO fix will be included in the next Service Pack (if any) and sooner in the next cumulative update for SP2. If you cannot install the full cumulative update, it will work to just install to GAC the AMO (Microsoft.AnalysisServices.dll).

Adrian Dumitrascu

|||

Adrian,

Here's the scenario I'm concerned about. A customer installs our software for use with a server running SSAS 2008. They need the 9.x version of AMO (since that's what we've built against), and since the SQL Server 2008 client tools installs version 10, their only option is to download the Feature Pack components. However, the roll-up won't update components installed through the feature pack install (at least, the latest one doesn't - I tried it). At this point, the customer is hosed - they have no real way to get a version of AMO that works with SSAS2008. The only solutions are to install the binding redirects (ugly) or hack in a patched AMO client assembly (uglier).

Please consider this sort of scenario in your planning. Take pity on us poor ISVs.

|||

Hi,

I forwarded the message to our team, I'll post new info once I have it.

At the moment, none of the SP2 cumulative update builds at http://support.microsoft.com/kb/937137 have the AMO fix (the latest build is 09.00.3182 now). But the next build should have it (you can download it and install from it just the AMO assembly, this will be the work-around until we'll refresh the Feature Packs, if we'll be able to do that).

Adrian