Showing posts from February, 2014

SSIS - Text was truncated or one or more characters had no match in the target code page

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

Microsoft BI implementation - Automated Meta Data dictionary for Business users for SSAS Cube

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

Microsoft BI implementation

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 - tuples or sets must use the same hierarchies in the function

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