Use Sybase SQL Anywhere as data source for PowerPivot

To use Sybase SQL Anywhere as the Power Pivot data source, you need to use ODBC. It is not intuitive to set it up.  After encountered several errors, I am able to load the data into PowerPivot to analyze it now.

Here is the list of steps:

• Install SQL Anywhere Client. You may download it from Sybase we: http://www.sybase.com/detail?id=1087327. If you have 32 bits Office product, you need to install 32 bit client.
• Open the Sybase Central App and create a new connection to the Sybase Host and the database you want to load the data from.
• After you test the connection, click on the Save ODBC Data Source menu on the Tools button.

• When the Save as ODBC Data Source window open, type in a name for the ODBC and click on the Save button.

• You could now open the ODBC data source admin to verify it been created.  Please note that if you install 32 bit SQL Anywhere on a 64 bit windows machine, then you will need to use the 32 bit ODBC to view/configure it. You could find it in C:\Windows\System32\odbcad32.exe.

• In Excel, open the PowerPivot, click on the Get External Data from Other Sources on the Home page.
• Select Others(OLEDB/ODBC) from the list to specify the Connection String

• You could type in the following connection string in the Table Import Wizard window:
  Provider=SAOLEDB.12;Data Source=DataSourceName; Persist Security Info=False
   

• Or Click on the Build button to open the Data Link Properties window to select the ODBC just created.

Now, you should be able to load the data from SQL Anywhere data source into PowerPivot workbook.

Create SSRS Report using DAX

 

We could query BISM Tabular model using either MDX or DAX. People are very familiar with how to use MDX in SSRS.  Using DAX in SSRS is totally different from using MDX.  Here is an example to create SSRS report using DAX language:

List of Steps:

1. Open SQL Server Data Tools to create a SSRS report.

2. Add an existed BISM Tabular Model as the Shared data source.

3. Write the DAX in the SSMS or DAX Studio first. The DAX Studio is an Excel Add-Ins that you could download from the CodePlex.

EVALUATE

SUMMARIZE

    (    

        CALCULATETABLE( 'Internet Sales'

        ,'Product Category'[Product Category Name] = "Bikes"       

    ) ,

 'Date'[Calendar Year],

 'Product Category'[Product Category Name],               

  "Total Sales", 'Internet Sales'[Internet Total Sales],               

  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              

  "Total Margin", 'Internet Sales'[Internet Total Margin]           

 )

 

4. Create SSRS dataset

• Add a new dataset and connect to the BISM Tabular Model
  
• Open Query Designer
  
• Click on the Command Type DMX button on the toolbar to switch to the DMX query designer.

• Click on the Query/Design Mode button to switch to the Query mode.

• Paste or type your DAX query into the Query window and then click on OK button.
  
• Click on the Fields tab to fix/rename the field name to make it more readable. The field name generated by the query designer will not lineup with the column name you have in the DAX query.

 

• Now, you could add those fields to the report.

5. Add parameter

• Go to dataset query, open the query designer.
  
• Change the DAX query by replacing the filer part of Query with parameter name and click on the Parameter Button to open the Query Parameters window

• Add Parameter and set the default value, then click OK to exit out the designer
  
• On the report data window, you will find a parameter been created with default value that you typed in.

6. Create parameter dataset

• There is no parameter query generated by the tool. You need to create it if you allow user to pick from the drop down list.
  
• Write a DAX query with VALUES() function using the same steps before to create a dataset that will contain the list of parameter values.

EVALUATE 
values('Product Category'[Product Category Name])
ORDER By 'Product Category'[Product Category Name]

• Associate this dataset to the report parameter

• Now your SSRS report should work with single value selection.

Report Parameter Allows Multiple Values

If you allow multiple values for the report parameter, you could not use filter with equal sign (“ = ”) in the DAX query.  You could use PATHCONTAINS() function in the filter part of query to filter the result set.

PATHCONTAINS(<path>, <item>)

Since the multi select parameter string pass in will contain extra special characters that could not be used for the Path() function, you need to use Substitute() function to replace them.

Here is the complete DAX query for the report.

EVALUATE

