Map

Country: - Country Code: - Flag: Capital: - Currency: -

using mdx order purely based on measure

Wonder how to do an "ORDER by" purely based on a measure value! Well yes, it can simply be done using the ORDER function. 

Any idea what the result of this query would be?

SELECT       [Measures].[Reseller Sales Amount] ON COLUMNS,       ORDER       (             NONEMPTY             (                   {                         [Geography].[Country].[Country]*                         [Geography].[State-Province].[State-Province]                   },                   [Measures].[Reseller Sales Amount]             ),             [Measures].[Reseller Sales Amount],DESC         ) ON ROWS FROM       [Adventure Works]

Here is the result

I am sure this is not what you wanted...As you see they are not sorted yet.

To solve this all that you need to change in the above query is replace the DESC with BDESC (Break Hierarchy Desc)

SELECT       [Measures].[Reseller Sales Amount] ON COLUMNS,       ORDER       (             NONEMPTY             (                   {                         [Geography].[Country].[Country]*                         [Geography].[State-Province].[State-Province]                   },                   [Measures].[Reseller Sales Amount]             ),             [Measures].[Reseller Sales Amount],BDESC         ) ON ROWS FROM       [Adventure Works]

Here is result

Awards at Golden Palms Resort @ Aditi Day

Star Performer Award @ Aditi Technologies from CEO - Pradeep Rathinam

Customer Centric BI Team @ Aditi Technologies from CFO - Alok Bajpai

It was a great moment for me for the hard work put in for the last 1.5 years in the project that i currently work

SSIS File Custom Attributes

Today I was given a requirement to extract custom attributes of files and then update the database with these value.


Searching for a DLL's lead me to this URL:-Interop.DsoFile.Dll from Microsoft. Once you download and extract the files go to the Demo folder and use the Interop.dsofile.dll. The dsofile.dll in the main directory is a COM DLL and not .NET dll. 


Using Interop.Dsofile.Dll
To use it in SSIS-Script task, copy the file to "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\binn".
Add a script task and add this dll as reference from "Add Reference". Access the custom attributes using the code below

DSOFile.OleDocumentPropertiesClass _file; _file = new DSOFile.OleDocumentPropertiesClass();              _file.Open(@"C:\TestFile.doc", false, DSOFile.dsoFileOpenOptions.dsoOptionDefault); MessageBox.Show(_file.CustomProperties.Count.ToString()); MessageBox.Show(_file.CustomProperties[0].Name.ToString() + ":-" + _file.CustomProperties[0].get_Value().ToString());

Average of Dimension attribute value

Query to find out the average of a value that is an attribute in the dimension.

WITH MEMBER [Measures].[Average Cars Owned] As       Avg(             DESCENDANTS(                   [Customer].[Customer Geography].CURRENTMEMBER,                   [Customer].[Customer Geography].[Customer]             )AS Set1,             StrToValue(Set1.Current.Properties("Number of Cars Owned"))       )       SELECT     {             [Measures].[Average Cars Owned]     } ON COLUMNS,     DESCENDANTS(     [Customer].[Customer Geography].CURRENTMEMBER,     [Customer].[Customer Geography].[State-Province],SELF_AND_BEFORE)      ON ROWS FROM    [Adventure Works] where [Customer].[Country].&[Australia]

Problems in SSIS FTP Task

Have you faced any problems in SSIS FTP Task connectivity? Has the FTP Task stopped working in production and worked locally? Are you able to connect from browser/ftp client tools and not from SSIS?

I did face this problem and so did my colleagues. It took some effort and time in explaining my problem to infrastructure team so that they could help me to identify what could be wrong. I did think that it could be an issue with firewall but wanted to confirm it. It was a simple fix in the end and was interesting to know the reason for failure.

I thought of sharing this as many other people I know faced the same problem and this time I was happy to guide them and save a lot of precious time and effort.

I am sure you are facing the same problem and hence you have landed on this page. So let me first tell you the fix and then explain what is happening.

Solution to this problem is to set up the SSIS FTP Task connection object to connect in Passive mode.

How to do it? – Open the SSIS package and in the “connection managers” double click the FTP connection object. In Options - select “Use passive mode”. Now run the package and it should work without any errors.

The SSIS FTP Task uses the Active mode by default and in a Firewall environment setup this does not work. . The Browser FTP client uses the passive mode by default and hence it works.

What’s happening under the hood? Here are my notes.

o    FTP is purely a TCP based service

o    FTP uses 2 ports for communication:-

§  Command Port (usually Port 21)

§  Data Port (usually Port 20. Depending on the mode, it may or may not use 20)

o    FTP has 2 modes

§  Active mode

§  Passive mode

In Active mode

o    Client contacts FTP Server’s Command Port (21) from a random port (N>1023, say 1026). This is clients command port. It also sends Information on the port it will listen to (for data) (say 1029 command=PORT 1029)

o    FTP server sends an ACK back to port (1026). A connection is established.

o    FTP server initiates a connection (from port 20 its data port) to port 1029 (client. Earlier sent info).

o    FTP server now establishes a connection with this port and sends the data using this port.

Firewall Issue:

If a firewall is set up at client, then a request from FTP in active mode will be treated as some external source trying to connect to client and therefore will reject/block the request. This is because the client just sent the port info that will it will listen to and it did not really open a connection.

In Passive mode

·         Client contacts FTP Server’s Command Port (21) from a random port (N>1023, say 1026). This is clients command port. Client now issues PASV command instead of PORT command.

·         Server then replies with from port 21 to 1026 telling the client the port number it is listening to for data connection.

·         Client then initiates data connection from its data port (say 1029) to servers data port 1024 (instead of data port 20)

·         Server finally sends ACK to client’s data port 1029 from server’s data port 1024.

Reference article: http://slacksite.com/other/ftp.html (really well explained)

Comma seperated values (SQL)

This code generates comma separated values for an entity having multiple values. i.e. Multiple rows into a single row data value (comma seperated).

Found this code very useful for data validations in datawarehouse and thought of sharing this.

Here is the code snippet 

Create Table #TempData (EmployeeName Varchar(50), EmployeeNickName Varchar(50))

Insert Into #TempData Values('Vinuthan','VB') Insert Into #TempData Values('Vinuthan','Vinu') Insert Into #TempData Values('Vinuthan','Newton') Insert Into #TempData Values('Venus','Ven') Insert Into #TempData Values('Venus','Vens')

 Query to get comma seperated values

SELECT DISTINCT EmployeeName,   STUFF   (                 (                                 SELECT ',' + EmployeeNickName  FROM                                  #TempData a WHERE b.EmployeeName = a.EmployeeName  FOR XML PATH('')                 )                 ,1,1,''   ) EmployeeNickName  FROM  #TempData b

Result: