This is the first article in a series, exploring the various methodologies for "application building" in Analysis Office.  In the initial installments, we will be working solely with worksheet formulas and Visual Basic for Applications (VBA) macros.  Later, we'll explore building our own add-ins to further enhance Analysis, using Microsoft's Visual Studio Tools for Office (VSTO).

 

Customers often come to us with asking for features or changes in the way Analysis Office works.  With ideas that benefit many of our customers, we add them to the product development backlog. With ideas that apply to a specific customer or ideas that if in the standard product would violate standard "best practices" for security, usability, etc they cannot be added to the backlog.

 

Some of these ideas that cannot be added to the backlog are still excellent ideas and the customer who proposed them would benefit if they saw the light of day.  Fortunately, Analysis Office exists within the Microsoft Office environment; which means that all of the various methods of extending and automating office are also valid for extending and automating office Analysis.  Analysis also brings its own palette of formula and macro commands to the table, making it even more powerful.

 

The feature that we'll look at in this installment was inspired by a recent customer visit.  Make the data source metadata available when offline.  This means that the Information tab of the Design Panel empty of anything interesting until a data source has been refreshed.  If a user has a large workbook, with many data sources, she may want to have a peek at this information without waiting for the whole workbook to refresh.  So let's give her this ability!

 

What we need (AKA Requirements and Specifications)

  • We need some sort of nonvolatile way of storing the contents of the Information Tab for offline use.  For the sake of simplicity here, we'll use hidden worksheets to hold this information.  Whenever the user wants to peek at it, she can simply unhide the relevant sheet.
  • This hidden sheet needs a naming convention so that the user can quickly find the sheet that she wants to look at.  We'll use <DataSourceAlias>_<QueryName>.  The data source alias is the "script and formula name", usually something along the lines of DS_1, DS_2, etc.  The query name here is the text name, not the technical name.  With these two bits of information, the user should easily be able to find her hidden sheet.
  • We'll fill this sheet with the information from the Information tab of the Design Panel.
  • As an added bonus, we'll show the variables and filters that are applied to the data source.

 

 

DocumantDataSource()

We are going to call our macro DocumantDataSource.  We'll make it a standard subroutine with no parameters.  This will allow use us to use it later as we see fit.  For example, the user could assign a button or hotkey combination to Excel to trigger this macro manually.  It could also be called from the CallbackAfterRedisplay macro (if there is one present) to automatically generate the data whenever the data source is refreshed.  We'll start with a blank subroutine and declare all of the variables that we'll need.

Sub DocumentDataSource()

'

' DocumentDataSource Macro

'

    Dim wrkSheet As Worksheet

    Dim activeRange As Range

 

    Dim sheetName As String

    Dim datasourceName As String

 

    Dim dataSourceVariables As Variant

    Dim dataSourceFilters As Variant

    Dim dslabel(1 To 11) As String

    Dim dsIinfo(1 To 11) As String

 

    Dim nNth As Integer

    Dim currRow As Integer

 

End Sub

Now let's fill it in!

 

 

If we have selected a crosstab or chart, then...

With the following lines of code, we'll ensure that the current focus cell is within a crosstab.  We do this by making a call to the Analysis VBA API command, SAPGetCellInfo to check whether we have a selected data source by asking for the formula alias of the current focus cell.  If the selection is not a crosstab or chart, then asking for it's formula alias will return an error.  The rest of the macro will live inside the if statement, ensuring that it only executes if we have indeed selected a data source.

    dataSourceAlias = Application.Run("SAPGetCellInfo", Selection, "DATASOURCE")

    If IsError(dataSourceAlias) = False Then

 

    End If

 

Create the New Hidden Sheets

In the next 9 lines of code (not counting comments), we'll do a couple of chores. 

  1. We'll ask the for the name of the data source and concatenate that with the formula alias, to determine the sheet name.
  2. We delete any sheet by that name, if one exists.  We turn off the display of alerts and turn it back on afterwards.  If we don't do this, Excel will show the user a popup, asking whether she really wants to delete the sheet or not. 
  3. We create a new sheet by that name. 

