Are OLEDB data types restricted in PowePivot?

Question

Hi there,

we are trying our OLEDB Provider (own developed for SAP tables) with PowerPivot and it seems that some of the OLEDB datatypes are not supported.

When I try to import a certain table I get this message:

The following system error occurred:  
The operation has been cancelled.

The same table works on the "preview and filter" page well!

When I am using only string columns from this table it also works.

So I think the problem will arise with some OLEDB datatypes (DBTYPE_DBTIMESTAMP, DBTYPE_NUMERIC).

Any Ideas or documentations?

Thanks,

Andi

 

Answer 1

Hi Andi - can you export this table using SSIS (or any other tool) into CSV file? This looks more as data issue.

If you can post here table definition and your database version - I will try to reproduce your error.

Thanks, Vlad.

Answer 2

Hi Vlad,

sorry for the delay - I have tested a lot of things.

First - I can not give you a table definition because my OLEDB provider extracts data from a SAP system (= source).

And it is definitly NOT a data issue, because the error occurs when gathering the meta data for the colums - before any data is fetched. 

The problem with PowerPivot looks like this:

• it only occures if I am extracting a numeric (DBTYPE_NUMERIC) AND a timestamp (DBTYPE_DBTIMESTAMP)  type together
• after reading the column metadata (interface IColumnsInfo::GetColumnInfo) i get this error:
  The following system error occurred:  
  The operation has been cancelled.
  
• Just numeric columns work well / Just timestamp columns work well / The Preview with both combined also WORKS!?!
• The whole thing works very well in SSIS packages no problems with those types

 The only thing I can post is the data of my ATLCOLUMNINFO structure, which is provided by my OLEDB Provider (interface IColumnsInfo::GetColumnInfo)

Ordinal	Name	Type	Precision	Scale	Offset	ColumnSize
0	__BOOKMARK__	DBTYPE_UI4	10	255	0	4
1	CONNID	DBTYPE_NUMERIC	4	0	4	19
2	FLDATE	DBTYPE_DBTIMESTAMP	16	3	23	16

The Flags for Ordinal 1 and 2 are set to DBCOLUMNFLAGS_ISNULLABLE | DBCOLUMNFLAGS_MAYBENULL | DBCOLUMNFLAGS_ISFIXEDLENGTH | DBCOLUMNFLAGS_WRITEUNKNOWN.

If you are not able to help me - where could I get support for this issue?

Thanks on advance,

Andi

Answer 3

Andi, thanks for the detailed information. Obviously you can use SSIS package to prepare your data but I understand that this is inconvenient. Let me do some research on this error and I will get back to you.

Regards, Vlad.

 

Answer 4

Hi there,

seems PowerPivot was designed by a different team than Excel 2010.

When I try to import my data via my OLEDB Provider into an "normal" Excel 2010 Sheet this works perfect:

Also a preview in PowerPivot works perfekt

- but when Importing the whole thing I get a not very meaningful error:

 

It seems that type DBTYPE_NUMERIC and DBTYPE_DBTIMESTAMP (or DBTYPE_DBDATE) are facing a problem while validating the metadata.

 

I think we have to open a support ticket....

 

Anybody a power pivot team any suggestions?

 

Thanks, Andi

Answer 5

Hi again,

the funny thing is that the table wizard design dialog shows the correct data (NUMERIC and TIMESTAMP field):

But importing those field together I will receive the error of my previous post.

We want to release a seeding version of our OLEDB provider with the release of Excel 2010 powerpivot - so we have to hurry!

Thanks on advance,

Andi

Answer 6

Hi Andy, can you try to cast CONNID as integer? PowerPivot is using column compression and will attempt to compress these values based on the columns content. Looks like numeric 19 is currently storing integer value which will fit into 4 bytes integer. Same is for the FLDATE field - can you convert it to the DATE from Timestamp? These will be changes to your query only.

Thanks, Vlad.

Answer 7

Hi Vlad,

I can not convert the data types by sql because the backend of my SQL statement is a SAP system which understands only Open SQL. 

But anyway this could just be a work around for this case - but imagine really big decimal types with a lot of digits - they never fit into a four byte integer.

