blog.

Wow! It's now a long time since I wrote the last blog post. I think many of my posts starts with this now. But anyway, let's get back to the subject. I have the opportunity to have a look at the new Data Warehouse Cloud (DWC) Analytic Model. It was presented in the keynote of Hagen Jander and Eric Schemer on the DSAG Jahreskongress.

It has been a while since I wrote the last blog post. But there happened a lot in the last two months. We had our Deep Dives about Self Service with SAP Data Analytics Cloud Architecture and I had also some weeks of vacation. Now I am back from my vacation, and now I want to share some ideas I had in the last months.

This post is about Microsoft PowerPoint and how I use it to create a master PowerPoint file for different purposes. The idea was to have one place for all my SAP Data Warehouse Cloud slides and use them in different customer scenarios.

Therefore, I search a little what I can do. If you have Microsoft Office 365, PowerPoint has the option of Custom Slide Shows under the tab Slide Show.

Unfortunately, I didn't make it to publish the post in the last month. There were several reasons that I didn't make it, like the internal BI days or to prepare the next deep dive for Data Warehouse Cloud. But back to topic. SAP published Analysis for Office 2.8 SP14. Maybe there is something with the SP14 because Analysis Office 1.4 had also a SP14 before Analysis office 2.0 was released. So perhaps we see some new feature in the future?

But back to Analysis for Office 2.8 SP14. Now you are able to connect to Data warehouse Cloud and consume the analytical data sets directly in Excel. This is the biggest update with for a long time with features. The last updates were mostly bug fixing and some technical setting parameter, but nothing what is fascinating.

Besides the function in Analysis Office 2.8 SP12 repeat titles of a crosstab. The latest version also offers a new API method called SaveBwComments and some new technical settings like

• AllowFlatPresentationForHierarchyNodeVariables
• SapGetDataClientSideValidationOnly
• UseServerTypeParamForOlapConnections

But for me the best part is now the Data Warehouse Cloud connection. For this, you have to create a connection in the Insert Data Source dialog.

In this blog post, I want to share my experience with the Data Warehouse Cloud Bridge. There are several blogs out there that describe how to set up eclipse (https://www.seaparkconsultancy.com/single-post/create-a-dwc-sap-bw-bridge-project and https://blogs.sap.com/2022/01/24/using-sap-bw-bridge-for-data-warehouse-cloud-part-1-creating-simple-objects-demo/). So I won't consider this, because there are other sources for this. I will focus on problems I had during the conversion from an SAP Business Warehouse 7.4 on any DB into Data Warehouse Cloud.

So here is the configuration of my systems

• SAP NetWeaver BW 7.4 on any DB SP19
• Data Warehouse Cloud 2022.8.60

First, I had a look into this blog post and look into the sap note 3141688. After I checked with the note analyzer, the notes that are missing in my system. I had a list of around 1,000 notes I had to implement.

Okay, to be fair enough, the system release with BW 7.4 SP19 is from 2018 or so. So that normally should not happen in the real world. Now, after I implemented the notes over a long time and created several SAP tickets because of some wrong notes and not documented issues. I now can start with the steps of the blog post.

Let's get started and look into the system. Because our system is a demo system, I have no real data and also no InfoCubes and DSO I can convert. Let's be honest, who created an InfoCube and a DSO in the last years? First, I had to think about how to create the objects in the SAP GUI ;)

But after I had created my first InfoCube for years, I can finally start. So let's open the transaction code stc01 and select the task list SAP_BW4_TRANSFER_CLOUD_SHELL as described in the blog post above.

There are different ways to create hierarchies in Data Warehouse Cloud. One way is to use a CSV file and upload it into DWC. Another way is to use the existing hierarchies of your SAP BW system. In this post, I want to show how to use a hierarchy from BW and transform it into a parent-child hierarchy in Data Warehouse Cloud. After the transformation, we use the hierarchy in a view. 

First, we need a hierarchy on one InfoObject in SAP BW. 

There are different ideas and logics to determine year-to-date. Besides my post, which is also available on blogs.sap.com there is another post to determine a week-to-date (WTD) and year-to-date (YTD). I think that idea is also a good starting point, and I looked into it.

Instead of a control table which I have to fill manually, I created a new SQL view based on the standard SAP timetables in Data Warehouse Cloud. First create a new SQL view in the Data Builder of Data Warehouse Cloud (DWC).

In this blog post, I want to share an idea of how you can generate month-to-date (MTD), quarter-to-date (QTD), and year-to-date (YTD) values in SAP Data Warehouse Cloud (DWC). This is only one way, I think there are several other ways how you can solve this issue. I am happy to discuss your ideas in the comment section. In my old post, I describe the same logic for SAP HANA Calculation Views.

It is a while since I published my last post here. There are several reasons that I don't write anything, for example, SAP does not publish new features for Analysis for Office or my current project has no special cool new things I can talk about because it is mostly just maintenance and nothing hip. So it was very quiet here, and this is what I want to change. If you follow me on Twitter, you could have seen this post.