Astute readers may notice that there is no VBA command in the Analysis API called SAPGetSourceInfo, but that there is a worksheet formula by that name.  This is correct!  AO worksheet formula commands may also be called with the from Application.Run, just like macro commands.

        'Determine the data source alias, text name and the name of the properties sheet

        datasourceName = Application.Run("SAPGetSourceInfo", dataSourceAlias, "DataSourceName")

        sheetName = dataSourceAlias & "_" & datasourceName

 

        'Delete the existing sheet if there is one.

        '   We turn off the display of alerts when we do this, because we don't want the user bothered by a dialog

        '   We turn it back on after deleting the sheet

        On Error Resume Next

        Application.DisplayAlerts = False

        Application.ActiveWorkbook.Sheets(sheetName).Delete

        Application.DisplayAlerts = True

 

        'Create the new worksheet

        Set wrkSheet = Application.ActiveWorkbook.Sheets.Add

        wrkSheet.Name = sheetName

        wrkSheet.Visible = xlSheetHidden

 

 

 

Get the metadata from Analysis

Next up, we'll prepare the information fields from the Information tab.  We'll pack this information into lists, so that we can write them in a single loop.  Again, we are using Application.Run to call an AO worksheet formula; in this case, SAPGetSourceInfo.

        dslabel(1) = "Data Source Name"

        dslabel(2) = "Key Date"

        dslabel(3) = "Last Data Update"

        dslabel(4) = "Query Technical Name"

        dslabel(5) = "Info Provider Technical Name"

        dslabel(6) = "Info Provider Name"

        dslabel(7) = "Query Created By"

        dslabel(8) = "Query Last Changed By"

        dslabel(9) = "Query Last Changed At"

        dslabel(10) = "System"

        dslabel(11) = "Logon User"

 

        'Collect the data fields

        dsIinfo(1) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "DataSourceName")

        dsIinfo(2) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "KeyDate")

        dsIinfo(3) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "LastDataUpdate")

        dsIinfo(4) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryTechName")

        dsIinfo(5) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "InfoProviderTechName")

        dsIinfo(6) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "InfoProviderName")

        dsIinfo(7) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryCreatedBy")

        dsIinfo(8) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryLastChangedBy")

        dsIinfo(9) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "QueryLastChangedAt")

        dsIinfo(10) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "System")

        dsIinfo(11) = Application.Run("SAPGetSourceInfo", dataSourceAlias, "LogonUser")

 

 

Next up, we actually populate these values into the hidden worksheet

        'Write the non variable/filter bits.  The label in Col A and the Value in Col B

        'We are writing the values, not the formulas

        For nextRow = 1 To 11

            wrkSheet.Cells(nextRow, 1).Value2 = dslabel(nextRow)

            wrkSheet.Cells(nextRow, 2).Value2 = dsIinfo(nextRow)

        Next nextRow

 

Variables and Filters

The variables and effective filters are handled a bit differently than most of the other properties.  They are a bit trickier, because SAPListOfVariables and SAPListOfEffectiveFilters both deliver "lists of lists" as variants.  We handle them by giving them their own worksheet blocks, with one line per variable or filtered dimension.  The block has three columns: 

  1. The first tells us whether it is a variable or filter section, in cross-join style (no repeating of the same info on following lines). 
  2. The second column tells us the variable name or dimension name. 
  3. The third is the variable/filter value.