I tried to implement a mapping for small DECIMAL types to  (DBTYPE_UI4, DBTYPE_UI8 and DBTYPE_R4, DBTYPE_R8 for types with scale) and instead of DBTYPE_TIMESTAMP using a DBTYPE_DBDATE or DBTYPE_DBTIME.
But this did NOT work either!

And now - hold on tight!

It is only the ordinal number of the date columns that matters! When pushing them at the end of the columns everything works fine!

I just undo my changes - and it also works with NUMERIC types in between.

Hey guys this really seems to be a bug in PowePivot!

Thanks,

Andi

Answer 8

Hi there again,

The import of NUMERIC and DATETIME fields work fine with Microsoft's own OLEDB Provider for SQL Server.

So I used MS RowsetViewer to figure out how "MS OLEDB Provider for SQL Server" will build up its ColumnInfo and build up mine in excatly the same way.

But I am always facing the error described before: columns of type DBTYPE_TIMESTAMP (or DBTYPE_DBDATE or DBTYPE_DBTIME)
MUST be at the end of a row that the extraction work.

Could this be an untested part of Microsoft PowerPivot?   Has Microsoft added a special handling for its own Providers into PowerPivot?

Please help me!

Andi

Answer 9

Hi Andi, are there any NULLs in the Date fields? Can you filter them out in your original query and reproduce the error?

I don't believe there are any changes to the providers, but PowerPivot has it's own logic to compress and store data in memory. It will be important to know if this issue related to the data in columns (NULLs etc.)

Thanks, Vlad.

Answer 10

Vlad,

I tried a SQL in which I permited any NULL values: 

SELECT
CONNID,   -- NUMERIC
BOOKID,

MANDT,   -- CHAR
CARRID,

FLDATE,   -- DATE
ORDER_DATE

FROM SBOOK
WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL

 

Remind: The dates are at the end of my selection list ==> This works!

Now I push em to the front:

SELECT
FLDATE,      -- DATES
ORDER_DATE,

CONNID,    -- NUMERIC
BOOKID,

MANDT,    -- CHARS
CARRID

FROM SBOOK
WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL

 

And *bam* I get this error: The following system error occurred:   The operation has been cancelled.

By the way - this error occures BEFORE any data is fetched - so this seems to be a metadata issue!

 

Conclusion:

I figured out that PowerPivot ist NOT able to import these in OLEDB defined standard types or to import them with their complete Precision and Scale:

• DBTYPE_NUMERIC   
• DBTYPE_DECIMAL
• DBTYPE_DBTIME
• DBTYPE_DBTIMESTAMP

 

My only workaround ist to map the types in my OLEDB provider to fitting smaller types like DBTYPE_UI4 etc. or to map them to strings (which is very poor).

Here a few basic questions:

• Why does PowerPivot not support all OLEDB standard types (defined in Microsofts own standard)?
• If PowePivot does not support all types - why they do not provide a message which columns will not work and why?
• Why does PowerPivot not ignore unsupported columns (at the moment the whole import canceled)?

Thank you,

Andi

Answer 11

Hi Andy, thank you for detailed analysis. Just to double-check:

This statement :

SELECT
FLDATE,      -- DATES
ORDER_DATE,

CONNID,    -- NUMERIC
BOOKID,

MANDT,    -- CHARS
CARRID

FROM SBOOK
WHERE BOOKID < 100 AND FLDATE IS NOT NULL AND ORDER_DATE IS NOT NULL

Works anywhere else , but fails in PowerPivot?

Can you try to replace Microsoft OLEDB provider for SQL Server with any alternative one? We need to rule out problem with provider.

Thanks, Vlad.

Answer 12

Hi again,

meanwhile I have done a lot of research and tests.

Yes - this selection works perfekt in preview (table import wizard) of PowerPivot and also in SSIS packages.

I tested an alternativ OLEDB provider - Postgres DB (OpenSource) and a commercial OLEDB Provider for Postgres DB.