The year is almost over, and I haven't written a lot of post on my blog this year. I have to do it a lot more next year. Some #DataWarehouseCloud, #Python and also #ABAP topics are on my list.

— Tobias Meyer (@reyemsaibot) December 17, 2021

So I will write some posts about Python, Data Warehouse Cloud, and some ABAP topics in the near future. This post starts with Python and how to analyze the Apple Health data.

You can export the health data from your iPhone and receive a ZIP file that contains an XML file. How you can do this can be found via Google. It is uncomplicated. In my case, my XML file was around 1 GB big, and it contains about 3 million entries until May 2021. So I could not analyze it with Microsoft Excel or Notepad++, and I need only some information out of it, so I tried Python. I work with Python just more than one year, so please be kind if it is not perfectly written code.

It is quite a while since I published my last post. A lot happened since then. Analysis for Office 2.8 SP10 is now available, the summer and the vacation  are over. But in the meantime I developed some new ABAP tools, had quite some interesting exchange and took the SQL Script course by Jörg Brandeis. But in this blog post I want to share with you the latest tool I developed which compare transformations in SAP Business Warehouse systems through the landscape.

Here is a short overview:

Since last week Analysis for Office 2.8 SP8 is available, and you can download it with your S-User. I just got a question if I could write about it, so here are the notes which fixes some bugs:

• AO 2.x: Template for patch for AO and AO_SAC (s-note 3035301)
• AO: SAC Live Data Connections - Input Readiness of BW planning queries can't get restored when opening a saved workbook (s-note 3038493)
• Array formula cannot be updated via VBA (s-note 3017643)
• BW OLAP Connection in Analysis Office Workbook Targets a Wrong System after Promotion (s-note 2972912)
• Date format in AfO Schedule different than normal run (s-note 3001137)
• New Lines - Value Help exception while opening if search is disabled [f197-731f-8e] (s-note 3019161)
• SAPSetData: Introduced Option \"InitValue\" (s-note 3028120)

In the last post I wrote about authorizations in SAP Data Warehouse Cloud and I had an open topic about authorization on hierarchy nodes in SAP DWC. So I looked into and here is one example of how it could work at the moment. I don't know if SAP changes something in future releases.

So let us started with a CSV file to create our authorization we can use in the SAP Data Warehouse Cloud. I would now authorize my user to a Product Category because my hierarchy looks like this:

• Product Category 01
• • Product 1
  • Product 2
• Product Category 02
• • Product 3
  • Product 4

So we have the same structure as in the last post:

In this blog post I want to show you how you can use data authorizations in SAP Data Warehouse Cloud. First, we have to log on to our SAP DWC and select the space we want to use for this.

After we have selected our space, we open the Data Builder of SAP Data Warehouse Cloud. Here we have to import a new table with our authorizations. In my case we want to filter the Product ID, so the table looks like this:

Someone ask me how you could create a time hierarchy in SAP Data Warehouse Cloud (DWC) to use it in SAP Analytics Cloud (SAC). Because out of the box by just create the time dimension it isn't working right now. So here are the steps you have to make.

In BW/4HANA SAP offers you the possibility to link restricted or calculated key figures across different Composite Providers. The advantage of the link components concept is that they can automatically synchronize whenever you make changes in the source or master component. If you are not familiar with this concept.

SAP Help:

"A linked component can be automatically synchronized whenever changes are made to the corresponding source component.

Example scenario: You have two highly similar InfoProviders, IP_A and IP_B. You have created the query Q_A for IP_A. You now want to create the query Q_B for InfoProvider IP_B. You want this query to be very similar to query Q_A and to be automatically adjusted whenever changes are made to query Q_A.

To do this, you use the link component concept: You create the linked target query Q_B for source query Q_A. This is more than just a copy, as the system also retains the mapping information. This mapping information makes it possible to synchronize the queries."

So the concept is really nice because you can ensure that your key figures with the same name has also the same configuration, e.g. your turnover is configured correctly on any Composite Provider in your system. Now let's create a new link component. First we select a restricted key figure on our Composite Provider and goto "Linked Component" view in Eclipse.

Last week a colleague and I look into the XXL InfoObjects in SAP BW/4HANA. We searched the online help and there is a short video about the topic. But after that we had no clue how we can use it. Can we use it in Analysis for Office? Or just in the query or Composite Provider? What is the purpose of these InfoObjects? Can we store a documentation and open it directly from the query?

So I build an example XXL InfoObject and added it as a XXL Attribute to an InfoObject. This InfoObject was in my Composite Provider, so I looked into the Composite Provider with Analysis for Office and don't see the XXL InfoObject. So we are back to start. What can I do with it? Now I asked around on twitter. And I was surprised about the answers.

Since last Friday the latest version of SAP Analysis for Office is now available. You can download it in the SAP Launchpad. As the last version we have some bugs which are now hopefully fixed:

