AS400 to SQL with different MEMBER name

I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.

The name of the file is:

Library = MTGLIBP2

File Name = CHSAVQPL

Member Name = INS

this is the query I have so far but I still need to reference the Member Name

SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')

I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.

The name of the file is:

Library = MTGLIBP2

File Name = CHSAVQPL

Member Name = INS

this is the query I have so far but I still need to reference the Member Name

SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')

I found the solution to my problem. I needed to use SQL Enterprise Manager to set up a DTS Package.

The first step is to link AS400 server and SQL, then set up the first query using AS400 and create an alias for the file I needed to use.

example: CREATE ALIAS qgpl.mydata FOR mtglibp2.chsavqpl (INS) the INS is the Member I need to use.

The second step is to transfer the data to SQL by using the following query :

SELECT * INTO MYTABLE

FROM OPENQUERY (AS400PL,'SELECT * FROM QGPL.MYDATA') -- (This will transfer only the data with the Member Name specifiedd)

I hope this helps. : )

AS400 member

I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.

Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.

If all else fails, I will just construct my correlated sub-query.

Thanks for the information.

I assume you are using a multi-member physical file PF(the AS400 refers to files rather than tables). The default member is *FIRST and generally speaking they are only created with a single member (you could treat it like a normal table). Multi-member is a throw back to the days of card files and tape files where you had header/detail/trailer records (typically banks/insurance) with different record layouts (long before SQL was conceived. the 70s). You can get a list with the command DSPFD Library/File *MBRLIST

If you want to retrieve data from the above Library/File.member in SQL you can try the following (i have never needed to run sql on multi-member files)

Select * from Library/File.member OR Select * from Library/File(member) OR

Select * from Library.File(member)

Let me know if you need more help

|||

These types doesnot work.

I have a situation. Its like we to migrate from IBM DB2 to SQl Server. But how will we move mulri membered files? Can anyone help?

|||

Just make sure you have the latest Client Access (OLEDB) drivers for DB400. there is a user group for As400 (now called iSeries) where i am sure u will get resolution (comp.sys.ibm.as400.misc)

As far as migration is concerned

You will have 2 kinds of multi-member files.

Each member has the same columns

AS2005. Distinct count meausure + aggregate seems to be a bug.

Look at following Query

-- the first query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count],[Measures].[Internet Sales Amount]})

ON ROWS

FROM [Adventure Works]

--and the second query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count]})

ON ROWS

FROM [Adventure Works]

The first query gets wrong result for the distinct count measure, the second one gets the right result.

I have tested it against build 2184 and buld 2195. Both have the same result.

Is the bug already fixed in SP2?

Yes, the problem was fixed in SP2. A public CTP release is scheduled to come out in a few weeks.|||

It would be more exectly if you wrtie "is scheduled to come out in a few weeks - 1", because you wrote more then a week ago the same words "a public beta release is scheduled to come out in a few weeks."

|||Nothing has changed to the release schedule since last time but unexpected things could happen before the software is actually released. Just don't want to promise you a specific date since it is out of my control, or any individual's control for that matter.

AS2005. Distinct count meausure + aggregate seems to be a bug.

Look at following Query

-- the first query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count],[Measures].[Internet Sales Amount]})

ON ROWS

FROM [Adventure Works]

--and the second query

with

member [Promotion].[Promotion Category].[No Discount und Reseller] as

aggregate({[Promotion].[Promotion Category].&[No Discount], [Promotion].[Promotion Category].&[Reseller]}, Measures.CURRENTMEMBER)

SELECT ADDCALCULATEDMEMBERS([Promotion].[Promotion Category].members)

ON COLUMNS,

CROSSJOIN({[Product].[Category].[All Products],[Product].[Category].[All Products].children},

{[Measures].[Customer Count]})

ON ROWS

FROM [Adventure Works]

The first query gets wrong result for the distinct count measure, the second one gets the right result.

I have tested it against build 2184 and buld 2195. Both have the same result.

Is the bug already fixed in SP2?

Yes, the problem was fixed in SP2. A public CTP release is scheduled to come out in a few weeks.|||

It would be more exectly if you wrtie "is scheduled to come out in a few weeks - 1", because you wrote more then a week ago the same words "a public beta release is scheduled to come out in a few weeks."

|||Nothing has changed to the release schedule since last time but unexpected things could happen before the software is actually released. Just don't want to promise you a specific date since it is out of my control, or any individual's control for that matter.

AS2005 Member Properties - MDX Query

Hi,

I am having a bit of a problem with retreiving member properties from our AS2005 cube. I'm using MDX query's in a VBA Excel app via ADO (not ADOMD). I've been prototyping the queries in the SQL Server Management Studio.

