Retrieve Column Names, Data Type from a table

A handy SQL query to retrieve column names, data type, and other info from a table.

SELECT

COLUMN_NAME,DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH,

NUMERIC_PRECISION,

DATETIME_PRECISION,

IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='tablename'

Using Microsoft SSIS to access MySQL Server's data

This document will show you the steps in a situation when it is necessary to retrieve data from MySQL server into a Microsoft based data warehouse using SSIS, and viewing table schema using Microsoft SQL Management Studio(SMS).

From a Microsoft SQL Server it will access the data in MySQL server through a ODBC connection. A User DSN need to be created for SSIS ETL, and a System DSN need to be created for SMS.

In order to create MySQL DSN it is necessary to get MySQL ODBC drivers.

1. Download MySQL ODBC driver. Go to MySQL website, click on Download. Look for ODBC Connector.
http://dev.mysql.com/downloads/connector/odbc/5.2.html
Select the OS you have then download the client.
Once download complete. Double click on install package file to install it.

2. Create User DSN. After the MySQL ODBC driver installed. Go to Control Panel, Administrative Tools, ODBC Data Source Administrator. On User DSN tab, click Add..
Select MySQL ODBC 5.2 driver
An MySQL ODBC connection window opened. Enter the server name, database, user account and password to set it up.
Give a name of the User DSN connection.
You can the same steps to create System DSN as well.

3. Once a MySQL DSN is created it can be applied on SSIS package for ETL. Using Visual Studio SSIS package, in a Data Flow task, create ODBC source.
In SSIS Toolbox, under Other Sources, drag an ODBC Source into a developer window.
Right click on Edit, on the ODBC Connection manager field, click on New.. button
Click on New.. button again. on the Connection Manager window. click on the drop down box of "Use user or system data source name:" then search and select the DSN you created on step 2.
input user name and password, click Test Connection then click OK.
After that, tables in  the MySQL database is available for ETL by either select a table, view or write a SQL query to retrieve data.

Multiple Joins/Multiple Aggregations in Microsoft SSAS Tabular Model.

In Microsoft SSAS Tabular Model you can make multiple joins between fact and dimensions. The first join is shown as solid line. Any additional joins are shown as dash lines.

However in SQL Server 2012 Tabular Model is using only the first join for value aggregations. It is OK when the model in a simple star schema. However it will not be enough in conformed dimension.

For example: A fact order table has date_submitted and date_closed. both values joined to a Time Dimension table.
The goal is to aggregate data to report weekly, monthly aggregation based on date_submitted. But when attempt to count data based on date_closed the result are incorrect due to the tabular mode is using the first join only, but not the second join.

The solution is that in SSAS Tabular Model you can specify a aggregated value to use the second join. use the USERALTIONSHIP() expression to specify the join after the CALCULATE value.

Count of Date Closed:=CALCULATE(COUNTA('FactTableName'[ColumnName]),USERELATIONSHIP('FactTableName'[ColumnName],'DimensionTableName'[PK_Dim]))

      

Failed to create Timeline on Excel 2013 from SSAS 2012 Tabular Model: "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

Excel 2013 Pivot Table comes with Timeline feature which provide a visual date slicer to display data in a date range specified by users.



After a SSAS 2012 Tabular Model was built and deployed, which included a time dimension table, when you attempt to create a Timline but Excel failed the operation with the following error message:  "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

 

Although a time dimension was built and join within SSAS Tabular Model, the dimension table still need to be set as Date table.
1. Open the SSAS Tabular Model  in Visual Studio Development mode
2. Select the time dimension table, go to Table from menu bar.
3. Select Date, select mark as date table.

 
4. need to specify a primary key of the time table. then OK
5. Process, then redeploy the Tabular Model
6. Test it from Excel 2013 by create a Timeline.

 
For detail to how to multiselect dates in Excel 2013, please check out this web site:
http://thomasivarssonmalmo.wordpress.com/2013/03/03/multiselecting-dates-in-excel-2013-on-top-of-ssasbism-multidimensional/
End

  

Find Server Name, Database name from Tables

When querying data from multiple database across multiple server, a simple two lines of SQL code can tell where the data comes from 

SELECT
@@servername,
DB_NAME() as 'Database name'

FROM dbo.tablename

Microsoft Visual Studio 2010 Tools Box Empty and Recovery

It has been an known issue in the middle of debugging a SSIS package might cause Visual Studio to reset its Tools Box into empty.

It looks like you could right click the area and drag items back to the toolbox area. However most of the options are pointers and they are grey out.

You need to do something in order to cause Visual Studio to reset/rebuild the tool box into its default state. To do so you need to delete the backup entries files AND remove cache registry keys.

1. Close and exit Visual Studio 2010, using windows explorer go to "c:\Users\<your_user_name>\AppData\Local\Microsoft\VisualStudio\10.0" 

2. delete all *.TBD files

3. Click "Start", "Run" type REGEDIT to open registry editor. go to "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\ToolboxControlsInstaller_AssemblyFoldersExCache" 
and "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\ToolboxControlsInstallerCache"

4. Remove all sub keys as highlighted.

5. Open Visual Studio. open a project and wait for the Toolbox recover itself. then ready to use.

End