The 1 Million Cell Limit when extracting BEX Queries and InfoCubes with the BWCube component from SAP BW

The BWCube component enables you extracting a BEX query or an InfoCube via the MDX Interface.
The MDX interface of the SAP BW system has a known restriction, if the Result contains too many cells (more than 1 million cell).
SAP officially states: "In general the MDX interface is not suitable for accessing mass data".

In this blog I will show you how to proceed in this case.

How to load SAP-data with Xtract Universal to Microsoft Access (and Excel)

Xtract Universal is since 2011 the newest member of the Xtract product line. Eight different SAP extraction types allow the BI architect to create an optimal, stable and fast data layer between SAP and different consumer applications.

Xtract Universal essentially consists of two components: a server component and a designer (client). The server component is responsible for the actual extraction. The consumer application communicates with the Server component, and these in turn with SAP. You use the Xtract designer for the design and configuration of the extractions.

Fore more information refer to the products page: http://www.theobald-software.com/en/products.htm
or to the help page: http://help.theobald-software.com

In this blog, I will show you how to load SAP-data with Xtract Universal to Microsoft Access.

How to extract a BEx Query and a BW Hierarchy with SSIS / Xtract IS from SAP BW and build them in SSAS

In this blog I will use Xtract IS to extract a BEx query and a BW hierarchy from an SAP BW system and load them into SQL Server. Then I will show how to build a cube in SSAS upon the BEx query and the BW hierarchy.

How to load a BW Hierarchy into QlikView using Xtract QV

In this blog I will use Xtract QV to extract a BW hierarchy from an SAP BW system and load it into QlikView.

SAP BW hierarchies are modeled as parent-child hierarchies. I will show how to convert it to a naturalized Hierarchy to use in QlikView.

How to load a BW Hierarchy into PowerPivot using Xtract PPV

In this blog I will use Xtract PPV to extract a BW hierarchy and a BEx query from an SAP BW system and load them into PowerPivot. Then I will show how to build the relation between the Hierarchy and the BEx query in PowerPivot.

Create Generic Datasource using Function Module and Timestamps

You can create a generic datasource using a function module.

For delta functionality you need a delta field. Some tables like VBAK (Sales Document: Header Data) don't have a timestamp field for creation/change that we can use as a unique delta field but have separate fields for creation date (ERDAT), creation time (ERZET) and change date (AEDAT). To get the data of the VBAK table by using delta functionality we will create a generic datasource using a custom function module which implements the necessary logic.

We will create an extraction structure that has a timestamp field and we will use this field to implement the delta functionality. In this blog I will explain this concept.

How to use Xtract QV with HTTPS

As of January 2010 Xtract QV offers a new feature to enable SAP data extraction via https. In this blog I will describe how to set up Xtract QV and your environment to use this new feature.

Xtract QV provides the following server settings:

- EnableSecureListener: default is no. To enable https set the kind column to individual and the value to yes.

- SecureListenerPort: default is Port 8185.

After changing the settings click on Save which will restart the Xtract QV Server.

Now you should install the ssl certificate:

- Obtain an ssl certificate from a certification authority and install it on your IIS web server. Be aware that the certificate common name must match the host name exactly. (refer to http://technet.microsoft.com/en-us/library/cc732230%28WS.10%29.aspx)

- Add a new SSL server certificate binding and corresponding client certificate policies for an IP address and port.

(refer to http://technet.microsoft.com/en-us/library/cc725882%28WS.10%29.aspx)

To test the https feature we will create a Self-Signed Certificate on Windows 7 :

• Open IIS Manager and navigate to the level you want to manage.

• In Features view, double-click Server Certificates.

• In the Actions pane, click Create Self-Signed Certificate.

• On the Create Self-Signed Certificate page, type a friendly name for the certificate in the Specify a friendly name for the certificate box, and then click OK. For more information refer to http://technet.microsoft.com/en-us/library/cc753127%28WS.10%29.aspx

Now the ssl certificate is installed. In the next step we will use the SHA hash (thumbprint) of the certificate for the configuration.

We will use the netsh prompt configure the server certificate binding and corresponding client certificate policies. Be sure to start the command prompt as administrator. Type the following command

netsh http add sslcert ipport=0.0.0.0:8185 certhash=1c56412e86cd76751f1dfddd2af594dd1b8fb7c5 appid={5ca9af00-8fc2-4f1c-938d-a4ed5f654ccc}

where ipport specifies the IP address and port for the binding, certhash specifies the SHA hash (thumbprint) of the certificate and appid specifies any GUID to identify the owning application. For more Information refer to http://technet.microsoft.com/en-us/library/cc725882%28WS.10%29.aspx#BKMK_2

Now select the extraction in Xtract QV Designer and select Run in Browser from the context menu. Since our ssl certificate is not issued by a trusted certificate authority, your browser may alert you. In Internet Explorer you will get the error: There is a problem with this website’s security certificate. Just click on Continue to this website (not recommended) to ignore the error.

As you can see in the web browser the URL starts with https.