• All crosstabs are removed from workbook (s-note 2990456)
• Date picker control using wrong calendar or chosen date is incorrect (s-note 2981896)
• Exception when saving a workbook with a chart sheet (s-note 2981234)
• Search result does not show translated texts (s-note 2977530)
• Planning sequence execution uses wrong variable values (s-note 2975399)
• Excel hangs after copy and paste as Worksheet object (s-note 2974961)
• Non-SAP worksheet cell referencing SAP Crosstab cell changes its number format (s-note 2974435)
• AO: SAC - Unsupported S/4HANA Cloud connections are listed in Live Data Connections overview (s-note 2973887)
• [a42d-4b73-7e] ServerGroup and SNC Information are Missing When Used Analysis Office Recent List (s-note 2972745)
• Input has invalid format error when using exponential / scientific notation in new lines (s-note 2971200)
• The Chart has empty data series or categories after swap crosstab axes or data refresh (s-note 2970169)
• Suppressing all messages (s-note 2970000)
• Refresh All button is disabled for AO workbook (s-note 2969805)
• Incorrect sort order of BW systems in Replace System dialog (s-note 2968291)
• Error [3291-74de-1b] when using Excel with Traditional Chinese or ClientLanguage is set to "ZF" (s-note 2967800)
• Opening a workbook from BIP results in a nested exception [1b72-be52-de] (s-note 2967546)
• Exception when using an embedded sheet in Word (s-note 2966861)
• Saving a workbook results in a nested exception [e525-fb53-d4] (s-note 2966368)
• Symbols of the Conditional Formatting are not removed (s-note 2966312)
• SAC Logon Error in Analysis Office - No connection to system INA_SAC (s-note 2964461)

The online help of SAP Analysis for Office is not updated yet. A lot of s-notes for a Service Pack 6. I had some issues when I refresh my data source with the last version, I hope this will be fixed now. Do you have any problems with Analysis Office?

At the end of last month I ask on Twitter why I should create a join between an Advanced DataStoreObject and an InfoObject to read the attributes from this InfoObject when I even can activate the attributes on the output tab of the Composite Provider in SAP BW/4HANA. The answers on Twitter was not satisfactory, and I ask a colleague if he knew a reason. So I build a Composite Provider with a join between an InfoObject and an ADSO in our BW/4HANA system.

Since a couple of days the latest version of Analysis for Office 2.8 is available. Michael was a little faster and notice it on Twitter before me. So let's look into it. SAP fixes a bunch of bugs with this service pack. Here is an overview:

• AO 4 SAC: \"Group Members\" functionality not available when using BW Live Data Connections (s-note 2962476)
• AO: BEx Workbook Conversion - An exception occurs when trying to save a converted BEx workbook which contains formulas (s-note 2965890)
• Action has been cancelled due to invalid input values (s-note 2958731)
• Analysis for office responds slow in AO 2.8 (s-note 2966651)
• BIP Logon Issues (s-note 2976169)
• BIP OLAP Connection targeting another system (s-note 2972912)
• BIP Workbook fails to save back to the platform (s-note 2963966)
• Error [3291-74de-1b] when using Excel with Traditional Chinese or ClientLanguage is set to \"ZF\" (s-note 2967800)
• Exception when the Number of NewLines is set to 0 (s-note 2961082)
• Exception when using an embedded sheet in Word (s-note 2966861)
• Filter Dialog - Change state of Tree Node if its text was clicked (s-note 2938903)
• Input has invalid format error when using exponential / scientific notation in new lines (s-note 2971200)
• Insecure Connection Message with BIP OLAP (s-note 2967910)
• New Lines - F4 help does not display member of respective cell (s-note 2963872)
• Refresh All button is disabled for AO workbook (s-note 2969805)
• Refresh Workbook with BIP OLAP SSO Connection Fails in Analysis Office (s-note 2957833)
• SAC Logon Error in Analysis Office - No connection to system INA_SAC (s-note 2964461)
• SAP Formulas in Scheduled Workbooks are Displayed as #NAME? in Excel (s-note 2765716)
• Saving a workbook results in a nested exception [e525-fb53-d4] (s-note 2966368)

A lot of s-notes for a Service Pack 5. I hope Analysis for Office will get a stable version again. Do you have any problems with Analysis Office?

Open ODS are a long time available in SAP NetWeaver BW. You can access directly database tables, database views or BW/4HANA DataSources (for direct access). In this blog post I want to look into the fast and agile modeling with Open ODS Views. So we start with a flat file we for example received from our customer to build a new data flow for reporting. The file has the following structure:

• Country
• Product
• Business Category
• Controlling Area
• Profit Center
• Month
• Year
• Amount

I created a test file with 10.000.000 entries to get some data we can play with. Now we create based on this file a data source to import the data.

I lately looked deeper into the modeling of Composite Provider in BW/4HANA 2.0 and found some difference between a BW/4HANA 1.0, BWonHANA 7.50 and the latest version BW/4HANA 2.0. So let's compare them and see what's new, and we can go deeper in further posts. We start with the context menu of the scenario for the provider.