We use the currRow variable to track where we are in the worksheet, so that we don't overwrite anything.

        'Let's proceed with handling the variables and filters

        currRow = 11

 

        'Variables

        nNth = 0

        dataSourceVariables = Application.Run("SAPListOfVariables", dataSourceAlias)

        For Each dataSourceVariable In dataSourceVariables

            nNth = nNth + 1

            currRow = currRow + 1

            'In the first row of variables, we'll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = "Variables"

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceVariable(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceVariable(nNth, 2)

        Next

 

        'Filters

        nNth = 0

        dataSourceFilters = Application.Run("SAPListOfEffectiveFilters", dataSourceAlias)

        For Each dataSourceVariable In dataSourceFilters

            nNth = nNth + 1

            currRow = currRow + 1

            'In the first row of filters, we'll write the appropriate signifier in col 1

            If nNth < 2 Then

                wrkSheet.Cells(currRow, 1).Value2 = "Effective Filters"

            End If

            wrkSheet.Cells(currRow, 2).Value2 = dataSourceFilters(nNth, 1)

            wrkSheet.Cells(currRow, 3).Value2 = dataSourceFilters(nNth, 2)

        Next

 

 

Re-size the Columns for Readability

        'resize the columns

        wrkSheet.Columns("C:C").EntireColumn.AutoFit

        wrkSheet.Columns("B:B").EntireColumn.AutoFit

        wrkSheet.Columns("A:A").EntireColumn.AutoFit

 

 

And Voila!  We now have a macro for storing the data source metadata offline.

Blog1.png

Part 1 covered installation and some features.  Part 2 discussed more features including Waterfall Charts, Read Access Mode, and Reset Data Source.

 

Launch BEx Query Designer

First I want to cover some troubleshooting.  Some comments in the discussion forum indicate that individuals are unable to launch the BEx query designer - it is greyed out.  Note you must be on a later SP for the BEx Query Designer.  Check your version by going to Help > About in the BEx Query Designer:

querydesigner.jpg

 

 

If you are not on this version then go to Service Marketplace and download the BI Add on:

querdesignersmp.jpg

 

 

Then you should be able to click this option in the User Settings.

 

Adjusting the Member Selector

You can limit the number of members in the "member selector" by going to User > Settings:

memberselector.jpg

You can change the Maximum Number of Entries in Recent List - the default is 1000.  This may help improve performance when working with large amounts of data.

 

Filter Hierarchy in Display Panel

 

In the display panel, you have the option to filter based on hierarchy only - if you select this, your hierarchy turns into a flat presentation.

rightclickusehierarchyforfilteronly.jpg

 

When it is clicked on, the hierarchy in the cross tab is turned into a "flat presentation"

2rightclick.jpg

 

 

 

 

But then you can filter by the hierarchy or the leaves of the hierarchy:

3filter.jpg

 

 

Hands On Experience

Next week at BI 2013 in Amsterdam, SAP Mentor Ingo Hilgefort will be providing hands-on sessions for the Analysis Suite, including Analysis Office.  I attended this myself at BI2013 in Las Vegas in March and these sessions were quite popular with great feedback.  I recommend attending these sessions if you are attending.

In my blog last Friday, I discussed installation steps and briefly just some of the new features.  In this blog, I will review some of the other new features that came with this release, which was GA last Friday.

 

We have seen many questions regarding Analysis Office 1.4 - please review the Product Availability Matrix. Analysis Office 1.4 32 bit and 64 bit supports MS Office 2013.

 

Waterfall Charts

In the past, Analysis Office supported charts in Excel.  Now it is offering two types of Waterfall Charts:

1waterfall.jpg

 

The first type of Waterfall Chart shows up as follows:

2waterfall.jpg

 

Here is an example of a pivoted Waterfall Chart:

3waterfall.jpg

 

Reset Data Source

In the previous blog, you saw you can launch the BEx Query Designer.

 

If you make some changes to the crosstab using the Display Panel, you can use Reset Data Source to set the query back to the initial state of the BEx query.

 

This is a right-click from the context menu, and select Reset Data Source

4resetdatasource.jpg

 

Selecting Reset Data Source will reset it back to the initial navigation state of the BEx Query.

 

Note you can also make this setting to reset on opening from the Display panel:

5resetdatasource.jpg

 

Read Access Mode

 

Select a characteristic ("aka Member") and select Members > Access Mode

6readaccess.jpg

 

 

You can select only those values with posted data as shown above.

 

If you show values in Master Data you will see everything, even those without any transactions posted against them.

 

Note in the User Settings the "Allow Access Mode to be Changed" option must be turned on:

7readaccess.jpg

 

 

More to come.

Last month SAP covered what is new with Analysis Office here.  Today is it generally available, according to the PAM.   This morning I was able to install it on my machine.

 

The install file is only 79.2 MB and installation is fairly easy.

1install.jpg

 

When you get to this screen you need to click the Enable 1.4 Features checkbox.

 

2installsuccess.jpg

 

Once you install you see the success.

 

Go to the Settings Tab to enable some new features:

3figenable.jpg

 

I can enable the launch query designer button and Create Web Application (launch Design Studio)

 

Note to BEx launch query designer you need to install the latest SAP Gui patch for 730; otherwise it is greyed out.

 

4querydesigner.jpg

 

After clicking "Launch Query Designer" you can then launch the BEx Query Designer:

5bexquerydesigner.jpg

 

More to come in future  - read access mode, reset data source, etc.  Watch the BusinessObjects Analysis Office space.

We had an ASUG Analysis Influence Council at ASUG Annual Conference where attendees learned how the ASUG Analysis Influence Council influenced SAP:

 

Features influenced by the ASUG Analysis Influence Council

Analysis Office

1) Converting BEx 3.5 workbooks

2) Variants

3) Launch BEx Query Designer (coming in 1.4 release)

 

1alex.jpg

Picture of SAP's Alexander Peter by Joyce Butler

 

Analysis OLAP - features where ASUG Analysis Influence Council provided input

1) new prompt dialog

2) custom grouping

3) mobility for Analysis OLAP

 

 

We also learned that the broadcasting feature will come to Analysis Office through a BI4.1 feature pack later this year.  The planned release for Analysis Office 1.4 is early June.

 

