Technical: Microsoft – Information Integration Server (IIS) – Version 8 – Hardening\Securing

Technical: Microsoft – Information Integration Server (IIS) – Version 8 – Hardening\Securing

 

Background

Still morose with the IIS web site hijack that I covered in a previous post (http://danieladeniji.wordpress.com/2014/04/12/technical-microsoft-dnsiis-attack-2014-04-12/).

Starting to take baby steps to harden IIS a bit.

 

Areas we will cover:

  • Web Server Extensions
  • Host Headers
  • Application Pools
  • Request Filtering – HTTP Verbs
  • Audit

 

Web Server Extensions

On the web server, here are the steps to review and adjust “ISAPI and CGI Restrictions”:

  • Launch IIS Manager
  • Select the Server
  • On the server level, make sure that you are viewing the “Features View”
  • Select the “ISAPI and CGI Restrictions” applet

Here is what our screen looks like:

ISAPIandCGIRestrictions

 

 

Interpretation

As we are not familiar with the hcap\hcapext.dll module, not so sure what to make of it, and so we changed his Restriction from “Allowed” to “Not Allowed”.

 

 

Host Headers

On the web server, here are the steps to effect Host Header

  • Launch IIS Manager
  • Select the Server
  • Access the web site
  • At the web site level, transverse to the “Access” panel
  • In the “Edit Site” branch, click the “Bindings” branch
  • Review listed bindings and make sure that you have specific URLs listed; including the one for localhost, if you will be browsing from the local machine

 


SiteBindings-AddSiteBinding

 

This helps shield you from fly by night visitors; i.e those that are just performing IP Sweeps.

 

 

Application Pools

Review registered Application Pools and make sure that you ‘re using Active Directory accounts or local accounts with the most basic permission set.

On the web server, here are the steps to effect Host Header

  • Launch IIS Manager
  • Select the Server
  • Access the “Application Pools”
  • Review each Application pool, service account
  • Select the Application Pool, right click on your selection, and from the drop-down menu, choose “Advanced Settings” entry
  • In the “Advanced Settings” window, navigate to the Identity entry and note the Account that you ‘re running under

 

ApplicationPool-AdvancedSettings-Identity

 

Please review the Application Pool user account on your Web Server, Active Directory, and Database, etc.

And, later be able to audit the account in terms of it activities.

 

Application – Request Filtering – HTTP Verbs

Aforementioned, we are quite concerned that some of our web site files are getting over-written.

To address, we will enable “Request Filtering” and only allow the following verbs – Get, Post.

To effect, we will slightly modify the Application’s web.config file:

 

Here is the snippet of code that filters out unlisted verbs; and adds\enables the two verbs that we want to allow – GET and POST.

<configuration>

	 <system.webServer>
	  <security>
	   <requestFiltering>
		<verbs
		   allowUnlisted="false"
		   >
		 <add verb="GET" allowed="true" />
		 <add verb="POST" allowed="true" />		 
		</verbs>
	   </requestFiltering>
	  </security>
	 </system.webServer>
	   
</configuration>


 

To view the settings:

  • Launch IIS Manager
  • Access the web site
  • Ensure that the “Features Views” is active
  • Within the “IIS” sub-section, choose “Request Filtering”
  • In the “Request Filtering” window, select “HTTP Verbs” tab

  Request Filtering:

RequestFiltering--HTTPVerbs

 

 

Audit

Audit – NTFS

Our primary hope at this time is to be better prepared to audit NTFS changes that is shamefully causing our website contents to be over-written.

Thankfully, Microsoft has built NTFS to be capably audit-able.

Steps:

There are two steps.  The first one is to select the object we will like to audit and specify which actions we will like to audit.

Steps – Specify actions to audit

  • Launch Windows Explorer
  • Access Folder that contains web site folders & files
  • Right click on your selection, and from the drop-down menu, choose “Properties” entry
  • In the “Properties” window, access the security tab, and click on the “Advanced” button
  • In the “Advanced Security Settings” window, access the “Auditing” tab window
  • Review the accounts that are currently being audited
  • In our case, we will be adding “Domain  Users” to our list
  • And, as we are not interested in execute nor read activities, but changes to the actual file, please click on “Show advanced permissions” button

Here are entries that we selected:

NTFS-Permissions-AuditingFolder

 

And, here is the completed list:

NTFS-Permissions-AuditingFolder-AdvancedSettings

 

 

Steps – Specify actions to audit

  • Launch Administrative Tools \ Local Security Settings
  • In the left panel, access Security Settings \ Local Policies\ Audit Policy
  • In the right panel, select “Audit object access” and double-click on your selection
  • In the “Audit object access properties” window, select “Success” and “Failure” — In many cases, it is OK to just audit failures — that is you want to know who is try gain access, but failure.  But, as I said, in our case, whomever or whoever is trying to gain access is successfully doing so
  • Click “OK” to enforce your changes

 

 

AuditPolicy-AuditObjectAccess-SuccessAndFailure

 

Completed:

Please pay close attention to the “Security Setting” column, as in many shops changes can not be effected, due to “Group Policy” settings.

LocalSecurityPolicy-AuditPolicy-Completed

 

 

Program Features

Review Program Features and see whether new applications have been installed.  In our case, we wanted to source hcapexet.dll.

Especially, as we have a file creation date.

ProgramAndFeatures

 

Good on Microsoft for augmenting “Add\Remove Programs” with an order-able “Installed On” column.   This way, we can correlate our initial problem date with Application Install days.

 

Conclusion

Who gets picked on ?  The security breach might very well be random in nature.  I know we sometimes feel picked on.  But, in the world wide web, there are no strangers and little anonymity.  We are all just IP friends and neighbors.

It seems the virus slash worm is targeting familiar folders; folders with names such as CascadingSyleSheets, Confide, DynamicData, and Northwind.

And, files with names such as default.asp and index.php.

Pasted below is a screen shot that shows infected folders and files.

 

familarFolders

The infected folders are:

  • App_Data
  • bin
  • Content
  • Controllers
  • Models
  • obj
  • Properties
  • Scripts
  • Views

And, the files are:

  • default.asp
  • index.php

The infection occurred on 4/7/2014 and the folders and files bear that DateModified timestamp .  Please keep in mind that even when we replace infected files with good ones, they are getting re-infected and the DateModified have the more recent dates.

 

 

References

References – General

References – Web Server Extensions

References – Web Server Extensions – IIS7

 

References - Host Headers

 

References - Request Filtering

 

Posted in Internet Information Server (IIS), Microsoft, Security, Technical | Tagged , | Leave a comment

Technical: Microsoft DNS/IIS Attack (2014-04-12)

Technical: Microsoft DNS/IIS Attack (2014-04-12)

Background

One area that I do not like to cover in a public forum is security. But, in the last few days we have started noticing a sustained attack on a couple of our publicly facing Windows boxes.

Attack Surface Area

Microsoft – DNS

Using SysInternals \ TCP View, we are are able to see repeated DNS connections.


DNSAttack

 

 

Attacked Area

Microsoft – IIS

Here is what one of our web sites look like post attacks:

 

HolyBibleSearch

 

Security Review

Microsoft – Event Viewer

Here is an attack from 173.194.64.70 targeting HTTPS/Port 443

Category-12809-EventID-5152__065034PM

 

Here is an attack from 173.194.64.111 targeting HTTPS/Port 443

Category-12809-EventID-5152__065034PM_v2

 

Here is an attack from 173.194.64.114 targeting HTTPS/Port 443

 

Category-12809-EventID-5152__065034PM_v3

Here is an attack from 68.87.64.106 targeting DNS/Port 53

Category-12809-EventID-5152__065034PM_v3__68-87-64-196

 

 

What to do

  • Consider perimeter Firewall
  • On individual hosts, make sure that you ‘re running Microsoft Windows Firewall
  • On individual hosts, make sure that you the DNS Services is only running on servers that need it
  • On individual hosts,  configure Microsoft IIS with Security best practices
  • On individual hosts,  make sure that you ‘re running a good, reputable AntiVirus

 

Anti-Virus

Symantec / ANt-Virus

Buzz99 has a good and freshlg updated blog post @

Norton Antivirus 2014 Product Key Free 6 Months Subscription
http://www.buzz99.com/norton-antivirus-2014-product-key-free-6-months-subscription/#sthash.K5Xl2TmT.dpuf

Courtesy of same blog post here is the URL to the product:

 http://liveupdate.symantecliveupdate.com/upgrade/NSS/SymCCIS/Production/IS/nav/MUI/fbook/Setup.exe

Even though it warns that product has not / yet to be tested on MS Windows 2012, it installs and works on it.

 

 

Posted in DNS, Internet Information Server (IIS), Microsoft, MS Windows, Technical, Win OS | Tagged , , , | 1 Comment

Technical: Linux (Cent-OS) – CD Drive – Aftermath of “Safely Remove Hardware”

Technical: Linux (Cent-OS) – CD Drive – Aftermath of “Safely Remove Hardware”

Background

It all started innocently enough.  There are a couple of machines that I wanted to play with.  One has a trial version of MS Windows 7, and believe me it works well and reliably.  And, the other one will not even come up!

I am trying to get my Linux Lab environment back up.  Who isn’t these days?

Well once Cent OS is installed, like every other Windows aficionados I started playing around with the GUI and pressed on a wrong button.

 

Introduction

Which wrong button, you might ask?  Well, the one that states “Safely Remove Drive“.

SafelyRemoveHardware_2

And, so even though my sure and reliable Memorex DVD Double Layer is still attached, it is no longer visible, as confirmed in the screen shot below:

ComputerWindowPostsSafelyRemoveHardware

 

 

So how do we get the CD/DVD Back?

List CD / DVD

I tried listing devices, grepping on cdrom

Command:

ls -la /dev | grep cdrom

Output:

listCDAndDVD

From the output above, our CDs and DVDs are:

a) /dev/cdrom1
b) /dev/sr0

 

 

