Friday, March 30, 2012

Extracting QuickBooks data using SSIS Package...

To extract data from QuickBooks, I used QODBC driver.
You can download a trial version of it using below link

When we installed the QODBC driver, if ODBC entry is not available for it, we can add it as System Data Sources. For that go to the Control Panel --> Administrative Tools -->Data Sources(ODBC)

Then you will get the ODBC Data Source Administrator window. In that click on the System DSN and you will get a screen as below

In that click on the add button and select the QODBC Driver for QuickBooks as shown in the below figure.

Then you have to configure the ODBC connection. Initial set up window is as given below.

If you want to set a different company profile, click on the Browse button and select the qbw file as shown in the below figure.

Then you can use the created ODBC connection in SSIS package.
Create a new Integration Service Project and add a Data source. In the New Data Source window, select the Provider as Odbc Data Provider as in the below figure.

Then select the relevant ODBC data source name for the QuickBooks and if any authentication is required provide those too. Then click on the Test Connection button and verify the connection is possible.

Then add a new package to the project and add a Data Flow task. Then add a ADO NET Source and provide the created data source as the ADO .NET connection manager. If you created the connection correctly, you will be able to select the required table as shown in the figure.

In this example I just added a DataReader Destination component to view the data using a Data Viewer.

Thursday, March 29, 2012

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.

SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))

Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.

Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gives an error since it only consider the first word before the space.
As an example if you send the branch value as Head Office, it only take the Head part. Therefore you have to pass the UNIQUENAME.

As an example you can take the data set for the Branch using below query.

WITH MEMBER [Measures].[ParameterCaption] AS [DimBranch].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [IDimBranch].CURRENTMEMBER.UNIQUENAME
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , [DimBranch].[Branch].ALLMEMBERS ON ROWS FROM [Sales]

Then you can use the ParameterCaption as the Label field and ParameterValue as the Value field for the Branch parameter.

Wednesday, March 28, 2012

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.
In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.


Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

Thursday, March 22, 2012

FROM clause have the same exposed names...

When I tried to get the matching item records from two databases, I got the below error.

The objects "Database1.dbo.Sales" and "Databse2.dbo.Sales" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

The query used was
SELECT DISTINCT Database1.dbo.Sales.ItemName FROM Database1.dbo.Sales INNER JOIN Databse2.dbo.Sales ON Databse1.dbo.Sales.Id=Databse2.dbo.Sales.Id

It is because that the table name is same in those two databases. To avoid it we have use aliases.
The query is shown below

SELECT DISTINCT Table1.ItemName FROM Databse1.dbo.Sales AS Table1 INNER JOIN Databse2.dbo.Sales ON Table1.Id=Databse2.dbo.Sales.Id

Databse1.dbo.Sales AS Table1 will fix the error.