Ian McAlpine said that the ASUG Analysis Influence Council "feedback is highly prized and valued to SAP."

 

BEx Quo Vadis Community Lounge

Right after this session there was an ASUG Community Lounge session titled "BEx Quo Vadis" or "BEx where are you going".  We had a similar community lounge at last year's conference but no one showed up.  I was quite skeptical about doing it again, but Lee Lewis insisted and fortunately Joyce Butler and SAP's Eric Schemer agreed to do it.

 

 

We had a great turnout this year, and special thanks goes to Lee, Joyce and Amy Finke of ASUG Headquarters.  Amy redesigned the lounge areas, which were called "theatres", which was a huge improvement.

2pic.jpg

Photo by Danny Pancratz

 

Oftentimes people hear that "BEx is going away" - this is not the case for the BEx Query Designer.

 

An attendee asked "what are the gaps between Analysis Office and BEx Workbooks?"  Alex said he felt document integration and broadcasting were still two missing features.

 

SAP also said that "BEx query designer remains core modeling tool for BW".  "We are not replacing BEx Query Designer" says SAP in BEx Quo Vadis session. 

 

Here is a link to a vine video where Alex discusses Analysis Office.  Also Jie Deng answered questions about placing Design Studio applications on the Enterprise Portal (minimum version required is 7.3) - here is the link to the vine video.

 

Thanks to all who attended and thanks to SAP's Alex Peter, Jie Deng and Ian McAlpine for their support of ASUG.

Alexander Peter, Product Owner for Analysis Office, provided this webcast to ASUG yesterday.

 

Since the discussion covers future versions, this is subject to change.

1abex.jpg

 

 

Figure 1: Source: SAP

 

Figure 1 shows that BEx tools are in maintenance mode.  There is not an immediate need to go off BEx clients but no new innovation.

Innovation comes with the Analysis clients.

 

Content with BEx Analyzer – conversion support exists today in Analysis Office.

 

1whatsnewaoffice14.jpg

Figure 2: Source: SAP

 

Analysis 1.4 release is smaller than 1.3 as SAP focused on improving performance.  SAP launched a performance project to review performance challenges from end to end. 

 

Alex said there are two collective notes to help you identify things on backend to streamline communication between server and client.

 

Main feature was broadcasting on BI Platform, another gap for BEx Analyzer– for legal reasons can’t release GA .  SAP chose the BI platform to use the scheduling features.  Broadcasting is planned for Q3.

 

GA for 1.4 is planned for May.

 

SAP has enchanced interoperability with Design Studio – in 1.3 you could copy a data source and insert it in Design Studio.  Now it is extended to be an automatic transformation to Design Studio – crosstab, chart, and components into an iPad application.  The scenario is to have a prototyping tool in Analysis Office and can use it to prototype a dashboard.

 

Strengthen BI platform support – if you store it in Launchpad ad start it from the Launchpad – before only Excel opens.  It didn’t know about Analysis Office workbook.  SSO wasn’t honored.  Now solved in with Analysis Office 1.4 and BI4.1 platform

 

Waterfall charts not available in native Excel so they have added them to the toolbar in Analysis Office 1.4.

 

With the ASUG Influence Council, SAP prioritized features.  After broadcasting query designer was a the top. 

 

You can open the Query Designer from Analysis Office 1.4 .  You can reset data source to query designer

 

With the read mode feature you can change from reading from the cube or the master data.

 

Time-dependent hierarchy at the given key date is now available in 1.4.

 

Data refresh – before 1.4, when press refresh, render cross tabs but not show the latest data – re-rendered data – had to leave Excel and open it again.  With 1.4 have a real data refresh.

 

They have added a member selector for mass data

 

 

2aofficeroadmap.jpg

Figure 3: Source: SAP

 

Figure 3 was not covered during the webcast, but it gives you an overall direction.  Today is version 1.3, planned innovation is Analysis Office 1.4.

 

 

Subset of Q&A:

________________________________________________________________

Q: Will AA 1.4 allow us to create or design Queries in BW?

A: The Query Designer remains the tool to create queries in BW.‑

________________________________________________________________

Q: Will AA 1.4 finally have LogOn / LogOff button?

A: No plan for a logoff button... but I am aware that AA can hold onto connections for an extended period so this issue is being investigated.‑

________________________________________________________________

Q: is it possible to connect to Essbase cubes directly?

A: Not from Analysis Office‑

A: ... but Yes with Analysis OLAP‑

Q: is AA 1.4 compatible with BOBJ 4 SP5?