Eject CD

Ejected the CD, hoping placing a new one in will result in the OS now seeing it.

Syntax:

sudo eject /dev/<filename>

Command:

sudo eject /dev/cdrom1

 

List Attached Hardware Devices

 

Use dmesg

Use dmesg to list hardware devices that Kernel recognizes.

In this case, list SCSI related info

Syntax:

dmesg | grep -i SCSI

Output:

dmesg--grep--SCSI

 

In this case, list usb related info

Syntax:

dmesg | grep -i usb

Output:

dmesg--usb

 

Hoping to remount it, using mount … But when I tried to do so, did not get anywhere.

Command:


mkdir /mnt/DVD
mount -r -t iso9660 /dev/sr1 /mnt/DVD

Error Message:


mount: special device /dev/sr1 does not exist

 

 

Reset USB Devices

 

The one thing that worked, is the one posted by Tamás Tapsonyi

How do I reset a USB Device without unplugging it in linux

http://superuser.com/questions/141908/how-do-i-reset-an-usb-device-without-unplugging-it-in-linux



# Original by Tamás Tapsonyi
# Slightly modified by Daniel Adeniji
# resetting USB ports
# http://askubuntu.com/questions/645/how-do-you-reset-a-usb-device-from-the-command-line/290519#290519
#http://stackoverflow.com/questions/59838/how-to-check-if-a-directory-exists-in-a-shell-script
# if Folder exists /sys/bus/pci/drivers/ehci_hcd/
# resetting USB2 ports
if [ -d "/sys/bus/pci/drivers/ehci_hcd/" ]; then
   echo "Resetting USB2 Ports ..."
   for i in $(ls /sys/bus/pci/drivers/ehci_hcd/|grep :)
	 do echo $i >/sys/bus/pci/drivers/ehci_hcd/unbind
        echo $i >/sys/bus/pci/drivers/ehci_hcd/bind
    done
    echo "Completed resetting USB2 Ports"
