Get your own SAP HANA DB Server using Amazon Web Services

Someone who is interested to explore SAP HANA and do not have access to HANA DB server can use the Amazon Web Services by creating their own instance on the cloud. Please keep in mind that this is not a free service and after completing the free trial limit you will be charged by Amazon based on the usage and volume of data.

The steps are very simple in order to create our own HANA instance using Amazon cloud services. We need to follow the below mentioned steps in order get the access to the SAP Hana instance running on the cloud.

1.   Account creation in Amazon Web Services. We need to have an account on the Amazon Web Service as a first step, please note while creating the account, you will be asked to enter the credit card information which is mandatory in order to complete the registration.

http://aws.amazon.com/

2.     Now once we have the account setp up, we need to generate the “Key Pair”. In order to create the Key pair we need to go to the “Amazon Management Console” àEC2 à Left hand side we should see the option called “Key Pairs”, give a Key pair name. You will be asked to save the Key Pair file which we should keep in a safe place as this will be used further.

3.      Once we have the Key Pair ready, we are ready to go to the SAP site to register for the Hana. You need to Login and need to accept the License agreement. In case if you go not have the account in the SAP SCN then we need to create first in order to move further.

4.   After accepting the licensing agreement, you will be asked to enter the Amazon account number which you can find from the Amazon Web Services.

5.     Enter the Amazon account number and in the next page you will be asked to enter the Key Pair name which you already created in the Amazon Management console. Enter the Key pair name and click Continue. Regarding the HanaInstaceSize, use the default option “m2.xlarge” until you have need of some extra memory.

    

      

6.  In the final step, you will be asked to enter the Stack name and new stack will be  created in few minutes.

7.     Now we should again login in the Amazon Web Services and go to the Management Console, click on the EC2 and you should see the option called “Launch Instance”. After clicking on the “Launch Instance”, the SAP Hana instance will be initiated and ready to use. We will have to give the IP Address of this instance from the HANA client in order to connect my HANA DB hosted on Amazon Web Services.

8.      In order to install the SAP Hana Client, please click on the link and download the Windows (32 and 64 bit) or Linux version of client.  

9.   As mentioned in starting this is not a free service and you should be careful about the usage. Also in order to reduce the usage cost you should start the instance only when we are going to use. The rest of the time we can easily stop the instance, it takes less than a minute to restart the instance. In order to Stop the instance, go to the Amazon Management Console,   go to the EC2 and in the home page in the left hand side under instances we should the option called “Stop” used to stop the instance. 

For more details on Amazon Web Services and SAP HANA DB Server, you can visit the SAP Community Network  

SQL Server Analysis Services Server Memory Issue

Sometimes we might have noticed that SSAS is consuming too much memory in the server and creating a deadlock kind of situation where the only solution is to restart the SSAS service or restarting the server.

This situation arises when bulky MDX queries are running and trying the fetch the large volume of data. Since in the SSAS the MDX queries are constructed with in the memory, the MDX query requesting for the large amount of data can be very much memory intensive. We can avoid these kinds of situations by changing some default memory settings in the server. Before we talk about the solution I think it is very important to understand some of the memory setting properties within the SSAS. I have captured their details below as per described in MSDN.

LowMemoryLimit

Specifies the amount of memory allocated by Analysis Services at start up. When this limit is reached, the instance will start to slowly clear memory out of caches by closing expired sessions and unloading unused calculations. The server will not release memory below this limit. The default value is 65; which indicates the low memory limit is 65% of physical memory or the virtual address space, whichever is less.

TotalMemoryLimit

Defines a threshold that when reached, causes the server to deallocate memory more aggressively. The default value 80% of physical memory or the virtual address space, whichever is less.

Note that TotalMemoryLimit must always be less than HardMemoryLimit

HardMemoryLimit

Specifies a memory threshold after which the instance aggressively terminates active user sessions to reduce memory usage. All terminated sessions will receive an error about being cancelled by memory pressure. The default value, zero (0), means the HardMemoryLimit will be set to a midway value between TotalMemoryLimit and the total physical memory of the system; if the physical memory of the system is larger than the virtual address space of the process, then virtual address space will be used instead to calculate HardMemoryLimit.

In order to fix the memory freezing problem, we should change the default Memory\HardMemoryLimit setting.  We should keep this value between the Memory\TotalMemoryLimit and 100. Since the TotalMemoryLimit is 80 by default, then in this case we can set the HardMemoryLimit to 90. What will happen is anytime when the memory exceeds 90%, the SSAS will start cancelling the queries.  The users might see the error message saying “The operation has been cancelled due to memory pressure”.

In order to set the Memory\HardMemoryLimit property to 90, please follow the steps below

1.       Login in the SSAS using the SQL Management Studio.