A: It is platform independent‑

________________________________________________________________

 

Q: What is the comparability of AA 1.4? We are on BI 7.0 and BOBJ XI3.1 sp 3.

A: It is platform independent - there are some features dependent on BW version but overall it is platform independent.‑

________________________________

 

 

ASUG Annual Conference

Alex is presenting at ASUG Annual Conference regarding the ASUG Influence Council.  Below is a list of other ASUG Analysis-related sessions for the conference:

 

Session IDTitle
301Deep-dive into Analysis for OLAP, the next generation tool for web-based multidimensional analysis.
305What’s new with Analysis Office & OLAP?
308Customize the Look and Feel of BW Reports in SAP BusinessObjects Analysis (Office) by Using API Calls
4210Influence SAP - ASUG SAP BusinessObjects Analysis Office/OLAP Influence Council
      CL32BEx Quo Vadis - "Where is BEx Going"
312Moving Beyond BEx: Strategies for Unlocking the Value in Your BW Data with BI 4

Hello Everyone!

In this blog i would demostrate that with SAP BusinessObjects Analysis edition for MS Office release 1.3 is possible, finally, to save workbook on SAP BW.

 

The previous realease of this frontend tool allowed to save workbook only on the BI platform.

 

The file format that you can store are: xls,xlsx, ppt and pptx and is necessary on SAP BW have an role that contain the authorization object R_RS_AO.

 

aut_obj_aoffice.PNG

To save an workbook on SAP BW is very simple : after the workbbok is developed , selecting the Office Button , is available the function "Save Workbook" and "Save to SAP NetWeaver".

 

analysis_bw1.png

Then a window appear that permit to choice where save the workbook: in a Role or in the folder "My documents".

 

analysis_bw2.png

In SAP NetWeaver Business Warehouse, Analysis objects are stored with object type AAOE (for workbooks) and AAOP (for presentations).

 

When the user, access to SAP BW by SAP GUI, the workbook will be visible on the menu:

 

analysis_bw3.png

To launch the analysis workbook from SAP menu is necessary SAP Netweaver BW 730 SP8 (see SAP Notes 1724842).

The ASUG BI Analysis Special Interest Group launched an ASUG Influence Council for the BusinessObjects Analysis Suite two years ago, initially focusing on BusinessObjects Analysis, Edition for Office, as BusinessObjects Analysis, Edition for OLAP shipped with BI4 platform.

 

SAP’s Alexander Peter has served as our SAP Point of Contact to this council and ASUG Volunteer Joyce Butler leads the council. 

 

The Influence Council was promoted during an ASUG kick-off webcast two years ago. Interest was high as Analysis Office is the “premium successor” to BEx Analyzer.


 

How did ASUG council members influence SAP?  See below on features that are generally available that ASUG influence council members influenced:


 

BEx Workbook Conversions:

 

Analysis menu in File ribbon > Convert BEx workbook

1convert.png


Not just BEx 7.x workbooks but also ASUG members influenced the decision to look at converting BEx 3.5 workbooks.

 


 

 

BW Workspaces:

 

ASUG Influence Council members influenced BW workspaces as well.  What are workspaces?  SAP describes them asBW Workspace is a dedicated area in SAP BW for a specific department to upload local data and combine it with central data"

2workspaces.png

Source: SAP

 

For more details watch Alex’s TechEd online session at http://www.sapvirtualevents.com/teched/sessiondetails.aspx?sId=3420

 

Prompting/Variants

 

Council members gave feedback into features such as prompting and variants, which became part of the 1.3 latest release:

3promptsvariants.png

Council members also influenced prompting features such cutting and pasting from the clipboard into the prompts.

 

 

Additional improvements include better PowerPoint integration for Analysis Office with SAP teams visiting council member customer sites.

 

Questions about ASUG Influence?  Email influence@asug.com


When should I move from BEx Analyzer to Analysis?  These are questions frequently asked at conferences. 

 

I attended this Analysis webcast last Thursday provided by Nick Wall, SAP.  He shared some suggestions about why/how you should adopt BusinessObjects Analysis.

 

On the agenda:

  • What is BusinessObjects Analysis?
  • Why you should adopt SAP BusinessObjects Analysis
  • How you can adopt SAP BusinessObjects Analysis

 

 

What is it?  Why should you consider adopting?  How adopt?

 

1fig.png

Figure 1: Source  SAP

 