else
   echo "Skipped USB2 Ports"
fi


# if Folder exists /sys/bus/pci/drivers/xhci_hcd/
if [ -d "/sys/bus/pci/drivers/xhci_hcd/" ]; then
   echo "Resetting USB3 Ports ..."
   for i in $(ls /sys/bus/pci/drivers/xhci_hcd/|grep :)
     do echo $i >/sys/bus/pci/drivers/xhci_hcd/unbind
     echo $i >/sys/bus/pci/drivers/xhci_hcd/bind
   done
    echo "Completed resetting USB3 Ports"
else
   echo "Skipped USB3 Ports"
fi


 

Validation

Our CD/DVD now shows up on the GUI and we can also query /var/log/dmesg and see that it is registered as sr1

Command:


cat /var/log/dmesg | egrep '(CD|DVD)'

 

Output:

var-log-dmesg --egrep cd-dvd

 

Conclusion

Of course, you can always unplug/re-plug in the USB Device or restart the device if it has its own power switch.

 

 

References

References – CD / DVD

 

References – CD/DVD Eject

 

References – dmesg

References – USB Device Reset

 

References – VLC

 

 

 

 

Posted in CentOS, Linux, Redhat, Technical, USB | Tagged | Leave a comment

Technology – Microsoft – SQL Server – Analysis Services – MDX – Member References