I currently have the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This works fine, however I now want to CrossJoin the measures [CR 01 Net Skw Adj USD] and [JTD 01 USD] with a dimension called CS Tenor in order to give me a seperate value for each Tenor.

So far the only way I can do this is with the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} * {[CSTenor].[CS Tenor].MEMBERS}

ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This has the unfortunate side effect of CrossJoining the Portfolio member property with Tenor as well, resulting in 9 unwanted Portfolio columns (there are 9 members in CS Tenor). In reality the problem is more severe as I have around 10 member properties in the query, I reduced it to 1 here for simplicity.

My question is: Can I bring back member properties on the Rows axis? and a seperate question is: Can I CrossJoin some measures and not other on the columns axis, for example, could I CrossJoin [CR 01 Net Skw Adj USD] with CS Tenor, but not [JTD 01 USD] ?

Many thanks!

I don't believe you can cross join some measures and not other on the column axis but assuminng portfolio is an attribute of your Trade dimension the you can try this on your rows axis:

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso],strtomember("[Trade].[Portfolio].[" + [Trade].[Trade By Source System].CurrentMember.Properties("Portfolio") + "]") ))

Hope this helps.

V

|||

Actually, you should be able to do what you want on the column axis by simply constructing the full set of tuples that you want back. Here's an example from Adventure Works:

select

{ [Date].[Calendar Year].Members } on rows,

{ { [Measures].[Internet Sales Amount] } * { [Product].[Category].Members }, ( [Measures].[Internet Gross Profit], [Product].[Category].[All Products] ) } on columns

from

[Adventure Works]

In this query, I'm just pulling back calendar years on rows. On the columns, I'm pulling back the [Internet Sales Amount] measure cross joined with the product categories along with the [Internet Gross Profit] measure -- but with it coming back only with [All Products], not all the product cateogries. The only caveat is that all the tuples returned have to have the same dimensionality. Thus the need to return [Internet Gross Profit] with a member (in this case, the [All Products] member) of the same hierarchy used in the cross join that generates the first set of tuples.

In your case, you'd want something like this (not knowing exactly what your [All] member is, I just called it [All CS Tenors]):

{ { [Measures].[CR 01 Net Skw Adj USD] * [CSTenor].[CS Tenor].Members }, ( [Measures].[JTD 01 USD], [CSTenor].[CS Tenor].[All CS Tenors] ) } on columns

HTH,

Dave Fackler

|||

Hi,

Sorry for the delay in replying, I've been on vaction.

Many many thanks for this solution, it works perfectly!

Happy New Year,

Stuart

AS2000 - Different default dimension member according to cube

Dear all,

Is it possible, in AS2000, to choose a different default dimension member according to the cube we are looking at? I'm in a project where the Accounting cube should open on the last closed month and the sales cube on the current month. Is this achievable?

Regards,

Pedro Martins

Portugal

Hello. This is from my(weak) memory. If this is possible in SSAS2000 you should be able to set the default member on the dimension in the cube editor.

Have you checked this?

I do not think it is possible if you use a shared time dimension in two cubes. I think that you set the default member in the dimension editor(advanced properties).

One solution is to build two separate time dimensions, shared or cube.

Another approach depends on if you have a third party tool for building reports on top of SSAS2000, like ProClarity. If this is the case you can build two different named sets in each cube(on the same shared dimension) that points to the different default members, and use them in the tool.

Regards

Thomas Ivarsson

Arithmetic Operation with Dates

Hi I'm trying to get the difference between two dates. I need to create a Calculated member to show it. I'm using this formula

PeriodDays = LastDate - FirstDate

For example, I have a Time Dimension, I choose two dates:

FirstDate = 07/04/2006 it's identity is 300

LastDate = 15/04/2006 it's identity is 308

PeriodDays = 308 - 300 = 8 this value I need to show in a Calculated Member

The Time dimension has three levels

Year, month and date

What happens when I choose only a month?

Thanks

Dear,

I dont understand onething u are talking about date diffrence and u are giving an identity example.

Pls explain in detail.

Regards

Sufian

|||

Hi, ohh, I will try to explain again

I need to get how many days are between two dates, For example, I choose two dates from my Time dimension

Date1 = 07/04/2006

Date2 = 15/04/2006

Days = Date2 - Date1 = 15/04/2006 - 07/04/2006 = 8 days and this value I need to show in a Calculated member

Is it possible?

My time dimension has three levels

Year, Month and day

Can I do this operation, with the month level?

Regards

Thanks