Clariba is a fast-growing, independent consultancy that represents clarity, professionalism, integrity, commitment, teamwork and quality — to name a few. Our People, our Solutions and our Expertise are what makes us stand apart from the competition. Through photos of our team — representing 18 nationalities — and their words, our team members will share their point of view on why they stand proud as Clariba — giving our followers, customers, partners and even future employees a glimpse into our culture and workplace. Follow us on LinkedIn, Instagram, Twitter to be part of the I Am Clariba experience — #BeClariba.
Recently, we upgraded Tableau from version 8.0 (32 Bits) to 8.1.7 (64 Bits) along with Automatic Login (Single Sign On) and PostgreSQL configuration (Access to the Tableau system Database). After the successful upgrade of this application and configuration of the named features, I would like to share my experience as they may be useful in case you face a similar situation.
Based on Clariba’s specific expertise and successful completion of all requirements of the SAP® Recognized Expertise Program, Clariba Consulting Middle East has been granted the SAP Recognized Expertise designation in SAP HANA® and Business Intelligence.
Building geographical information on top of maps is a topic that is becoming trendier and trendier and this is where big investments from companies are focusing on during this and coming years. We all have seen an explosion of public geo-information brought to our mobile devices and consumers at corporations are asking themselves why they cannot have that same flashy stuff with their corporate information being shown on a map, either on mobile or on desktops. We have researched across most of the SAP BI modules and also some competition and analysed the features that one or another can offer; you can find below the results of this benchmark study.
Data mining, as per definition, is a discipline whose main aim is to discover patterns and most importantly, to predict and gain more knowledge on your data. This is done by combining different methods and approaches from artificial intelligence and statistics disciplines. One of the most common problems is how to build accurate and optimal classifier that given raw data helps us to build a model to classify future cases. There are many algorithms and methods available and we will refer in this blog article to the C4.5 which is available in the SAP Predictive Analytics tool. SAP Predictive analytics (SAP Infinite Insight) provides data mining capabilities that help many companies to anticipate customer behaviors and demands.
More and more SAP products integrate "R" as data processing, statistical modeling language of choice. Acquiring R expressiveness in our skill set is now a strategic achievement in the BI sector, so Clariba has composed a list of resources available on the Internet for auto-training in R skills.
SQL Server Integration Services (SSIS) is the ETL (Extract, Transformation and Load) tool in the Microsoft Business Intelligence suite. Microsoft changed the way they handle data loads completely with the introduction of SQL Server 2005, offering complete ETL functionality as opposed to mainly Extract and Load functionality available in earlier versions. The release of SQL Server 2012 introduced another significant improvement on previous versions, providing a new way of configuring packages for deployment. Once development of packages has been completed, the challenge is to deploy those packages from a development environment to test or production environment without having to manually reconfigure them for the new environment. Configuring packages in SSIS has not always been very easy. A main problem was the different possibilities that developers had, including XML configuration files, Environment variables, Registry entries, Parent package variables and SQL Server configurations.
In SSIS 2012 the configuration management has been redesigned and now there is one common way to do this. In SSIS 2012 parameters on project and package level have been introduced, as well as environments in the SSIS catalog and together with parameters it offers a new opportunity to configure packages during runtime.
There now exists two different deployment models to help deploy SSIS packages and projects to different environments such as development, test and production. The two deployment models are the project deployment model and the older package deployment model. This article describes how to perform project deployment on SSIS packages in SQL Server 2012.
Project deployment model
Once package have been developed and tested it needs to be deployed to an environment where it will be used, this can be either development, testing or production.
In SSIS the configuration manager is used to create the different environments. To open the configuration manager either select it from the drop down list on the main toolbar next to the Start Debugging icon
or right click on the project and select Properties.
To open the Configuration Manager just select Configuration Manager... in the top right corner. To create a new configuration scenario select <New...> from the Active solution configuration drop down list.
Provide a name for the configuration and either copy settings from an existing configuration or create new configuration settings. Click on Close to return to the Project Deployment model.
Usually the Server Name and Server Project Path differ between environments so by setting those values for the different environments it is possible to deploy the package to different systems only by choosing a value from the drop down list.
Now in combination with parameters the project deployment model gets more useful as it is possible to assign different values to parameters for each scenario, for example you can assign different connection strings to each of the development, test and production environments. It provides the opportunity to execute packages against different systems during design time simply by choosing a different scenario form the drop down list, and also the possibility to deploy the project and packages with the correct configurations for the different environments.
Parameters are new to SSIS with SQL Server 2012 and can be created on project or package level. If defined on project level then it can be used in all packages within that project and if it is created on package level then it can only be accessed within that specific package. Parameters can't store results from Execute SQL Tasks but other than that works similar to variables.
To create project level parameters double click on Project.params in the Solution Explorer window.
Click on the Add Parameter button to create a new parameter and set the following properties:
- Name - Provide a descriptive name for the parameter.
- Data Type - Specify the data type of the parameter.
- Value - The default value of the parameter during design time.
- Sensitive - If a parameter is flagged as sensitive then it's value will be encrypted when the project is deployed.
- Required - Indicates that it is mandatory to pass a value to this parameter before the package can be executed.
- Description - Provide a description of the purpose of the parameter.
Similarly to create package level parameters just click on the Parameters tab on the Package design window. New parameters are created in the same way and the same properties should be set.
If different scenarios have been applied to the project then it is possible to assign different values to a parameter for each scenario. This can be done by clicking the Add Parameters to Configurations button.
Click on Add and select the parameter you want to configure for the different scenarios.
Change the values for each scenario and click OK when finished. here you can for example set the connection string or server name or file path for the different servers.
Now that the environments have been created and the parameters have been created and configured for the different environments the integration services catalog needs to be created on the server in order to deploy packages to the server.
The catalog is used to store, manage and monitor deployed packages. The catalog uses CLR stored procedures so CLR integration needs to be enabled. After CLR is enabled you can use SQL Server Management Studio Object Explorer to create the catalog. Just right click the Integration Services Catalogs and select Create Catalog...
You will be asked to supply a password which is used to create a database master key used to encrypt sensitive data in the catalog.
After creation the catalog is displayed twice in the object explorer, first in the databases node and also in the Integration Services node. In the database node you can work with the catalog SSISDB the same as with any other user database. In the integration services node you can administer the catalog by right clicking on the child nodes.
The catalog stores all environments which will be used to configure packages during runtime. Each folder created in the SSIS catalog contains two subfolders - Projects folder where SSIS packages will be deployed and stored and the Environments folder which manages the different environments, created for specific projects.
After the SSIS catalog has been created and configured and at least one custom folder has been created to store projects and packages, then environments can be created in the appropriate subfolder. Each custom folder can store different projects and environments so it is possible to reuse already defined environments and also to define more than one environment per project.
To create a new environment simply right click the subfolder and select Create Environment... and then specify a name and description for the environment. After clicking OK a new entry appears in the Environments folder.
To configure the newly created environment just double click to open it. Three nodes exist on the left pane - General, Variables and Permissions.
The General node displays the name, identifier and description of the environment.
Click on the Variables node to create variables for the environment. Specify the variable name, type, description, value and also indicate whether or not the information should be flagged as sensitive.
Information regarding environments can be retrieved from the SSISDB, stored in the following four tables in SQL Server:
- internal.environments:Stores unique id, name and folder in which the environment is located.
- internal.environment_variables:Provides information about variables used in environments, such as name, data type and current value.
- internal.environment_references:Stores the relationship between projects and environments. It gives information on which project references which environment.
- internal.environment_permissions:Stores information about permissions for each environment.
These tables also provide the possibility to modify existing environments or create new ones.
After a project has been deployed to the SSIS catalog it is possible to configure it by right clicking the project or the package entry and selecting Configure...
This opens up the window to configure the deployed SSIS package. All parameters and connection managers can be configured on both project and package scope.
For parameters the following information is provided:
- Container - Parameter is either on project or package level.
- Name - Parameter name.
- Value - Current value of the parameter.
For connection managers the following information is provided:
- Connection String - The connection string of the connection manager.
- Initial Catalog and sever name- Values of server and initial catalog.
- Username and Password - For authentication purposes.
- RetainSameConnection - Specify if all tasks should use the same connection.
Under the References node it is possible to assign environments to the project, which can be used to configure the values of the different properties.
To assign the environment variables to parameters or connection manager properties click the ... button next to each value.
In the Set Parameter Value window choose between editing the value manually, using the default value from the package or assigning a variable from the environments.
For each parameter that has to be configurable, one variable needs to be created in the according environments.
If more than one environment is used within a project and each should configure the same parameters and connection managers then it is mandatory that the variable names are the same for all environments.
When development of the SSIS package and project has been completed we can deploy the project to the catalog that was created. When using the project deployment model, selecting deploy will immediately launch the Integration Services Deployment Wizard. In the wizard you can choose to deploy the current project or import an existing project from another server, for example you want to move your project from development to test server.
After this you select the server where you want to deploy to and also the location (folder) in the SSIS catalog.
It is important to note that you can only deploy the entire project and not individual packages within a project.
These days almost every company store data such as users, emails, roles, printers, etc. in what it is call a Directory Service, providing a centralized access to company-related data that can be use across all its systems. One of the most widely used solution is Active Directory from Microsoft, since its tightly integrated with Windows OS systems. The other one LDAP, which stands for Lightweight Directory Access Protocol, has a broad range of users too since the de facto standard in Linux-based systems.
Given the fierce competition and the ever-soaring demands of customers, small and medium-sized enterprises (SMEs) today need to invest in having BI capabilities to stay relevant and meet these demands. Yet, with scarce resources available, the alignment of business and IT is vital to ensure success, and new options for hosted BI solutions freeing up cash flow are attractive options for SMEs.
Download issue 4 of the Clariba Connection Newsletter and read Marc Haberland’s takeaway on the importance of Business Intelligence Competency Center (BICC). Also, don’t miss out on why you should upgrade to SAP BI BusinessObjects 4.1. Your peers are taking their BI capabilities to the next level by moving to SAP BusinessObjects BI 4.1. Join the list of clients and follow their path!