What is Analysis? Analysis OLAP allows you to query OLAP data, develop analytical applications, develop additional capabilities and it has 3 clients:

 

  1. Analysis Edition for MS Office – add in to Office that allows you to do analytical multi-dimensional analysis – can be used standalone…premium alternative to BEX Analyzer
  2. Analysis Edition for OLAP – web-based – business analysts can run their own analysis private or share via BI platform – successor to Voyager
  3. Design Studio – coming Q4 – premium alternative to BEx Web Application Designer – power users to create OLAP dashboards – enabled for mobile support –deploy on desktop or mobile devices

 

 

Analysis MS Office offers a familiar environment, add-in to Excel, and extends your Analysis with Excel. It has API calls.  You can create Excel formulas in Excel and extend via API calls.

 

You can combine datasources via BW or HANA to build analysis and it offers user interface customization via ribbons and menus.  You can also take Analysis in Excel and export into PowerPoint, and it is a live image, or go via BEx query into PowerPoint.  It is a live object;  you are not cutting and pasting.

 

Nick suggested using Microsoft Analysis for PowerPoint to build a board report

 

Why should you consider adopting?  How adopt?  That is what Nick covered in this webcast.

 

2fig.png

Figure 2: Source: SAP

 

Figure 2 shows that Analysis OLAP is the successor to Voyager

 

Analysis OLAP has the common look and feel of products across BI Suite

 

It connects to BW, HANA, EPM solutions, MSAS

 

It calls those information sources into a single view so you can share with others

 

It offers interoperability with other tools using Analysis Views with multidimensional analysis, save that result view as Analysis view, connect to BI platform and share that with other tools – share with wide user community such as Crystal Reports for Enterprise or Web Intelligence users.

 

3fig.png

Figure 3: Source: SAP

 

Figure 3 shows  how you can use Analysis to use new BI tools without disrupting current environment

 

It has a direct connection to BEx queries; there is no need to edit or migrate them

 

It supports 3.5 BEx queries and 7.x

 

BEx Query Designer will remain in place for the future; will continue to use

 

It offers automated migration of simple workbooks (1.3 version)

 

You can also run Analysis MS Edition for Office and BEx Analyzer Side by side.  You can deploy it without changing the infrastructure.  You can install Analysis for Office without uninstalling the BEx Analyzer but only one add-in can be active at a time.

 

Simpler, more intuitive to use, common interfaces across suite of products, which may allow increasing user autonomy, says Nick.  You can combine multiple data sources.

 

Nick said you can “extend the reach of your BW deployment”, reduce dependency on IT, minimize user training & support costs with Analysis.

 

 

How can you can Adopt Analysis

 

4fig.png

Figure 4: Source: SAP

 

Nick discussed best of breed tools, the right tool for the right job.  Register for the October 2nd How to Select the Right BI Tool for Your Environment to learn more.  This webcast is open to all.

 

5fig.png

Figure 5: Source: SAP

 

Figure 5 shows a high level mapping, a recommendation from SAP.  Today SAP recommends using Xcelsius for those using BEx Web Application Designer but in Q4 they recommend Design Studio (see Figure 5).

 

I asked Nick "where is Visual Intelligence" and he said it is part of the "Explorer" family.  Nick said the BEx suite has a “blank”  for Ad hoc Reporting

 

6fig.png

Figure 6: Source: SAP

 

Figure 6 shows suggestions from SAP on how to transition from BEx to Analysis

 

7fig.png

Figure 7: Source: SAP

 

Figure 7 shows a recommended phased  adoption of BI solutions for Classic BW Customers.  The slide suggests starting with Analysis for Office, then adding Analysis OLAP, Crystal Reports, and Explorer.  Lastly add Web Intelligence, the “best of breed”.

 

Hopefully many of you have seen this information before, but Figure 7 is the first time I’ve seen a recommended order of adopting BusinessObjects tools for SAP customers. 

 

 

If you are attending SAP TechEd, the following lists Analysis sessions for you to consider:

 

World Premiere of SAP BusinessObjects Design Studio

Latest Innovations in SAP BusinessObjects Analysis for Microsoft Office and OLAP

Implementation of SAP BusinessObjects Analysis at Colgate-Palmolive

Road Map for SAP BusinessObjects Dashboards and SAP BusinessObjects Design Studio

ASUG Influence Council – SAP BusinessObjects Analysis Update

Road Maps for SAP BusinessObjects Analysis

Better Reporting with SAP BusinessObjects Analysis, Edition for Microsoft Office


SAP BusinessObjects Analysis Office 1.3 - Upload data into Workspace as Transient provider

 

1. Launch AO 1.3 and Open the excel file containing the data to be uploaded.

2. Select the data range.