Technology – Microsoft – SQL Server – Analysis Services – MDX – Member References

Background

Reading through Sample MDX Statements and confused about the role of the ampersand (&).

And, so googled for help, but nothing quite came up.  And, so ventured down the more steady path of reading through Books.

 

Introduction

Members can be referenced via a variety of ways.  The basic pathway been through the Dimension name, its Hierarchy, and the attribute value.

In the case of the attribute value one can use the name value or the key value.

Keep in mind that each attribute has a couple of properties that are important.

Attribute Properties

The Attribute property definition affects how an Attribute is displayed and referenceable.

Column Definition Sample Data
Type User Defined Data type Calendar – Year
Calendar – Quarter
Calendar – Month
Calendar – Week
Calendar – Day
KeyColumns References the combination columns that make the Column values unique Year, Month
NameColumn Descriptive Name that will show up when an attribute such as Time.Month_Name is chosen then the contents of the Time.Month_Name such as January, March will show up
ValueColumn Underlying Value when an attribute such as Time.Month is chosen then the contents of the Time.Month such as 01, 03 will show up

 

 

 

Attribute Definition Values

Here is a quick snapshot of how our Attribute Definitions are specified:

Column Type NameColumn ValueColumn
Date Day Time.Date_Name (WChar) Time.PK_Date (Date)
DateAsYYYYMMDD Day Time.DateAsYYYYMMDD
(WChar)
Date_Name
Day_Of_Month DayOfMonth Time.Day_Of_Month_Name (WChar) Time.Day_Of_Month (Integer)
Day_Of_Year DayOfYear Time.Day_Of_Year_Name (WChar) Time.Day_Of_Year (WChar)
 Month Months Time.Month_Name (WChar) Time.Month (WChar)
 Year Years Time.Year_Name (WChar) Time.Year_Name (WChar)

 

Attribute Hierarchies

The Hierarchies defined on a Dimension also plays a role in how Dimension Attributes are “spelled” out.

AttributeHierarchies

 

Data

Data Contents

Here is screenshot that shows the data generated by the Dimension Wizard when we chose to use it to create a Time Dimension.

 

DataContents

 

 

 

Queries

Here are some sample code – Transact SQL and MDX Statements that shows subtle differences between Name and Key value references.

Queries – Get data for March – Third Month of the Year

Transact SQL


select
	  tblTime.[Month_Of_Year]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Month_Of_Year] = 3
group by
	  tblTime.[Month_Of_Year]



 

MDX – Using Name Value



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].[Month 3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]


 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Month Of Year].&[3]
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]


 

 

Queries – Get data for 2014

Transact SQL


select
		  tblTime.[Year]
		, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
		inner join [dbo].[SalesOrderHeader] tblSales
			on tblTime.DateAsYYYYMMDD 
                             = tblSales.OrderDateAsYYYYMMDD
where tblTime.[Year] = '2014-01-01 00:00:00.000'
group by
	  tblTime.[Year]


 

MDX – Using Name Value



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	       (
		  [Time].[Year].[Calendar 2014]
	      )

	}  ON ROWS

from [MultidimensionalProject_Time]


 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		[Time].[Year].&[2014-01-01T00:00:00] 
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]


Queries – Get data for March 27th

Get data for March 27th (Dec 86) and Dec 25th (Day of Year = 359)

Transact SQL


select
  	  datepart(dy, tblTime.[PK_Date]) as [dayofYear]
	, sum(tblSales.[SubTotal]) as SubTotal
from   dbo.[Time] tblTime
	  inner join [dbo].[SalesOrderHeader] tblSales
		on tblTime.DateAsYYYYMMDD = tblSales.OrderDateAsYYYYMMDD
where (
	     (tblTime.[Day_Of_Year] = 86) -- March 31st 
	  or (tblTime.[Day_Of_Year] = 359) --- December 25th
      )
group by
	  datepart(dy, tblTime.[PK_Date])


 

MDX – Using Name Value



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (

		{
			  ([Time].[Day Of Year].[Day 95])
		 	, ([Time].[Day Of Year].[Day 359])
		}

	   )

	}  ON ROWS

from [MultidimensionalProject_Time]

 

 

MDX – Using Key Value

Keep an eye on the ampersand that precedes the bracket around our value column.



select
       {
		(
		    [Measures].[Sub Total]
		)
	}
        ON COLUMNS

	,
	NON EMPTY 
	{
	   (
		{
			  ([Time].[Day Of Year].&[95])
		 	, ([Time].[Day Of Year].&[359])
		}
	   )

	}  ON ROWS

