Data factory SAP BW

Question

I'm trying to use the Data Factory in Azure to export data from a SAP BW. The connection is working, and I'm able to get data. The problem is how I'm getting the data. The picture describes the issue pretty well.

Has anyone encountered something similar? Any tips on how to approach this issue? Any help is greatly appreciated!

Query like:

SELECT
    [Measures].<<Measure>> ON COLUMNS, 
    NON EMPTY 
    {<<Dimension>>.MEMBERS,
    <<Dimension>>.MEMBERS} ON ROWS
FROM <<Cube>>

Picture: https://i.stack.imgur.com/9Gxfh.png

Best regards,

MoazRub · Answer 1 · 2018-12-11T14:16:44.250

2

This is how your query should look like.

select Measures.Value on columns,

nonempty
(
DimPlan.Plan.Plan,
DimCategory.Category.Category,
DimProduct.Product.Product
)
on rows 
from YourCube

edited Dec 11 '18 at 14:16

answered Dec 11 '18 at 14:06

MoazRub

2,881
2
10
20

score 0 · Answer 2 · answered Dec 16 '18 at 18:02

Looks like you are getting the ALL members of each hierarchy coming through into the results.

Very similar to MoazRubs answer but avoiding needing to use the NonEmpty function - you can simply cross-join the hierarchies via the * operator:

SELECT
      Measures.Value ON 0,
      DimPlan.Plan.Plan.MEMBERS *
      DimCategory.Category.Category.MEMBERS *
      DimProduct.Product.Product.MEMBERS
         ON 1
FROM YourCube;

Data factory SAP BW

2 Answers2