3. Click on Upload button in the DataSource ribbon.

IMG1.png

4. Connect to the specific BW system.

IMG2.png

5. Select a Workspace where the Transient Provider needs to be created.

IMG3.png

6. AO automatically recognizes the column headers as Dimensions/Measures and also detects the corresponding datatypes.

7. Technical name has a default namespace @3 followed by set prefix (defined in Workspace).

IMG4.png

8. We can modify the Technical Name, select the measures and change the datatype as we see fit.

IMG5.png

9. We can also choose if we just need to create the transient provider or to insert it in current sheet or in a new worksheet.

IMG6.png

10. Now a transient provider is created in BW system using a simple Excel data. We can perform all Analysis actions on this datasource, just like how we do it on any BW Query.

IMG7.png

1. Launch IDT.

2. Create New Project.

IMG1.png

IMG2.png

3. Create new Relational Connection.

IMG3.pngIMG4.pngIMG5.pngIMG6.pngIMG7.png

4. Publish Connection to BIP repository.

IMG8.pngIMG9.pngIMG10.pngIMG11.pngIMG12.png

5. Now the connection is published in BIP repository.

6. Goto CMC --> Connections The connection should be available there.

IMG13.png

7. Now AO can consume HANA connections from BIP.

8. Insert --> Select Datasource --> Connect to BIP.

9. Added HANA connection should be available in the list.

IMG14.png

At last week's ASUG webcast on SAP BusinessObjects Analysis Office 1.3 (now in ramp-up) updates for SAP Hana support with Analysis Office 1.3 were reviewed.  Since then I have received several questions so a summary of this part of the webcast is reviewed below:

 

1fig.png

Figure 1: Source: SAP

 

Figure 1 shows you can use the BI Platform from BusinessObjects or use the ODBC driver

 

Procedures are different with 64 bit – is the Office version 32 bit or 64 bit and then you have to choose the right version of the ODBC driver

 

2fig.png

Figure 2: Source: SAP

 

Figure 2 shows the steps to define the SAP Hana connection in BI4, using the IDT

 

3fig.png

Figure 3: Source: SAP

 

Figures 3-5 shows connecting via the Local ODBC driver

 

4fig.png

 

 

Figure 4: Source: SAP

 

5fig.png

Figure 5: Source: SAP

 

 

SAP Analysis Office started supporting Hana in version 1.2 and could access analytic and calculation views

 

6fig.png

Figure 6: Source: SAP

 

New in Analysis Office 1.3 is the support of hierarchies, prompts, and password change with SAP Hana.

 

7fig.png

Figure 7: Source: SAP

 

Figure 7 shows variables in SAP Hana and the properties you specify, that are reflected in Analysis Office – e.g. mandatory or multiple variables.

 

8fig.png

Figure 8: Source: SAP

 

Figure 8 shows how the SAP Hana settings impact Analysis Office prompts screen

 

Ranges, single value, interval, multiple entries are shown above in Figure 8.

 

9fig.png

Figure 9: Source: SAP

 

Figure 9 shows hierarchies in SAP Hana

 

You can open the Analytic view; opening the logical view allows you to select the hierarchies

 

10fig.png

Figure 10: Source: SAP

 

Hana supports 2 types of hierarchies.  One is the Level hierarchy where you specify several characteristics in Hana and a specific order – such as Continent, Country, City ID in Figure 10.

 

Another type is the parent child – one dimension ID and for each ID you have a Parent ID – shown in the right of Figure 10 where manager ID is the parent.

 

 

Related Links:

SAP Help for Analysis Office 1.3:

http://help.sap.com/boaoffice13

 

See the SAP Hana Development Center for access to SAP Hana Developer licenses:

http://scn.sap.com/community/developer-center/hana

SAP BusinessObjects Analysis Office 1.3 - Activate/Deactivate BEx conditions

 

  1. Open a query which contains BEx condition (defined in Query Designer).
  2. Invoke context menu (on any member/data cell).
  3. A new option "BEx Conditions" will be available. And all the conditions defined in the Query will be listed here.
  4. We can choose to activate and deactivate them at will.IMG1.png

 

Pre- Requisite:

SAP BusinessObjects Analysis 1.3.

