Excluding PolygonZ and M in shape file using ArcGIS

To do this using tools available in ArcMap follow these steps. 

Locate the following tool - 
ArcToolbox > Conversion Tools > To Shapefile > Feature Class To Shapefile (multiple) 

Launch the tool and add your Polygon ZM shapefiles to the input features. 

Select an 'Output Folder' 

Click on the 'Environments...' button at the bottom of the dialog box. To the right of the 'OK' button. 

Click on the 'General Settings' text. 

You should see two fields marked 'Output has Z Values' and 'Output has M Values'. 

Set both fields to 'Disabled'. 

Click 'OK' to exit the Environments Settings then click 'OK' to run the tools. 

You should now have copies of your original shapefiles, without Z & M values in your new folder. 

Courtesy
http://forums.esri.com/Thread.asp?c=93&f=1730&t=296920

Geocoding Postal code

http://www.gpsvisualizer.com/geocoder/

Allows to geocode 5 postal code at a time.


Another link where all Canadian postal codes with Longitude Latitude included in single .gz file

http://geocoder.ca/?freedata=1

Using Table variables in SSIS Package

Issue:

When declaring and using T-SQL table variables in SSIS pacakge results in zero rows results.

Solution:
In the query under OLEDB source before actual query execution use "SET NOCOUNT ON".

Aggregating Spatial Data

Spatial table data can be aggregate such as to get a high level map.  This code works for tables in SQL 2012.

SELECT    [Column Name]    ,geometry::UnionAggregate(geom.MakeValid()) AS goem
FROM             
GROUP BY [Column Name]

Dynamic Dimension Security MDX Error

Getting errors:

Check MDX script syntax failed because of the following error:
----------------------------------
An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[Dim Employee]' was not found in the cube when the string, [Dim Employee].[LoginID].[CORP\Name], was parsed.

Solution:
When you set up your role, use the following MDX for the "Allowed member set" for the "User Name" attribute of the "Dim User" Dimension (not the Cube Dimension, the regular Dimension):
STRTOSET("[Dim User].[User Name].["+Username+"]")

You do NOT need to enable Visual Totals!

The other thing you need to set in your role is the "Allowed member set" for the "Dim Reseller" CUBE Dimension. This is important. If you put the security on the regular "Dim Reseller" Dimension, it doesn't seem to work. You have to secure the CUBE Dimension for "Dim Reseller" (I guess this is because only the Cube Dimension knows about the relationships defined for the cube)? Anyway, set the allowed member set to:
EXISTS(
[Dim Reseller].[Dim Reseller].Members,
STRTOSET("[Dim User].[User Name].["+Username+"]"),
'Fact Reseller User'
)

This time you DO need to enable Visual Totals (at least if you want to restrict the FactResellerSales data).

Referenced from Comments from:
http://hccmsbi.blogspot.ca/2007/08/implementing-user-specific-security-in.html

Activate SSRS subscription report on demand

I usually do this to run subscription based report based on SSIS package routine steps. This enables me to trigger subscription based report on demand when data has been refreshed on my Data warehouse.

Run this on Report server.

DECLARE @EVENTData varchar(100)
select @EVENTData=SubscriptionID from dbo.Subscriptions
where Description = ['Report subscription name']

PRINT @EVENTData

exec ReportServerDB.dbo.AddEvent @EventType='TimedSubscription', @EventData=@EVENTData

Issue: Browsing HTML file on Sharepoint 2010 prompt to download

Issue: When you try open a aspx/html/pdf or other such files from your SharePoint Foundation 2010 site; you are prompted for downloading the file rather than opening the file in Browser.

Resolution:

This is an enhanced feature in SharePoint Foundation 2010 and IE8.  Microsoft intentionally locked browser handling to strict to avoid malicious code execution using HTML and Java script object model. In the previous versions of SharePoint; you could open the files directly.  By default; the browser file handling option is set to strict in Central Administration

To change this setting; you need to follow the steps given below.  Please note that the steps needs to be performed in the Central Administration; which is available in your SharePoint Server.

1.    Goto Web Application and Select the WebApplication under which the Site Collection and files are located.

2.   Click on General Settings from the top “Ribbon”.

3.   Select General Setting

4.   Locate the section “Browser File Handling”

5.   Change it to Permissive.

Using xml path to combine multiple rows into single column

Can combine multiple row values into a single column using xml path function

select distinct employee_manager_id,
(SELECT ',' + h1.employee_id from hr.dim_employee_reporting h1
where h1.employee_manager_id=h.employee_manager_id for xml path('')) as employees
from hr.dim_employee_reporting h
where employee_manager_id = 'xxxxx'

Source reference: http://bisqlserver.rdacorp.com/2011/10/using-for-xml-path-to-combine-multiple.html

Changing DSV file name of and existing cube

One of my requirement was to create a new cube from existing cube (structure of the cube was important for me) and point it to a different Data source view (since i wanted to extend the existing DSV to accomodate additional changes while retaining the old cube as it is.).  Unfortunately the DataSourceViewID property for the cube/ dimensions are greyed out from BIDS.

So the trick is to open the Cube in Code view and search for DataSourceViewID element.  Once found change the value to new DSV file name.


<Source dwd:design-time-name="b225080e-6fcc-4a40-8a7a-7233084ee828">
    <DataSourceViewID>XXXXXX</DataSourceViewID>
  </Source >


Thats it now when you go on the UI and choose to Edit Data Source View for the cube or dimension it would take you to the new DSV file name you have associated it to.

Excel 2007 error : The cube might have been reorganized or changed on the server.

