Unassigned nodes in BW query are a common case. This wasn’t available in SAP Datasphere for a long time and leads to some incorrect data while displaying the aggregated data in hierarchy form.

There is also an influence request in the influence portal. SAP still hasn’t implemented yet a good soulation and the request is still open but there is a hint for a SAP online help entry how to
solve the issue. The request is 304831.

It is only available through the validation rules. The validation rules allow you to identify missing or enhanced hierarchy notes in your hierarchy. 

The help entry is found under the name „Collect Unassigned Fact Records for Inclusion in Aggregations and Visualizations“. It has no link to the Hierarchy with Directoy entry nor is it
linked with the Creating Analytic Model page. 

With click on the validation of the fact view you get the information about missing hierarchy nodes in my GL Account hierarchy.

Under „Details,“ you can find information on how to disable this behavior. 

You can download the automatically created CSN files.  After importing them to your Datasphere tenant, you will see these three objects.

Now we have to change the hierarchy association in the dimension to the enhanced object.

Now you deploy the dimension and make the validation again. As you can see now there are no missing hierarchy nodes.

The analytic model now correctly displays the unassigned nodes when I display the hierarchy.

I don’t understand why SAP doesn’t do this automatically or why it’s hidden under the validation rules of a fact model. After completing the validation and changing the dimension association, the
dimension can be used anywhere, and unassigned nodes will appear under „Unassigned Node.“

I hope this information helps you correctly display unassigned nodes in the hierarchy.

Last weekend, I attended the SAP Inside Track Netherlands (sitNL) event at the Herzog Bosch Edison Community. Ronald and Tim were
some of the organizers of this event, and I presented our customer project, „Road from BW 7.5 on AnyDB to Datasphere and Beyond.“ The 15th annual SAP Inside Track Netherlands has taken
place. It was also a celebration featuring highlights from previous years.

The agenda was quite interesting. There was a lot of AI stuff, as well as other presentations related to Datasphere.  The first one was about migrating high-volume BW to Datasphere, as well
as the challenges they had to overcome. It was interesting to learn how many data records they have in this project and how they solved the issue of handling millions of records, tracking
duplicates, and applying performance optimization. 

The second presentation was all about learning about replication flows in Datasphere. The presentation covered best practices for using replication flows in a greenfield data project and what you
can do with replication flows.

In addition to the interesting presentations, there was a lot of good exchange of ideas.

If you’re interested in my presentation, you can download it here. I hope to see many of you again next time. On the Inside Track Netherlands

I really like community events because you can learn a lot from them. There is also no competition between the different consulting firms. People like to share their knowledge, and you can have
good conversations with them. 

Sometimes, someone else writes a good article, and I’m happy when it solves my problem. If you have the time, attend these events and benefit from the knowledge. If you are ready, present your
story. Hopefully, I will see you next time at sitNL!

This blog post is all about customer exit variables. In SAP Business Warehouse, including BW/4HANA and BW on HANA, every customer has several. Now, it is time to bring this concept into SAP
Datasphere.

Familiarity with BW logic is assumed; it will not be explained in detail here.

Some customers have implemented customer exit variables with different classes and tables to map something. For more information, see my old post about the concepts of customer exit variables in
BW/4HANA.

• BW/4HANA 2.0 Customer Exit Variables with own enhancement spot
• BW/4HANA: Customer Exit Variable with dynamic assignment

Now, back to SAP Datasphere. For a long time, Analytic Models has offered the possibility to consume variables as either Derived Variables or Dynamic Default.

When compared to BW, the derived variables are similar to I_STEP 2 when the variable is
not input-ready. Dynamic default variables are like I_STEP 1, which occurs before the variable prompt pops up.

Sandy showed us in the Datasphere Topic Group how to achieve this. This is
also documented in the SAP Community.

https://community.sap.com/t5/technology-blog-posts-by-sap/sap-datasphere-bw-series-getting-started-part-2/ba-p/13953994

I used this approach and enriched it with more time-based logic.

• today
• yesterday
• last working day
• same month last year
• last year

and many more. I think this is a good starting point for the time filtering you normally need. The code is always pretty much the same and looks like this:

I think everyone can adopt this. I won’t go into detail for each time option.

In addition to time logic, you can also create customer exit variables for other dimensions.

Let me explain this approach in more detail. For example, I created a table for Company Code that has three columns: ID, User, and Value.

I also created a control table for all customer exit variables, including an ID and table name.

Then, I created a master view, or a class, depending on what you would call it. This object determines the corresponding table for the customer exit depending on the ID