2.       Right Click on the Server Name and Go to Properties.

3.       Look for the “Memory\HardMemoryLimit” property and set the value to 90. By default it is 0.

New Business Intelligence Features for Office 2013

After exploring quite a bit the preview version of Office 2013 it is exciting to see the nice features coming which are closely integrated with the Business Intelligence.

The office 2013 excel looks great, apart from the look and feel which I liked. There are a lot of things to explore in terms of Business Intelligence.

Some of the key features I have described below

1.     The xvelocity (aka Vertipaque) in memory database is now integrated with excel 2013. The Power Pivot comes bundled with the Excel 2013 but it is not enabled by default. In order to use the enable the PowerPivot the steps are mentioned here in detail.

Integrating xVelocity simply means adding more power in Excel and I am sure users end users will like it as now they will have the ability to pull the large amount of data, defining the relationships, creating their own data model and designing the interactive dashboards.

There are some enhancements done in the Power Pivot with in excel 2013

a)      TimeLine feature in addition to “Slicer” functionality which we already have.

b)      We can now integrate the multiple pivot tables and can create a model out of that.

c)       Ability to mash several chart types.

I believe adding these features will help excel coming out of from the tag of “ad hoc tool” for  analysis and will be able to do a lot more than this.

2.     Another important integration is done by providing the ability to create a sheet using “Power View”. We can go to the insert Tab and click on the Power View button to do so. The most important thing is now Power View supports the maps as well.  We can easily import the spatial data and display the interactive maps in excel.

                                   

3.     Office 2013 is now can connect to the Windows Azure Market place. This means we can now import the data hosted on Data Market. This seems to be very important feature with respect to cloud by providing ability to pull the data in excel hosted on cloud.

 

Microsoft Office 2013 Customer preview available to download now

Microsoft has recently released the customer preview of the Microsoft Office 2013. This new version of Office 2013 is compatible for both the touch screens and desktops. However the new features of the Office 2013 are tailored to the Windows 8 but this is still going to support the Windows 7. The customer preview can be downloaded from here

Microsoft has also announced that Office 2013 will not be supporting the Windows XP and Vista and they will not be able to upgrade on top of these two versions.

UnPivoting data in SQL Server

There are some situations when we need to do the data comparison row level. One of the solution of this kind of situation is to do the "Unpivoting" of data which will apparently move the records under a specific column and then we can do the filter of the data by using the "Where" clause.

Here is an example.
Lets take the below sample code

declare @Test as table(id int, col1 int, col2 int, col3 int, col4 int)
insert into @Test values(1, 100, 500, 300, 200)
insert into @Test values(2, 600, 300, 500, 400)
insert into @Test values(3, 400, 100, 800, 300)

select * from @Test

The expected output from the above table is to get the each rows max value. This means the output should be
ID MaxVal
1 500
2 600
3 800

Since in this case we need to do the comparison at row, the first thing which should be clicked is unpivoting of data and then do the normal filtering. To get this output we will use the SQL Server Unpivot command as below.

SELECT id,Max(colVal) as MaxVal
FROM
(SELECT * FROM @Test) T
UNPIVOT
(
ColVal FOR columnnames in(col1,col2,col3,col4)
) as UnPvtTable
Group by ID

Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach

Here is the link available for you to download the white paper on Implementing the parallel Data warehouse using Kimball approach.
Download White Paper here

SharePoint 2010 PerformancePoint Services and PowerPivot Hands on Labs

This would be injustice if I do not share the detail on a nice training material available on Business intelligence 2010. I wanted to create and publish some KPI’s, Scorecards, Filters, Reports and Dashboards using PPS 2010 along with PowerPivot after creating the Business Intelligence site in SharePoint 2010 environment and I came across channel9’s excellent hands on lab which covers all these.

http://channel9.msdn.com/Learn/Courses/SQL2008R2TrainingKit/SQL10R2UPD03

The first three hands on labs cover all the above mentioned things. The hands on labs are using Adventure Works cube as a data source. Each lab has a word document which contains complete walkthrough of the exercises and it is very easy to follow.
Thanks to Channel9 team for their efforts on preparing such a wonderful hands on lab.
There is one more thing which I would to mention about a SharePoint 2010 VHD available which contains some PPS 2010 sample dashboards. I have not tried this but just wanted to share with you.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en

For more information about PerfromancePoint Dashboards in this VHD, you can look into the below blog.

http://sptechland.wordpress.com/2010/06/09/performancepoint-dashboards-in-sharepoint-2010-vhd/

I would recommend trying the hands on labs if you want to learn creating KPI’s, scorecards, reports, and filters, dashboards using PerformancePoint 2010 along with PowerPivot.

I hope this helps

Thanks,
Gaurav Mittal