Knowledge Sharing

Current BI implementation require to deploy the cube in multiple location to make it available  locally in each location to improve the performance. Cube development happening in one location say X but since the company is having presence in around the world and so business users sits in different locations X, Y and Z. Internet connectivity between X, Y and Z are not good and accessing the cube deployed in X from Y and Z is very slow and it impact the end user productivity. To overcome this issue, cube has to be deployed in Y and Z locally and upload the connection in their local SharePoint as mentioned below.  Fig 1 Now let see how we can automate all these process from taking back up of the cube database to restore it in each location. Follow the below mentioned steps to automate back up and restore of cube database. - select the back up option by right click on the cube database to be backed up and generate the XMLA script after providing necessary proper

This is one common error that SSIS developers confronts while pulling data from CSV files.  There may be two issues related to this, one is due to size of the CSV connection file field is less compared to the original file and the second one is due to different code page.  I had one CSV file for which data extracted from Oracle database and here is the connection settings  Fig 1 Here is code page is 65001(UTF-8) and when I tried to run the package, I got the error. Fix for this error is simple, text qualifier for this CSV file is " and when I given quotes in the text qualifier in connection properties this error vanished.  Fig 2

It is very important for business users to understand the business names of the fields that are used inside the cube than the actual table field name. Using DMV queries we can pull most of the cube meta data but I failed to get the database source field used to generate the cube field. I wish Microsoft will take care this in future release. I proposed below logic to the customer to track the source field and the business logic if any so that it is easy to automate and provide the business users through MS-Excel. we can see description field inside dimension properties window as shown in Fig 1 which is a free text and it can accept very long text. So I urged the cube developers to provide all the source field name and the business logic used inside here so that this can expose to the MDX query. Fig 1 deploy the cube and query the metadata and see the description field. Fig 2 Now if we can access this details from SQL using open query, we can crea

My current assignment is implementing Microsoft BI solutions to the customer using SSAS cubes, SharePoint, Power view and Pivot tables and charts. I am very excited about this project because this project is considered as very critical for IT team to provide self service BI to the business users. Currently they are using Business Objects for their enterprise reporting and QlickView for the dashboard. Microsoft BI is going to replace these two biggies. Currently it is in design phase and need to consider lot of critical pieces of BI such as security, version controlling, data dictionary, error handling, Process etc. I will be providing all my challenges here that I will be facing in this project.

MDX is totally different from SQL queries. MDX engine thrown back the error when I have given the below query .Here [Color] and [Size] belongs to the same dimension Dim Product. Fig 1 This is very simple syntax issue. In MDX we should keep all the members of same dimension within the brackets ( ) as showed below. Fig 2

We know that Hadoop helps to store mass data, process and analyze the same very fast. It is not easy for non java developers to extract and analyze the data from Hadoop framework but with the development of Hive any non java database developers can easily do the data analysis quickly. Hive is developed in Facebook labs and the syntax is more similar to all other Structured Query Languages such as Oracle , SQL server, MySQL etc. Pig which I have explained in my previous post can consider as alternative to Hive. Pig was developed at Yahoo! about the same time Facebook was developing Hive. Hive is used for OLAP purpose than the OLTP whereas Pig is considered as ETL language for Hadoop. To illustrate the Hive syntax and use, I thought it is best to load the data from Adventureworks DW database. I followed below steps to load data from Adventureworks database to file and to Hadoop eco system. 1. Pull the records from required tables to xlsx files 2. Browse the csv file from Hadoo

PIG is a script used in Hadoop to fetch data and generate different varieties of result set. Here I would like to explain the basic use of PIG script. Lets consider one table called  census_education which contains census education data for each county.  Fig 1 Concept here is slightly different from normal SQL. Filtering, grouping etc are not doing in one step instead multiple steps. In the first statement LOAD program pulls the record from the table census_education which is highlighted in red and store the output result into a variable called a. USING keyword refer the table metadata from the HCatalog while pulling the records from the table. Second statement pulls only required data for analysis and store it in another variable b. similarly third statement group the records and store it in another variable c. Foreach iterate through the entire record in the output result of c and find the average for highly educated peoples for each city ( city is a dimension c