You are not logged in.

Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

gscholz

Intermediate

  • "gscholz" started this thread

Posts: 5

Date of registration: Nov 18th 2008

Location: Waidhofen/Ybbs

  • Send private message

1

Tuesday, November 18th 2008, 3:30pm

Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Hello!
I tried to import via ODBC from a MSSQL database sales data into a new database/cube. I created manually a new database, the dimensions (salesorg , country, branch, customer name, article, year, month and turnover). For all dimensions ex. turnover I created values for the dimensions.

I started the ODBC import with =PALO.SETDATA(H2;WAHR;"localhost/ITF";"Cube1";A2;B2;C2;D2;E2;F2;G2) where H2 is the turnover, A2 (salesorg, country, branch, customer name, article, year, month).

I always geht #WERT! and the the cube is empty. What's wrong?

Perhaps you can help me as a new PALO user.

Thanks,

Gunnar

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

2

Tuesday, November 18th 2008, 4:39pm

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Hi,
What does the query return in row 1?
Columns in row 1 should be filled by query.
Please attach a screenshot.

Holger

This post has been edited 1 times, last edit by "h_decker" (Nov 18th 2008, 4:41pm)

gscholz

Intermediate

  • "gscholz" started this thread

Posts: 5

Date of registration: Nov 18th 2008

Location: Waidhofen/Ybbs

  • Send private message

3

Tuesday, November 18th 2008, 4:56pm

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Hi!
Yes, the data is returned in line 1. Pls find attachments. The formular is
=PALO.SETDATA(H1;WAHR;"localhost/ITF";"Cube1";A1;B1;C1;D1;E1;F1;G1)
Gunnar
gscholz has attached the following images:
  • definition1.jpg
  • definition2.jpg
  • import.jpg

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

4

Tuesday, November 18th 2008, 5:05pm

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

I see that "cube1" has an 8 dimensions and you only got 7 elements returned in your query.

If cube only contains Umsatz-data==> rebuild the cube with 7 dimensions. Then change WAHR in your PALO.SETDATA formula into FALSCH because you don't want to splash on consolidated elements. Then try again.

Hope this helps.

Explanation:
The 7 cols left to the value must match the dimension elements.
PALO.SETDATA must have them in the right order.

Holger

gscholz

Intermediate

  • "gscholz" started this thread

Posts: 5

Date of registration: Nov 18th 2008

Location: Waidhofen/Ybbs

  • Send private message

5

Tuesday, November 18th 2008, 10:00pm

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Hallo!

Yes, this was my problem. Thanks! The "Umsatz" is not allowed as part of the cube.

Gunnar

gscholz

Intermediate

  • "gscholz" started this thread

Posts: 5

Date of registration: Nov 18th 2008

Location: Waidhofen/Ybbs

  • Send private message

6

Wednesday, November 19th 2008, 7:15am

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Good Morning!

After a long night session I found my self another time in trouble. I had to swith the data source due to wrong data in my source database. I rebuild a new PALO database ITF1 with a new Cube1. The number of dimensions was reduced - see first attachment (datastructur1.jpg). I started than again the import via ODBC source to receive this dataset (dataset1.jpg). When I now insert the PALO.SETDATA formular "=PALO.SETDATA(F1;WAHR;"localhost/ITF1";"Cube1";A1;B1;C1;D1;E1)" I get #NAME?.

F1 = Turnover (Umsatz)
A1 = SalesOrg
B1 = Branch
C1 = Customer
D1 = Materiagroup
E1 = Year

It is interssing, that the attributes of the Cube1 has data in it? (cube1.jpg)

Is my Palo installation wrong, or do I have to restart my PC periodically to refresh the memory?

Gunnar
gscholz has attached the following images:
  • datastructur1.jpg
  • dataset1.jpg
  • cube1.jpg

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

7

Wednesday, November 19th 2008, 9:24am

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Good Morning Gunnar,

For the exmpale row you attached please check if

Dimension VorGruppe got a base element 31
Dimension Branche got a base element BAUTEILH.
Dimension Kunde got a base lement Laird Technologies s.r.o.
Dimension Warengruppe got a base element FSMT

Dimension Jahr got an base element 2007

Then change WAHR in your PALO.SETDATA formula to FALSCH (use TRUE or FALSE) in english excel installations. True means that you want to splash a value (if at least one of the coordinates is a consolidated elements) but I think you only want to import base values, right?

Then try to import the exmpale row. If the formula works it will return the import value.

"Anzahl der Zellen" (number of cells) in your cube properties attachment means that there are 813050 cells in your cube. It does not mean that there is any data in it. You would see that in the line below ("Anzahl der gefüllten Zellen" "Number of filled cells").

When you multiply the number elements for every dimension you should get the amount above. This is the number of cells according to the coordinates in your dimensions.

Try to place the PALO.SETDATA formula in A2 if the hints above don't have an effect.

Good Luck.

Holger

gscholz

Intermediate

  • "gscholz" started this thread

Posts: 5

Date of registration: Nov 18th 2008

Location: Waidhofen/Ybbs

  • Send private message

8

Thursday, November 20th 2008, 9:25pm

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

Hi!

Sorry, for late reply. I still work on my "final" solution. Your help was indeed very useful and I have currently no problem to import data without #Name? or #Wert?. But, i don't receive the turnoverdata "Umsatz"?
I includef two screenshots - one directly with the SQL statement in the MSSQL Studio and the other with the same data in XLS/Palo. I checked the dimensions and there values. All seems fine but the last, important part, is not really misssing but I don't find cons. value. I have to open excatlly all vaule for the date describtion to receive the data? What is wrong. I checked the cell of the "umsatz" and the cell where the palo.setdata formular is located. Both have the number format?

Gunnar
gscholz has attached the following images:
  • sql.jpg
  • palo.jpg

This post has been edited 1 times, last edit by "gscholz" (Nov 20th 2008, 9:27pm)

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

9

Friday, November 21st 2008, 9:21am

RE: Data Import > ODBC > MSSQL > Palo 2.5 with XlS 2007

That means that at least one yof the element in the view is String Element. For saving numeric data all cells of the coordinate must be numeric. Typical mistake is that people think they have to choose Type String for elementnames of type string. This is wrong.

Here is one description. Lets say there is a cube with 3 dims

Dimension Year with elems
2008
2009
2010

they are all numeric


Dimension Department with elems
Department A
Department B
Department C

they are all numeric


Dimension Datatype with elems

Turnover
Cost of Sales
Comment

Turnover and Cost of sales are numeric.
Comment is type string. There you can save text data.


Hope that helps.


Holger

This post has been edited 1 times, last edit by "h_decker" (Nov 21st 2008, 9:22am)

Rate this thread