Linked OLAP Server is loosing rows
-
Hi everybody,
I'm using a linked server to access Analysis Services cubes and join the result to normal SQL Server tables. We are running SQL Server 2000 SP3.
This normally works fine, but now the following problem showed up:
I have a parent/child dimension called KeyCustomerSaleslevels. The lowest level is the KeyCustomer. If I'm selecting data for a single KeyCustomer by his ID, this works fine in most cases, but sometimes, no data is returned.
This select does not return any rows:
select
* from
OPENQUERY( OLAPSALES, 'SELECT ...
, [KeyCustomerSaleslevels:KeyCustomer!key]
, [KeyCustomerSaleslevels:KeyCustomer!Name]
, ...
, SUM([measures:Sales]), SUM([measures:Cost])
FROM AccessSalesAnalys WHERE
([KeyCustomerSaleslevels:KeyCustomer!Key] = 1253)
GROUP BY ...
, [KeyCustomerSaleslevels:KeyCustomer!key]
, [KeyCustomerSaleslevels:KeyCustomer!Name]
, ...')
This select, which filters outside the cube query, returns rows, but is much slower (of course):
select * from
OPENQUERY( OLAPSALES, 'SELECT ...
, [KeyCustomerSaleslevels:KeyCustomer!key]
, [KeyCustomerSaleslevels:KeyCustomer!Name]
, ...
, SUM([measures:Sales]), SUM([measures:Cost])
FROM AccessSalesAnalys
GROUP BY ...
, [KeyCustomerSaleslevels:KeyCustomer!key]
, [KeyCustomerSaleslevels:KeyCustomer!Name]
, ...')
WHERE
([KeyCustomerSaleslevels:KeyCustomer!Key] = 1253)
I've also tried filtering on KeyCustomer!Key OR KeyCustomer!Name in the cube query and then once more on the key outside the cube query, which again works for some cases, but not for all (and it did definitely not work if the name was not unique).
There are no problems selecting on parent levels of the dimension.
Does anyone have an idea or similar experiences?
Thank you and best regards
Christian
-
Off-the-wall-question, what size is your 'Large Level set to? The default is 1000 and if a level has greater than 1k members, then not all the members (meta-data) are downloaded to the client, which in turn could mean that the query can be rejected when parsed (the member specified "doesn't exist" because it's not in the meta-data. If you get time, check out pg 188 in Spoffords 'MDX Solutions' which explains this better. To test this theory, you could try using the where clause (in MDX, not the external one) with a lower level of uniqueness. The example George gives is:
[Customer].[Cust ID].&[1022834] versus [Customer].[All Customer].[CDN].[Alberta].[Calgary].[1022834], where the second member declares the metadata to the client tier and will therefore be found.
Steve.
-
Hello Steve,
we increased "Large Level" to 2000 (we have about 1400 Key Customers defined) and ... it works
!Thank you very much, I have no idea how I should have figured this out.
I hope we won't find any other "Microsoft Information hiding effects" like this.
Christian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply