Hi,
we are on AS 2000 with sp3. We have a fact table in Sybase
11.9.2. We connect using the 12.5 Sybase ASE OLEDB driver
and it processes into a cube just fine.

When we try to drill though it takes forever on a
relatively small table, and for test purposes, only
returning 3 columns. More often than not it times out. The
queryplan i bring up on the sybase server, shows that it
is doing a tablescan instead of using the index. I do not
wish to increase the timeout, i want it to pick up the
index. The field i am drilling to is a numeric(6,0).

However, if i grab the same SQL that is captured by
auditing on Sybase, the same SQL always uses the index and
returns quickly. When i have a similar structure on SQL
Server 2000 (also with sp3) and use profiler to capture
the SQL, i see that it is making a call to sp_prepexec and
correctly typing the drill parameter as a numeric. But as
Sybase is not behaving the same (i cannot see the
sp_prepexec call) it is behaving as if it is typing it as
an integer or at least some type other than numeric(6,0)
(which seems to be the only reason that could be causing
this to happen).

SQL simply looks like:
-----------------
select the_key, the_dimension_key, the_measure from
fact_table where the_dimension_key = @the_key_being_drilled
-----------------
where the_dimension_key is a numeric(6,0)
and i believe the @the_key_being_drilled is being defined
as int (or at least not numeric(6,0), i need it to be
numeric(6,0))

How can this be fixed so that the correct type is passed
and the SQL runs picking up the index as it should?

R

Hi Rui,

Thanks for your post. As I understand, when you try to drill though the
data in the cube using Analysis Manager, the process always times out. If I
have misunderstood, please feel free to let me know.

When you drill through the data in the cube, the Analysis Services will
generate a query, which is accepted by the data source, and will be waiting
for the data source returning the Dataset. The duration of the drill
through depends on the time of the query execution on Sybase. Since I am
not familiar with Sybase's detailed information, what I can do is very
limited. At this point, I think it would be most beneficial for you to talk
to Sybase support regarding this issue.

Also, on the Analysis Services side, increasing the value of the timeout
option would be best. From what I know, by default, the value of the
timeout option in Analysis Manager is 30 seconds. Unfortunately, the
timeout option cannot be set using Analysis Manager. You have to set the
option and drill through the data using codes. The CommandTimeout method
will be used to set the value of the timeout option.

Sample codes:

/////////////////////

dim cst, cmd
Set cst = server.CreateObject("ADODB.recordSet")
Set cmd = server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Data Source=shasql92;Initial Catalog=foodmart
2000;Provider=msolap.2;"
cmd.CommandTimeout = 60
dim strSource
'strSource = strSource & "DRILLTHROUGH maxrows 1000 "
'strSource = strSource & "SELECT {[Time].[1997]} ON columns, "
'strSource = strSource & "{[measures].[supply time]} ON rows, "
'strSource = strSource & "{[Warehouse].[All Warehouses]} ON pages "
'strSource = strSource & "FROM [warehouse] "
strSource = strSource & "drillthrough "
strSource = strSource & "select "
strSource = strSource & "{[Measures].[Unit Sales]} on rows, "
strSource = strSource & "{[Promotions].[All Promotions].[Best Savings]} on
columns "
strSource = strSource & "from sales "
cmd.CommandText = strSource
set cst = cmd.execute()
Response.Write cst.fields.item(0)
response.write " "
Response.Write cst.fields.item(1)
response.write " "
Response.Write cst.fields.item(2)
response.write " "
Response.Write cst.fields.item(3)
response.write " "
Response.Write cst.fields.item(4)

///////////////////////////

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

The timeout is the symptom of the problem, not the problem
itself. I do not wish to change the Timeout. The code
should run in under 5 seconds for me, but through AS it
does not, because the index is not getting picked up,
apprently because the datatype is incorrect.

What i want to know, is on the SQL that is generated by AS
on the drillthough, why AS appears not to pick up the
correct parameter datatype.

Before i can talk to Sybase support i need to know exactly
what analysis services is passing to the Sybase OLEDB
provider (i can see what reaches the server). this will
enable me to determine where the problem is, ie. Sybase
Server, AS, or Sybase OLEDB Driver. Alternatively, is
there anyway to force AS to process that drillthrough as
the correct datatype, or even override with the correct
datatype.

Hi Rui,

Thanks for your update. Can you provide the queryplan you mentioned in the
previous post? How do you think that Analysis Services do not pick up the
correct parameter data type? As I understand, the data types used in the
query are OLE DB data type.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Queryplan when i run the SQL manually is essentially:

The type of query is SELECT.
-----------------------------
FROM TABLE
my_dimension_table
dpd
Nested iteration.
Index : idx_desk_denorm11. <------which is good & expected
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

<then the join on Fact table is using index>
---------------------------------------------

Compared to: (when i run it through AS Cube Browser
drillthrough)
---------------------------------------------
The type of query is SELECT.
-----------------------------
FROM TABLE
my_dimension_table
dpd
Nested iteration.
Table Scan <----which is bad & caused by incorr. datatype
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

<then the join on Fact table is using index>
--------------------------------

given that the SQL is identical, the only difference
causing the tablescan is the parameter, given that the
value is the same, the only thing left is the type.

When an OLEDB call is made to Sybase (or SQL Server for
that matter) a call to the RPC function sp_prepexec if the
setting is left to the dfaults in the drivers, this
creates a temporary stored proc on the fly for performance
if the query gets called again. This converts the literal
SQL parameter into a real parameter:

The below is a simplified extract from SQL Profiler from
the similar query against SQL Server:
----------------------------------------------
SQL:BatchCompleted
SET ROWCOUNT 1000
SQL:BatchCompleted
SET FMTONLY ON SELECT fact.id, fact.foreign_key,
fact.measure FROM fact, dimension SET FMTONLY OFF
RPC:Completed
declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 numeric(6,0)',
N'SELECT fact.id, fact.foreign_key, fact.measure FROM
fact, dimension WHERE (dimension.key=@P1) AND
(fact.foreign_key=dimension.key), 123 <--- the value of
the param
select @P1
RPC:Completed
exec sp_unprepare 1
---------------------------------
note the RPC call creating the numeric(6,0)

i cannot obtain this level of detail from the Sybase call
to see what AS is sending but i can see this:

------------------
SELECT fact.id, fact.foreign_key, fact.measure FROM fact,
dimension WHERE (dimension.key=@P1)
------------------

so given that if i run the same SQL direct against Sybase
and declare @P1 as a numeric(6,0) it works fine and picks
up the index, if i declare it as numeric(7,0), int etc. it
produces the bad query plan above and does not pick up the
index.

Is AS responsible for making this RPC call and therefore
the type of the parameter? or is this something done by
the sybase driver or server? If i alter the driver
settings to say never produce a temporary stored proc, it
ignores me and produces one anyway, leading me to believe
that AS is overriding/ignoring this setting or that there
is a problem in the driver that ignores this setting.

R

Hi Rui,

Thanks for your update. I noticed that the data type of the @P1 is int. I
would like you to check and see the key data type of the bottom level of
the dimension. Please make sure the key data type property of the bottom
level of the dimension is numeric.

For more information regarding the key data type property, please refer to
the following article on SQL Server Books Online.
Topic: "Properties Pane (Dimension Editor Schema View)"

Also, as I understand, the Analysis Services generally call API such as
ADO, OLE DB to query. It does not call the query directly.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi,
"key data type" for the lowest level is numeric, and "key
data size" is 19. Is this data type actually used for the
building of the OLE-DB call? I was under the impression
that this was just used for the storage of the
aggregations. (Otherwise, why would the same call be done
with the SET FMT ONLY option be made immediately
beforehand?)

R

Hello Rui,

I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you.

Thanks for posting to MSDN Managed Newsgroup.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hello Rui,

Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. You can contact Microsoft Product Support directly to
discuss additional support options you may have available, by contacting us
at 1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default...d=sz;en-us;top. If this is not
an urgent issue and your would like us to create an incident for you and
have Microsoft Customer Service Representative contact you directly, please
send email to (remove "online." from this no Spam email address):
mailto:dscommhf (AT) online (DOT) microsoft.com with the following information,

* Include "Followup: 20213043 " in the email Subject.
*Location of the post
*Issue Subject Line
* First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address

Thanks for posting to MSDN Managed Newsgroup.

Vikrant Dalwale

Microsoft SQL Server Support Professional


Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer’s security."
This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------