The lookup entity for the company code is a new view that calls the central view/class and returns the company codes that I want to use to the variable.

This approach allows me to be flexible by determining the corresponding table for the exit centrally and using separate logic for the lookup. I could also build another lookup with a join or
other filtering options. The preview looks like this:

In an SAP Analytics Cloud story, the prompt looks like this:

This is an example of how to use derived and dynamic default variables in SAP Datasphere.

Let me know in the comments if this approach is something you’re interested in or if you have a completely different approach.

Yesterday I was at the DSAG TG Datasphere. DSAG is the German SAP user group. The TG Datasphere is a topic group about SAP Datasphere.

Thanks Hakan for the invitation to the Porsche Campus in Zuffenhausen. It is always great to see the campus and all the nice sports cars.

But back to the topic. The agenda was packed with lots of slots, including 3 speed dating project experiences. This approach was cool (for the audience, the speaker has to do the presentation 3
times 😉 ) 

But the first presentation was by Fabian about the
SAP Data & Analytics Strategy. Here we heard all about SAP Business Data Cloud (BDC) and the strategy for enterprise data. There was also a detailed answer on how to deal with the old BW in
the Private Cloud Edition (PCE) and what SAP’s approach is here.

Then we had the 3 speed dating sessions. One from Sebastian about a Datasphere reference architecture and some project acceleration tools. The next one was a demonstration of
data products @ Porsche by Hakan. This is also nice,
but I don’t think many customers are there at the moment. 

The last one was a presentation about the future architecture @ BAUER. They showed how to implement a business accounting form within SAP Datasphere. Thanks to Andreas and the team for the insight.

After lunch, Sandy gave us an overview of the best of SAP BW features in SAP
Datasphere. And also how the SAP Business Warehouse is located in the SAP Business Data Cloud. We saw some features like the devolution of variables with a great example and had the opportunity
to discuss missing features and ideas. 

Next was a project experience report from Alena and Anas. They showed how they implemented HR reporting in SAP Datasphere and the challenges they faced

The last slot was covered by Florian with
Data Products and what SAP will do in the future. This sounds interesting, but is still far away from the customers.

All in all, it was an interesting event. Thanks for the organization and also for the opportunity to see familiar faces and have some good conversations.

See you next time 

Now that we know how to get data from the SAP Datasphere API using Postman, I want to automate the steps using Python. In this post, I implement authentication via OAuth. I use the two
existing libraries OAuth2Session and urllib.parse. 

To create the OAuth request we need the secret, the client ID, the authorization URL and the token URL. I stored all this information in a JSON file so I can read and process it. Where to get
this information can be found in the earlier post. So we need to read the JSON file first to
get all the parameters.

secrets_file = path_to_the_secret.json

f = open(secrets_file)
secrets = json.load(f)
print(secrets)

Next, we need to encode the client ID, as described by Jascha in this post. I use the
urllib.parse.quote functionality for this..

With the print command, you should see a result like this: sb-e4c75210-40c4-4c89-a960-381b156c4e93%21b106441%7Cclient%21b3650

Once we have the encoded client ID, we can create the URL for authentication. For this, we need the authorization URL and combine it with the client ID. 

code_url = secrets['authorization_url'] + '?response_type=code&client_id=' + client_id_encode 

When you print the URL, it should look like this:

https://xyz.authentication.eu10.hana.ondemand.com/oauth/authorize?response_type=code&client_id=sb-e4c75210-40c4-4c89-a960-381b156c4e93%21b106441%7Cclient%21b3650

Open the printed URL in a browser and copy the code you get at the end. 

Now we can create the session object for the request.

session = requests.session() 

And use an input function to store the code we get from the above url, see screenshot

This is all we need for the OAuth process with Python.

As you can see, we use the token URL, the client ID, the client secret, and the code we got from the URL earlier. The redirect is localhost, where we added the OAuth client. With the print
statement, we can print the JSON response from the request.

print(OAuth_AccessRequest.json())

That is all the magic. We are now using Python to authenticate to the SAP Datasphere API. The next steps are to store the information for future use and to renew the token when it expires.
In my example, I store the information in a file and use it to renew the token. 

token = OAuth_AccessRequest.json()
with open(path, 'w') as f:
   json.dump(token, f)

Updating the token is simple. First, we need to read the file where we previously stored the information. Open the file with the secret information, create an OAuth session object, and refresh
the token.

f = open(token_file)
token = json.load(f)

f = open(path_of_secret_file)
secrets = json.load(f)

