Northwind Odata Feed

If you are new to Odata feeds, you may like to play around with some sample data to see how one works. Northwind is a fictitious company database that has been used in SQL Server and Microsoft Access for may years and kind of like the Grand-Daddy of Adventureworks.

Access the Northwind Odata feed from here  http://services.odata.org/Northwind/Northwind.svc/.

This is the URL that you need to paste into the Data Feed URL field of the PowerPivot Table Import wizard.

Image

For first timers this is a good starting page where you can find links to how to create an OData feed and other FAQs http://www.odata.org/faq.  This can be useful when you want an oData feed from your vendor, but they have never heard of it.

The PowerPivot Table Import Wizard will chug you through a few screens and then you are on your way to consuming a real Odata feed sample.

Image

Happy Data Feeding !

Adding a calculated column for an IsCurrent record SCD

You want to add a simple calculated column to identify a current record using some criteria.  An example might be the Slow Changing Dimension task in SSIS, which only provides a null end date for new records inserted into the table.

The following example adds a calculated column called “IsCurrent” to the table “YourDimensionTable” based on records that have a null end date for the column “date_to”.


ALTER TABLE YourDimensionTable

ADD IsCurrent AS CASE WHEN date_to is null then 'Yes' else 'No' end

GO

Now you have provided  a more intuitive method for identifying or slicing current records i.e.


SELECT * FROM YourDimensionTable WHERE IsCurrent = 'Yes'

On a side, there is a reference here for customising the scd task to cater for custom dates…

http://www.bp-msbi.com/2010/04/custom-dates-for-an-ssis-scd-task/

 

Windows – Change Your Drive Letter using the Command Prompt

For whatever reason your drive letters have changed on your Windows PC which is bad news if you are running SQL Server databases on that drive.  We want to reassign the drive letter using command prompt only. Begin by opening a command prompt.

We will need to launch the diskpart utility from the command prompt.  Type :


diskpart

Type the following to return a list of volumes on your machine :


list volume

Under the column “Volume ###” will be a list of volumes by Volume name, and under the column “Ltr” will be the currently mapped drive letter.  Select the Volume you want to change the drive letter for e.g. to select volume 3 … :


select volume 3

Now change the drive letter to for example, “d”, by issuing the command :


assign letter = d

Thats it, but you will need to restart the machine.

PDF Documents as a Data Source using SSIS

You need to import some tabular data into your database via SSIS but your customer can only deliver it in PDF format.

The Nitro PDF tool isolates tabular content in a PDF and converts it to Excel format.

http://www.nitropdf.com/sem/2012/convert-pdf-to-excel?gclid=CMrq7LGFkbQCFQRKpgodIlAASQ

It supports command line operation and can therefore be integrated within the SSIS package control flow using the Execute Process task.

http://kb.nitropdf.com/article.aspx?id=10163

Analysis Services Processing Task Connection String

Keep your Analysis Services Connection String dynamically with the following sample then just substitute the placeholders.  In design of course, you can simply click Edit and create a connection using the Analysis Services Connection’s GUI front end configuration screen.


Data Source=%SQLInstanceName%;Initial Catalog=%SSASDatabaseName%;Provider=MSOLAP;Integrated Security=SSPI;Impersonation Level=Impersonate;

Business Analytics Conference 2013 !

No it has nothing to do with bacon…rather, the Pass Business Analytics Conference 2013 is a new event focussed fairly and squarely on … Microsoft Business Intelligence … or Business Analytics as it sounds cooler.  There will also be a lot of other in fashion words being thrown around at the event, like Big Data.

http://baconference.sqlpass.org/

Be there in 2013 !
April 10-12  Chicago, IL !

Tracks include :

Data Analytics and Visualization

Advanced Analytics and Insights

Information Delivery and Collaboration

Big Data Innovations and Integration

Strategy and Architecture

 

 

Rank Records According to Date with RANK() TSQL

You have a bunch of active records that you would like to rank in descending order of record date i.e. most recent gets the ranking 1, etc.

A quick sample is provided here to get you going.  This will select your records but add an additional column called RecordRank to store the Rank value.


SELECT RecordKey, RecordStatus, RecordDate,
 RANK() OVER (PARTITION BY RecordKey ORDER BY RecordDate DESC) AS RecordRank
 FROM dbo.Records

A full working example is provided below with sample data.


CREATE TABLE dbo.Records
(
RecordKey int,
RecordStatus varchar(10),
RecordDate datetime
)
GO

INSERT dbo.Records
VALUES
(1,'Active','2010-04-01 15:37:00.000')
INSERT dbo.Records
VALUES
(1,'Active','2010-04-03 11:05:00.000')
INSERT dbo.Records
VALUES
(1,'Active','2010-05-01 09:00:00.000')
GO
SELECT RecordKey, RecordStatus, RecordDate,
RANK() OVER (PARTITION BY RecordKey ORDER BY RecordDate DESC) AS RecordRank
FROM dbo.Records