from [MultidimensionalProject_Time]


 

 

Listening To

D’banj – Bother You
(Footage taken from the feature film “Half of a Yellow Sun” – Directed by Biyi Bandele)
Written by Oyebanjo Daniel Oladapo and Lionel Richie
https://www.youtube.com/watch?v=RKCk6PtNPdw

 

 

 

 

Posted in MDX, Microsoft, MS SQL Server Analysis Services (SSAS) | Tagged , | Leave a comment

Technical: Microsoft – SQL Server – Analysis Services – MDX – Function – LastPeriods

 

Technical: Microsoft – SQL Server – Analysis Services – MDX – Function – LastPeriods

 

Background

Needing to do some Date Arithmetic with MDX.  The basic premise being the need to fetch data for N days relative to the current date.

 

 

Getting Data for last 7 days using LastPeriods



/*
   Example:

https://sqlserverbiblog.wordpress.com/tag/mdx-queries/

*/
select 
		{
			[Measures].[Total Due]
		} on columns

	, NON EMPTY LASTPERIODS
		( 
			7
			, [Time].[Date As YYYYMMDD].&[20140406] 
		) on rows


from [MultidimensionalProject_Time]



 

Screenshot:

NonEmptyLastPeriods

 

 

Getting Data for current year using LastPeriods

In the sample below, we create a couple of Calculated members (TodayDate, DateYYYYMMDD as an Integer value of YearMonthDay, and Day of Year as DayofYear).

 



WITH  
MEMBER [Measures].[TodayDate] AS NOW()
MEMBER [Measures].[DateYYYYMMDD] AS format(NOW(), "yyyyMMdd")
MEMBER [Measures].[DayofYear] AS StrToValue(datepart("y",Now()),CONSTRAINED)
select 
          NON EMPTY 
		(
			{
				 ([Measures].[Total Due])
      	                    -- ,([Measures].[DateYYYYMMDD]) 
     	      	            -- ,([Measures].[DayofYear]) 
			}

		) on columns

	, NON EMPTY LASTPERIODS
		( 
			  [Measures].[DayofYear]
			, StrToMember(
					"[Time].[Date As YYYYMMDD].&["
					+ [Measures].[DateYYYYMMDD]
            	                        +"]"		
				    )
		) on rows


from [MultidimensionalProject_Time]


 

Screenshot:

NonEmptyLastPeriodsForCurrentYear

 

 

Listening

Listening to:

Ed Sheeran – The A Team [Official Music Video] https://www.youtube.com/watch?v=UAWcs5H-qgQ

 

References

References – Date Sample

 

References – MDX – Function – LastPeriod

 

References – MDX – Function - StrToValue

 

References – Date Function References

 

 

Posted in MDX, Microsoft, MS SQL Server, MS SQL Server Analysis Services (SSAS), Technical | Tagged , , | Leave a comment

Technical: Microsoft – SQL Server – v2008/R2 – Business Intelligence Studio – Cube Browsing – Error dragging measures\attributes to view pane

Technical: Microsoft – SQL Server – v2008/R2 – Business Intelligence Studio – Cube Browsing – Error dragging measures\attributes to the View Pane

Background

When Browsing Cube, one of the errors one might encounter as you add measures and attributes to the pane is one that reads:


Retrieving the COM class factory for component with CLSID 
{C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. 
(Microsoft Visual Studio)

Full Error Message

Error Text


===================================

Retrieving the COM class factory for component with CLSID {C966837E-970C-4F4F-A91D-D81E21ABBBDB} failed due to the following error: 80040154. (Microsoft Visual Studio)

------------------------------
Program Location:

   at Microsoft.AnalysisServices.Controls.MiscUtilities.GetMixedDataObjectForFilterAndPivotTable(DataObject dataObjectForFilter, PivotTableDataObject pivotTableDataObject)
   at Microsoft.AnalysisServices.Controls.PivotTableBoundMetadataBrowser.GetDataObject(TreeNode node)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.OnItemDrag(ItemDragEventArgs e)
   at System.Windows.Forms.TreeView.TvnBeginDrag(MouseButtons buttons, NMTREEVIEW* nmtv)
   at System.Windows.Forms.TreeView.WmNotify(Message& m)
   at System.Windows.Forms.TreeView.WndProc(Message& m)
   at Microsoft.AnalysisServices.Controls.MetadataTreeView.WndProc(Message& msg)
   at Microsoft.AnalysisServices.Browse.CubeBrowser.CubeBrowserMetadataTreeView.WndProc(Message& msg)



Error Image

 

RetrievingTheComClassFactory

 

 

Resolution

There are correlating errors on the .Net and prominent fixes includes:

Repair / Install Microsoft Office 2003 Web Components

 

The OWC track did not work for us.

Inspect MS Windows registry

Searched the Windows registry for C966837E-970C-4F4F-A91D-D81E21ABBBDB and found the stub, but could not find CLASS (CLS) branch.

VSIntegrationNativeHelpers

 

 

Tried fiddling with MS Windows Registry

Tried fiddling with MS Windows Registry, but could not find good starting data on the Internet; note that data has to match SQL Server Version, etc.

Install latest Product patch

Downloaded and Installed MS SQL Server v2008/R2 SP2.

BTW, patch is available @ http://www.microsoft.com/en-us/download/details.aspx?id=30437

 

Possible Workaround

Registry Tweaking

BTW, if you want to try to tweak your registry and see if that might work and you avoid installing\re-installing a Service Pack, here is a working version:



Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}]
@="MarshalledToIStreamDataObject Class"
"AppID"="{B2463DC8-B3FA-4BEC-945E-60219DCC6FD8}"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\InprocServer32]
@="c:\\Program Files (x86)\\Microsoft SQL Server\\100\\Tools\\Bin\\Microsoft.DataWarehouse.VsIntegration.Helpers.dll"
"ThreadingModel"="Apartment"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\ProgID]
@="VsIntergrationNativeHelpers.Marshalle.2"

[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C966837E-970C-4f4f-A91D-D81E21ABBBDB}\TypeLib]
@="{84F2933D-1F4E-43D8-9006-372E64998B36}"



Please keep in mind that the registry entry pasted above is for MS Windows 2008/R2 SP2.  Other versions of SQL Server will invariable have version specific entries.

 

Listening To

Listening to Brett Eldredge – Raymond (Video) [http://www.youtube.com/watch?v=txCUwSKo1kg]

 

 

Posted in Business Intelligence Development Studio, Microsoft, MS SQL Server, MS SQL Server Analysis Services (SSAS), Technical | Tagged , , , , , | Leave a comment

Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Introduction

Getting up to speed with SQL Server Analysis Service Cube Browsing and Reporting. And, finding that I need to get comfortable with MDX.

MDX is a query language for querying OLAPS.

Data Source View

DataSourceView


Fact Table Browser

What we are trying to do, that is filter on specific Country IDs and Names, is easy to do when using a query or reporting tool. Before Filtering Browser-Before-Filtering

Post Filtering Browser-After-Filtering

Query

But, when we resort to MDX and try to do same, we find out that we have to work a bit harder. In the next two examples, we attempt to filter by using the where clause.

Error Message :- The MDX function CURRENTMEMBER failed because current coordinate is empty.

In this scenario, we pass in a non-existence Country ID (47)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[47]

		}

	  )

Here is our error message:

Executing the query ...
The MDX function CURRENTMEMBER failed because current coordinate is empty.
Execution complete

Error Message – The Geography hierarchy already appears in the Axis1 axis.

In this scenario, we pass in an existing Country ID (4)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[4]

		}

     )

 

Error Message:

The Country ID hierarchy already appears in the Axis1 axis.

Resolution

There are a couple of ways to address our little problem.

Our proposed solution involves introducing our filtering at the Row sub-section.

To do so we can employ the exists or the Filter expressions.

Fix Problem by using exists keyword



select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, EXISTS
		(
			  [Country].[Country ID].Members
			, {
			         [Country].[Country ID].&[3]			
			       , [Country].[Country ID].&[4]
                           }

	       ) on ROWS

from  [DBLAB]

Fix Problem by using “filter” keyword


select

	{
	    [Measures].[GDP Amount]

	} on COLUMNS

	, FILTER
		(
	 	    [Country].[Country ID].Members
			, (
				    ([Country].[Country ID].Member_value = 2)
				 or ([Country].[Country ID].Member_value = 3)
			  )	 

	    ) on ROWS

from  [DBLAB]

References

References – MDX Query

References – Restricting the Query with Query and Slicer Axes

References – Blogs

 

References – MDX Query – Q/A

Posted in Database, Microsoft, MS SQL Server Analysis Services (SSAS), MultiDimensional eXpressions (MDX), OLAP | Tagged , | Leave a comment