extra = { 'client_id': secrets['client_id'],
          'client_secret': secrets['client_secret']
        }

datasphere = OAuth2Session(secrets['client_id'], token=token)
token = datasphere.refresh_token(token_url=secrets['token_url'], **extra)

That’s it. You can now authenticate to the Datasphere API using OAuth from Python. I know there’s a manual step in the code, but that’s not a showstopper for me right now. I only need to
authenticate with the code after 30 days, and that is fine for some admin tools. If you want to automate some data extractions, then you can build something for that. Jascha described a way in
this post. I look forward to hearing from you
and your ideas about what we can do with Python and the API in the future. The next blog will be about processing information with Python, so stay tuned.

Now that we know how to use Postman to send an API request to SAP Datasphere and receive data. We
can now take a closer look at the API and what we can do with it. At api.sap.com we get different API
endpoints, I want to focus on the consumption part. The API reference for the consumption part allows us to consume relational and analytical models. 

Let’s start with the relational models and retrieve the data with Postman to see some results. The relational API endpoint can be accessed at this URL

https://xyz.eu10.hcs.cloud.sap/api/v1/dwc/consumption/relational/{space}/{asset}/{technical_object}

You can find the exact description in the API catalog. So I won’t copy and paste the content here.  Now let’s get the data with the above URL. My view is called DBV_BIKE_DELIVERY.
If I now use my OAuth from the last post, I get the data directly in Postman.

As you can see, it is very easy to retrieve the data from the API. To make it more flexible, the URL can have different parameters like

• select
• filter
• order by
• top
• skip

This can be really interesting when we use this URL later with Python to get more flexible data. For example, if we use the $top parameter, we can display only the top 3 records.

Now you can use Postman to access relational views and retrieve the data. The next step is to retrieve data from analytical objects such as an analytical model. The URL is almost the same:

https://xyz.eu10.hcs.cloud.sap/api/v1/dwc/consumption/analytical/{space}/{asset}/{technical_object}

As you can see, the only difference is the word relational to analytical. 

With the analytic URL, we get data from an analytic model. The advantage of the analytic model is that we also get data from associated dimensions in the result. So the result can be very large
because all active dimension attributes are now displayed. So be careful what you select.

To reduce the dimensions you want to consume, we can add the parameter $select and get only the columns we need.

So now we only get the 4 columns I added to the URL. The columns are 

• CALWEEK
• PARTNERID
• PRODUCTID__PRODCATEGORYID
• QUANTITY

As you can see, I get different columns. The CALWEEK column from the associated time dimension, the PARTNERID and Quantity from the fact view, and the PRODUCTCATEGORYID from the product
dimension. 

I think this offers a lot of possibilities for getting data out of Datasphere.

In this post, I looked deeper into the SAP Datasphere API to get data directly from the system. You can access relational views or analytical models and consume them in other tools. Prerequisite
is the OAuth we created in the previous post. What do you think about accessing the data via API? 

In the next post we will switch to Python and try to get the data we have consumed with post in Python. 

This is the first in a series of articles about the SAP Datasphere API and what you can do with it. I will start by explaining how to configure the OAuth and start with the first request
using Postman. Postman is a tool for creating requests to web services. 

So let’s dive into it. If you want to consume the SAP Datasphere API, you need to find the right endpoints. A good place to start is api.sap.com. But how can we test and see if the request is correct? If we get the right result?

In this post, I will go through the configuration of Postman that allows you to send requests and also receive data from those requests. To do this, we use OAuth technology. I won’t explain what
OAuth is. If you are interested, take a look at Wikipedia. 

First, we need an OAuth connection. To create an OAuth connection, go to your SAP Datasphere tenant and select System >> Administration >> APP Integration. There you will
find the authorization and token URL that you will need to create a token later.

Now we need to create a client so we have a client id and a secret id. Click Create New OAuth Client.