While trying to deploy a cube and opening in Excel 2007 (Pivot table). I encountered this error:

Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server. 

Contact the OLAP cube administrator and, if necessary, set up a new data source to connect to the cube.

I checked the datasource it was all good.  I could further access the processed cube through Proclarity and SSMS.  But excel wouldnt accept it.


So I suspected that it might be one of the calculated member in my cube that might be causing this issue.  And viola there it was one of the named set created in my cube had an incorrect Hierarchy path.  I guess Proclarity and SSMS ignored this but excel was bit strict on that.  Fixed the Hierarchy path, reprocessed the cube and bang!! I am back on business.

Other reason for this as explained in MS KB articles may be due to invalid characters in attributes:

http://support.microsoft.com/kb/210806

or problem with translation setting

http://support.microsoft.com/kb/931388/en-us

In my case all these seems be ok.  The issue was the faulty named set.

Insert tid bits from SQL Pass 2011

Found this useful bit of tip from SQL Pass 2011 presentation.

create table dbo.numbers
(
Numbers int primary key
);

SELECT TOP(1000) RN = ROW_NUMBER() OVER
(ORDER BY c1.[object_id])
FROM sys.columns c1
cross join sys.columns AS c2
cross join sys.columns as c3
order by c1.[object_id]

Alternative Time Intelligence implementation reference link

Very interesting blog by Marco Russo about alternative time intelligence implementation (YTD etc).

http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx

Get Job Name of the SQL Job

DECLARE @SQL NVARCHAR(72),        @jobID UNIQUEIDENTIFIER,        @jobName SYSNAMESET     @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'EXEC    sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT

SELECT  @jobName = nameFROM    msdb..sysjobsWHERE   job_id = @jobID

Querying Cube (SSAS) Metadata Information

Internal SSAS DMV's can be used to gather MetaData information about SSAS Objects. Following are the list of DMV's which enable to query Cube, Dimension, Hierarchies, Performance information etc using SSMS.

$SYSTEM.MDSCHEMA_CUBES $SYSTEM.MDSCHEMA_DIMENSIONS $SYSTEM.MDSCHEMA_FUNCTIONS $SYSTEM.MDSCHEMA_HIERARCHIES $SYSTEM.MDSCHEMA_INPUT_DATASOURCES $SYSTEM.MDSCHEMA_KPIS $SYSTEM.MDSCHEMA_LEVELS $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS $SYSTEM.MDSCHEMA_MEASUREGROUPS $SYSTEM.MDSCHEMA_MEASURES $SYSTEM.MDSCHEMA_MEMBERS $SYSTEM.MDSCHEMA_PROPERTIES $SYSTEM.MDSCHEMA_SET

References : 

Checking Cube performance: 

http://blogs.microsoft.co.il/blogs/yanivmor/archive/2010/01/27/dmvs-for-analysis-services.aspx

Querying SSAS:

http://richardlees.blogspot.com/2010/07/querying-analysis-services-for-cube.html

http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

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

Debugging Filter Link in PPS Dashboard

Found this link by Nick Barclay which describes method to debug and test Filter Link parameters passed to PPS dashboard components http://nickbarclay.blogspot.com/2008/02/debugging-filter-links-with-web-page.html Further the Post Filter formula can also be evaluated by adding additional formula : For example : Descendants(<<uniquename>>,<<level>>) TOPCOUNT(<<uniquename>>,<<level>>), UNION also IIF conditions can be used.

Tool to create Insert data Scripts in SQL Server 2008

A Niffty tool SQLPubwiz.exe under %Program Files%\Microsoft SQL Server\90\Tools\Publishing\1.2 allows you to generate scripts for either a individual objects or the entire database. For table it generates insert scripts with data which is really valuable considering you need to backup master data information entered into tables.

Link a Scorecard to a Web aspx page

Assumption: The web pages bears the Sharepoint list reference through webpart and the list has been filtered using the filter condition of the webpart.

1) Add custom property to KPI in the dashboard such that the name of the custom property should be same as the QueryString name used to pass to the web page. The value can be either be hard coded or dynamic.

2) Once custom property has been added to KPI it should display in dashboard editor under KPI. Select the customer property name and drag and drop it on the web page.

3) Make sure that the display condition is set appropriately such as to show all the KPI's for which the sharepoint list values needs to be displayed.

Publish and browse the dashboard to view.

You can debug if the Query string value is being passed correctly using fidler utility.

PS: You can acheive the same using SSRS report too. If the SSRS Report points to a Sharepoint List.

Fixing corrupted SSAS Cube.

1. Stop the Analysis Services service
2. Delete everything in your C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\%YOUR_PROJECTNAME% folder
3. Restart the Analysis Services service
4. Re-deploy your project

How To : Create Parent/ Child List in Sharepoint Videos

http://www.youtube.com/watch?v=HPsk3RRVPtg&feature=related

How to check permission in SSAS by impersonating user

Question:

There is a button "Change User" in Cube Browser, we can impersonate other user in "Security Context" dialog. But in SSMS, how do I impersonate another user to test the MDX query?

Answer:

In SSMS, connect to SSAS server, type the Server name and then click button “Options”. Switch to “Additional Connection Parameters” tab, specify the user account for property “EffectiveUserName”:

EffectiveUserName="UserAccount"

You can also test the user account with below query:

WITH MEMBER MEASURES.X AS
USERNAME
SELECT MEASURES.X ON 0
FROM [Adventure Works]
 
But note: Only SSAS server administrators can use the EffectiveUserName property. Since you are still using current windows account to logon to the SSAS server, you will access to SSAS server with your own credential, and EffectiveUserName property only works with your MDX query.