When I build up the same table metadata and filled it with exactly the same data - it works :(

Ok - so I build the metadata of our OLEDB provider 1:1 to the metadata of Postgres OLEDB provider (using RowsetViewer to compare the all rowsets, etc.)

But it finally does not work - in my OLEDB provider the data will only be imported when all timestamp fields are at the end.

Any ideas what I can do ?

Is a test software for OLEDB providers availlable?

When I use my OLEDB provider in a C# sample project it works perfect and all data is returned correct.

Thanks for any help,

Andi

Answer 13

HI Andi, can you please post metadata which were generated for Postgres and then modified (failing) ones?

Thanks, Vlad.

I tried with all kind of Date columns in SQL 2008 and was not able to reproduce the issue. I was using SQL Server Native Client 10.0

Answer 14

Hi Vlad,

you can not reproduce this error with MS OLEDB providers.

The error occurs when PowerPivot gathers and validates my metadata. But I cannot find our what is wrong because I only get the messag:

The following system error occurred:   The operation has been cancelled.

Below I post the schema rowsets of my provider and Postgres OLEDB provider.

The only difference is that some values of my provider returns 0 instead of a null value. But I think this might not matter.

I am working against a black box and it will help me to know which interfaces and schemarowsets are called by the PowerPivot Import-Task.

---

My Providers Columns Schemarowset

TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_FLAGS	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION
SFLIGHT	MANDT	1	120	True	DBTYPE_WSTR	3	6	0	0	0
SFLIGHT	CARRID	2	120	True	DBTYPE_WSTR	3	6	0	0	0
SFLIGHT	CONNID	3	120	True	DBTYPE_NUMERIC	0	0	4	0	0
SFLIGHT	FLDATE	4	120	True	DBTYPE_DBTIMESTAMP	8	8	0	0	0
SFLIGHT	PRICE	5	120	True	DBTYPE_NUMERIC	0	0	20	2	0
SFLIGHT	CURRENCY	6	120	True	DBTYPE_WSTR	5	10	0	0	0
SFLIGHT	PLANETYPE	7	120	True	DBTYPE_WSTR	10	20	0	0	0
SFLIGHT	SEATSMAX	8	120	True	DBTYPE_I4	0	0	10	0	0
SFLIGHT	SEATSOCC	9	120	True	DBTYPE_I4	0	0	10	0	0
SFLIGHT	PAYMENTSUM	10	120	True	DBTYPE_NUMERIC	0	0	22	2	0
SFLIGHT	SEATSMAX_B	11	120	True	DBTYPE_I4	0	0	10	0	0
SFLIGHT	SEATSOCC_B	12	120	True	DBTYPE_I4	0	0	10	0	0
SFLIGHT	SEATSMAX_F	13	120	True	DBTYPE_I4	0	0	10	0	0
SFLIGHT	SEATSOCC_F	14	120	True	DBTYPE_I4	0	0	10	0	0

---

Postgres Columns Schemarowset

TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_FLAGS	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION
SFLIGHT	MANDT	1	120	True	DBTYPE_WSTR	3	6
SFLIGHT	CARRID	2	120	True	DBTYPE_WSTR	3	6
SFLIGHT	CONNID	3	120	True	DBTYPE_NUMERIC			4	0
SFLIGHT	FLDATE	4	120	True	DBTYPE_DBTIMESTAMP	8	8
SFLIGHT	PRICE	5	120	True	DBTYPE_NUMERIC			20	2
SFLIGHT	CURRENCY	6	120	True	DBTYPE_WSTR	5	10
SFLIGHT	PLANETYPE	7	120	True	DBTYPE_WSTR	10	20
SFLIGHT	SEATSMAX	8	120	True	DBTYPE_I4			10
SFLIGHT	SEATSOCC	9	120	True	DBTYPE_I4			10
SFLIGHT	PAYMENSTSUM	10	120	True	DBTYPE_NUMERIC			22	2
SFLIGHT	SEATSMAX_B	11	120	True	DBTYPE_I4			10
SFLIGHT	SEATSOCC_B	12	120	True	DBTYPE_I4			10
SFLIGHT	SEATSMAX_F	13	120	True	DBTYPE_I4			10
SFLIGHT	SEATSOCC_F	14	120	True	DBTYPE_I4			10

---

And here is the metadata of the other interface "IColumnsInfo::GetColumnInfo"

Ordinal Name Type Precision Scale Offset ColumnSize

0 __BOOKMARK__ DBTYPE_UI4 10 255 0 4

1 MANDT DBTYPE_WSTR 255 255 4 3

2 CARRID DBTYPE_WSTR 255 255 12 3

3 CONNID DBTYPE_NUMERIC 4 0 20 19

4 FLDATE DBTYPE_DBTIMESTAMP 19 0 39 16

5 PRICE DBTYPE_NUMERIC 20 2 55 19

6 CURRENCY DBTYPE_WSTR 255 255 74 5

7 PLANETYPE DBTYPE_WSTR 255 255 86 10

8 SEATSMAX DBTYPE_I4 10 255 108 4

9 SEATSOCC DBTYPE_I4 10 255 112 4

10 PAYMENTSUM DBTYPE_NUMERIC 22 2 116 19

11 SEATSMAX_B DBTYPE_I4 10 255 135 4

12 SEATSOCC_B DBTYPE_I4 10 255 139 4

13 SEATSMAX_F DBTYPE_I4 10 255 143 4

14 SEATSOCC_F DBTYPE_I4 10 255 147 4

Answer 15

Hi Andy, thank you! It will take me some time to build, I will update you on my results.

Best regards, Vlad.

Answer 16

Vlad,

I have experimented with the order of the date (DBTYPE_TIMESTAMP).

In pseudo SQL code the following came out:

SELECT <TIMESTAMP>, <WSTR>, <NUMERIC> FROM ...  => works

SELECT <WSTR>, <TIMESTAMP>, <NUMERIC> FROM ...  => works NOT

SELECT <WSTR>, <NUMERIC>, <TIMESTAMP> FROM ....  => works

SELECT <WSTR>, <TIMESTAMP>, <WSTR>, <NUMERIC> FROM ...  => works

Conclusion: TIMESTAMP field followed by a numeric field produces the error. If a char field is between this works!

I thought maybe there will be data overriden through wrong offsets or lengths - but the method where data is loaded is never called.

So this must be a pure metadata issue. Maybe this information leds to a solution.

Thank you,

Andi

Answer 17

Hi Andi, I'm trying to reproduce the issue in my lab, which version of SAP are you using?

Also can you give me details about your client software, drivers etc?

Thank you, Vlad.

Answer 18

Vlad,

this sounds good! At the moment I am building a Dummy OLEDB Provider to reproduce this issue without a SAP system and other software.

How may I provide you the dummy provider and description to reproduce?

Thank you,

Andi

Answer 19

Hi Andi, you can use vlad_social@hotmail.<NOSPAM>com

Please, remove <NOSPAM>.

I already have access to SAP, it should not take long until I will be able to repro your issue.

Thanks, Vlad. 

Answer 20

Vald,

I sent you a dummy provider to reproduce this case with a SQL Server as backend database.

Good luck, Andi

Answer 21

Hi Andi, thank you I received your email. WIll update you on my results later today.

Best regards, Vlad.

Answer 22

Sorry, it took me longer than I expected when I tried SQL provider I got this error:

"OLE DB or ODBC error: Conversion failed when converting the nvarchar value 'AA' to data type int.; 22018.

An error occurred while processing the 'Query' table.

The operation has been cancelled."

My query looked as follows:

 

"SELECT FLDATE, CONNID, MANDT,  CARRID

FROM SFLIGHT
WHERE CONNID < 100 AND FLDATE IS NOT NULL "

I will check into this error, but at this point it does look like metadata issue. However when I run this query in Management Studio and then tried to reproduce the error again query was executed without any errors.

With the dummy provider I was able to reproduce the problem.

Thanks, Vlad.

 

 

Answer 23

Vlad,
I do not have a conversion error for my queries (but I do not use any WHERE statements there). Maybe the dummy provider has a gap there.

I am proud that you can reproduce my case and "feel my pain".

I hope for suggestions to avoid this metadata problem - because we want to release our product with the launch of Office 2010 in Europe.

Waiting for help...

Andi

Answer 24

Hello Andi, after making decent number of tests I can tell that issue is 100% reproducible with your provider, but I cannot reproduce it with anything else. The first error I got might be related to the low memory conditions on my VM, and I never was able to reproduce it again.

So here is the plan: attach PowerPivot to the debugger and check metadata being sent in "working" and "failing" queries.

I suspect that something is not transferred properly when first field is of the Date type.

If you want, you can email me symbols for your demo provider and I will take look on it too.

Thanks, Vlad.