Define a name, the purpose, a redirect URL (in this case http://localhost:8080), and as of release 2024.8 you can also define some details about the token. After clicking add, the new OAuth
client is created. Now we need the secret and the client id for authorization. Copy the OAuth client ID and secret for later use. 

Now we have everything we need to access the API. Let’s switch to Postman, you can use it either on the web or via the downloaded program. Visit https://www.postman.com for more information.

Go to the Authorization tab, select the OAuth 2.0 type, and select Request Headers for the „Add the authorization information to“ section. Now we need to add the token
information.  

• Use token type: Access Token
• Header Prefix: Bearer
• Auto-refresh token: True
• Grant type: Authorization Code
• Callback URL: http://localhost:8080
• Auth URL: Auth URL from the Datasphere tenant (e.g. https://xyz.authentication.eu10.hana.ondemand.com/oauth/authorize)
• Access Token URL: Token URL from the tenant (e.g. https://xyz.authentication.eu10.hana.ondemand.com/oauth/token)
• Client ID: Client ID from the OAuth we created earlier
• Client secret: Client Secret we get earlier from the OAuth.
• Client authentication: Send as Basic Auth header

Click „Get New Access Token“ to create a new token. This will take you to your SAP Datasphere login page, where you will need to log in for the first time. You will receive the token
and can use it. 

Now we are ready to kick against the Datasphere API and get some results. To give you a first look at the API, we use the following URL
https://xyz.eu10.hcs.cloud.sap/api/v1/dwc/catalog/spaces to get a result of all the spaces. As you can see in the screenshot below, the http status is 200. This means that
the request was successful.

The next post will go deeper into the API endpoints and some use cases.

This is the first of a series of posts about the SAP Datasphere API. I hope you now understand the basics and how to check if your requests return data or if something went wrong. There is also
an old post in the SAP community about Postman configuration. But as
far as I know, sometimes SAP deletes all the old stuff, and you cannot find it anymore. So feel free to use the comments and give me some ideas what I should show with the API in the next posts.

Dynamic source filtering is a big issue in SAP Datasphere. There are several approaches that may work for one, but not for another. 

The discussion started again on LinkedIn after I read a post about dynamic filtering that was set with a fixed filter. Wanda then showed us a solution he uses to filter
data. But
Christopher pointed out that the filter is not pushed down to the source when you use a DP agent, e.g., the ABAP connection.

I had in mind that there was a blog post about how to push a filter down, even on an ABAP connection. So I tried Wanda’s idea and combined it with the other knowledge. This blog post will show
you how it works.

If you already know s-note 2567999, you know that you can filter data with a stored
procedure. And as of June, you can run stored procedures directly in task chains. This is a really nice feature. But back to the other solution.

First, we need a local table to store the load parameter for this approach. The loading table is just to set the parameter easily and not always in the coding.

It is quite simple. 3 columns, one for the application, one for the parameter and one for the value. It may be different on your approach, but keep in mind to adjust the coding as well.

On the local table, we now need a SQL script view with the logic. The coding is simple, as Wanda described in his LinkedIn post.

return
select 
    case 
        when APP_NAME = 'APP_1' AND PARAM_NAME = 'TEST' then
            case
                when PARAM_VALUE is null then
                    add_days(to_date(now()), -1)
                else
                    to_date(PARAM_VALUE)
            end
        else
            to_date(now())
    END as PARAM_VALUE
    
from "0LT_LOADING_PARAMETER"
where
APP_NAME = :I_APP_NAME
and PARAM_NAME = :I_PARAM_NAME;

We have one return parameter (PARAM_VALUE) and two input parameters (I_APP_NAME and I_PARAM_NAME). That was it. Now the magic needs to be done in a new SQL script view. Based on this
post. I tried to combine the logic from Wanda with this approach to
push the filter down on a SAP BW Connection. The logic in this example is quite simple.

DECLARE myDate DATE;
DECLARE myDateString NVARCHAR(8);

select PARAM_VALUE into myDate from "1SV_FUNCTION_PARAMETER"(I_APP_NAME=>'APP_1',I_PARAM_NAME=>'TEST');

myDateString = TO_NVARCHAR(myDate, 'YYYYMMDD');

return select "RECORDMODE","ORT","ARTIKEL","DATE1","QUANTITY" from "ZDWC003" where "DATE1" = myDateString;

What we do is get the date from the function parameter (see above) and convert it to the correct format for the connection date (in this case, YYYYMMDD). Then we display the result.

As you can see, the filter is pushed down when the condition is equal. If I use >= then the filter is not pushed down.

You can see it in the WHERE clause. I hope this helps you a bit and gives you an option how to solve your problem with dynamic filtering. I hope we get a good solution directly from SAP because
this is not a small problem. The same should work for replication flows. 

The filter push down is necessary for old SAP systems and also for non-SAP systems. We will see what comes in 2025. 

You may also be interested in these posts:

• SAP Datasphere: Dynamic previous year in an Analytic Model
• Consume hierarchies with nodes in SAP Datasphere
• MTD/QTD/YTD Calculation in SAP Datasphere
• Create a time hierarchy in SAP Datasphere