SUMMARIZE(

    CALCULATETABLE( 'Internet Sales'

            ,PATHCONTAINS(

                    substitute( 

                        substitute( 

                            substitute( 

                                    @CategoryName

                              , "{ ", "") 

                        , " }", "") 

                    , ",", "|") 

        ,'Product Category'[Product Category Name] ) 

),                

 'Date'[Calendar Year],

 'Product Category'[Product Category Name],               

  "Total Sales", 'Internet Sales'[Internet Total Sales],               

  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              

  "Total Margin", 'Internet Sales'[Internet Total Margin]           

 )

 

After you update your query, the SSRS report will work for multiple selections from the user now.

 

 

Reference:

http://daxstudio.codeplex.com/

http://msdn.microsoft.com/en-us/library/gg492182.aspx

SQL Dump After Either Upgrade or Downgrade the PowerPivot

Recently I upgraded the PowerPivot Excel add on to the version 2 to work with new DAX. I experienced excel crush when I opened the workbook.  The error message showed as:

SQLDUMPER.EXE
Unable to open file \\?\Program Files\Microsoft Analysis Services\AS OLEDB\10\FlightRecorderCurrent.trac error 2

I tried to uninstall and re-install the program, but it didn’t work.  I have to manually delete the directory C:\Program Files\Microsoft Analysis Services\AS OLEDB\ and repair the PowerPivot, then it works.

Same situation happened again when I downgrade my PowerPivot back to version 1 to be compatible with my client’s environment.  Once I manually remove the C:\Program Files\Microsoft Analysis Services\AS OLEDB\ directory and then repair the program, it works again.

Publish a BISM Tabular Model Database Connection on SharePoint 2010

Once you built a BISM Tabular Model project and deployed to SSAS,  you may want to publish it on the SharePoint 2010. A BISM connection allows business end users to access the underline model on SharePoint with addtional security control. It could also utilize the SharePoint quick launch commands to open the Excel Workbook or Power View Report.  For more information about how to create a BISM Tubular Model project, see my previous blog entry Create a SSAS BISM Tabular Model Project.

 

Add the BI Semantic Model Connection Content Type to the SharePoint Library

• Go to the SharePoint Library site that will host the connection file, click the Library in the Library Tools.
• Click the Library Settings
• In the General Settings section, select the Advanced settings

• In the Content Types, Select the Allow management of content types to be Yes, then click OK
• In the Content Types section, Click on the Add from existing site content types
• Add the BI semantic Model Connection

• After click on the OK button, you can see the BI Semantic Model Connection added in the Content type table

 

Set Up Permissions

• Grant the Tubular Model Analysis Service administrative permissions to the SharePoint Service Account.
• Grant the user who is going to use the connection with the Read permission on the Tabular Model database
  
  • Add a Role with Read permission
  • Adding the user to the Role

 

Create the Connection File on SharePoint

• Go to the library page, click on the Documents in the Library Tools.
• Click on the down arrow on the New Document and select the BI semantic Model Connection.

• On the New BI Semantic Model Connection page, specify the server name and database name and then Click OK.

• On the library page, you should be able to see the new connection file.

Use the BISM Connection in SharePoint

Now you may use Excel or Power View to consume the BISM connection file as the data source. 

• If you create the file in PowerPivot Gallery, you could click on either the Open New Excel Workbook or Create Power View Report link on the right upper corner to open the application.

• If you create the file in the Shared Documents page, you could click on the Down Arrow on the File Name to launch either the Excel or the Power View Report.

Additional Reference

http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx
http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx

SSIS Error Loading Excel Data with Office 2010 installed

Recently I am setting up a virtual machine to play with the new SQL Server “Denali”. The latest Office 2010 applications and Visual Studio 2010 also have been installed on it.  I open a SSIS project and create an Excel Connection Manager with default version set to Microsoft Excel 2007 to load a excel spreadsheet that was saved as *.xlsx format .
When I  use this newly created Excel Connection Manager as the Data Source in my data flow, I got the following error after click the Preview button:

The SSIS error log has following:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
People will normally assume that the machine should contains the latest data provider (Office 2007 provider(12)), when the Office 2010 Excel/Access application installed on the machine. Unfortunately, it is not the case.

There are two ways to resolve this issue:

1. Install the latest office driver form the following link: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
2. Save the Excel spreadsheet to be Microsoft Excel 97-2003 (*.xls) format to load the data in.