SAPBusinessObjects Analysis Office 1.3 - Launcher (document/query/view) from backend

  

  1. Log on to the BW system.
  2. Transaction --> RAAOE (AO Excel). RAAOP(AO PPt).
  3. We get four options(radio buttons) here.To launch - IMG1.JPG
    1. Client Only
    2. Document (Workbook in case of Excel and presentation in case of PPt
    3. Query
    4. Query View
  4. Based on the aforementioned selections we get options to enter secondary parameters.
    Document name or Query Name or Query View name. IMG2.JPG
  5. In case of Document an additional parameter (checkbox) comes up Force Refresh. This option can be used to forcibly refresh the
         workbook/presentation on open.This setting overpowers the workbook/presentation setting “Refresh Workbook/Presentation on Opening”.
  6. Click on execute button (F8).
  7. Based on the selection, document/query/query view opens.

 

Pre-Requisites: 

  1. AO 1.3 installed in the system.
  2. BW system >= 701.

Last week SAP provided an ASUG webcast on Analysis Office 1.3 which is now in ramp-up.  Below I am focusing only on converting BEx Workbooks to Analysis Office and I am also including the webcast’s question & answer to share with everyone.

 

1figworkbook.png

Figure 1: Source: SAP

 

In this webcast, SAP said the workbook conversion is not a tool for mass migration

 

It takes into account the filter and navigation state of data sources

 

It is not an item-centric approach; some components not converted such as navigation pane/block as Analysis has the Design panel

 

There is a detailed log created if you enable it – what is converted/not

 

2fig.png

Figure 2: Source: SAP

 

Figure 2 shows what is a convertible object and what is not convertible.

 

Buttons are not converted due to Ribbon concept in Analysis Office.

 

3fig.PNG

Figure 3: Source: SAP

 

Figure 3 shows the conversion options and possible settings

 

Recommendation is to show the log in an invisible sheet so you get an idea what is happening.

4fig.png

 

Figure 4

 

Figure 4 shows an actual conversion of a BEx Workbook to Analysis Office.  You can see on the bottom of Figure 4 that the navigation pane is not convertible.

 

 

Related posts:

BusinessObjects Analysis Office 1.3 Your First Steps and New Features

 

BusinessObjects Analysis Office 1.3 – Does it have Report to Report Interface

 

 

Below is the Q&A from last week’s webcast.

 

Question & Answer from ASUG Webcast on Analysis Office 1.3

 

Q: Is there a size limit on the file that can be uploaded (reference to BW workspaces)

A: Have not specified a size limit; validate with customer scenarios

________________________________________________________________

 

 

Q: Are there any performance/functionality differences between accessing HANA via JDBC and ODBC

A: There is detail information on the connections at http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_data_acs_en.pdf

________________________________________________________________

 

 

Q: I want to schedule the analysis workbook

A: it is planned ... most probably in a 2013 release

________________________________________________________________

 

Q: any updates on Dynamic Parameter from Web Intelligence or Crystal  to Analysis workbook

A: Have RRI support as sender. The next release should also cover the receiver side.

________________________________________________________________

 

 

 

Q: Is there a variable to choose which hierarchy to be used?

A: sure, you can use hierarchy variables.

________________________________________________________________

 

Q: Is it possible to apply a OSS note and get this functionality of Saving workbooks on SAP Netweaver system instead of upgrading the whole system to SP11....?

A: unfortunately not

________________________________________________________________

 

Q: Can we refresh individual worksheets instead of the complete workbook?

A: sure, you can refresh individual data sources. Context menu -> Refresh

________________________________________________________________

 

Q: Can BEx workbook be converted to Analysis for Office document if BEx workbook have multiple queries on multiple tabs?

A: yes, conversion also works with several data sources

________________________________________________________________

 

 

Q: Have you seen these workspace upload features used to deploy a planning/budgeting solution, where users upload updates to their budget numbers periodically?

A: no, planning is typically more than just entering new values … it is about planning functions etc. … we’re currently discussing extending that functionality to planning some time, but that is not yet confirmed yet …

________________________________________________________________

 

Q: Is it possible to refresh the workbook which is transported to another system

A: Yes

________________________________________________________________

 

Q: Can we use "Calculation" functionality for Analysis for Office on top of HANA?

A: yes, some calculations are supported ...

________________________________________________________________

 

Q: Will BEx conversion to Analysis for Office document support BEx structures?

A: Sure, they are part of the query/data source, not of the workbook

________________________________________________________________

 

 

Q: Any body know if there is a way of integrating an analysis in MS Word. Is it possible to embed a presentation containing an analysis? Is Live office the only alternative? ( but seems there are some limitations currently if report is using BICS connection)

 

A: no Word integration at this point in time … planned, but currently not with highest priority.

 

 

My thanks to SAP's Alexander Peter and Tobias Kaufmann for this webcast.

Actions

Filter Blog

By author: By date:
By tag: