Cross Join to Generate Every Combination

Cross Joins are useful in Business Intelligence where you want to perform a cartesian product on two sets of data.  Possible uses are discussed below http://stackoverflow.com/questions/219716/what-are-the-uses-for-cross-join … in fact, it even makes a good business name http://www.crossjoin.co.uk/ … 🙂

Here is a basic example of how a cross join works using userid’s and dates.


CREATE SCHEMA Demo
GO
CREATE TABLE Demo.Users

( UserId varchar(10)

)
GO
INSERT Demo.Users
VALUES
('User1')
INSERT Demo.Users
VALUES
('User2')
INSERT Demo.Users
VALUES
('User3')
CREATE TABLE Demo.Dates

( DateValue date

)
GO
INSERT Demo.Dates
VALUES
(getdate())
INSERT Demo.Dates
VALUES
(getdate()-1)
INSERT Demo.Dates
VALUES
(getdate()-2)

SELECT Users.UserId, Dates.DateValue
FROM Demo.Users
CROSS JOIN
Demo.Dates

Selecting the Parent Description in a Hierarchy using a CTE

Some sample code for how to get the parent’s description in a parent child hierarchy using a CTE.

CREATE TABLE dbo.PCH

( id int null, description varchar(10) null, parentid int null )

INSERT PCH VALUES (5, 'Id Four', 4)

INSERT PCH VALUES (4, 'Id Four', 2)

INSERT PCH VALUES (3, 'Id Three', 2)

INSERT PCH VALUES (2, 'Id Two', 1)

INSERT PCH VALUES (1, 'Id One', null)

/* Get the Parent Description*/
;WITH Hierarchy1 (id, description, level, parentid, parentdescription)
AS (SELECT T1.id, T1.description, 0, T1.parentid, convert(varchar(10),'') AS ParentDescription
FROM PCH T1
WHERE T1.parentid IS NULL
UNION ALL
SELECT T1.id, T1.description, TH.Level+1, T1.parentid, TH.description AS ParentDescription
FROM PCH T1 INNER JOIN Hierarchy1 TH ON TH.id = T1.Parentid )
SELECT * FROM Hierarchy1

/*Get the very Top Parent*/
;WITH Hierarchy1 (id, level,parentid, topparent)
AS
(
 SELECT
 T1.id, 0,T1.parentid, T1.id as topparent
 FROM
 PCH T1

 WHERE
 T1.parentid IS NULL

 UNION ALL
 SELECT
 T1.id, TH.Level+1, T1.parentid,
 CASE WHEN TH.parentid IS NULL THEN T1.parentid ELSE TH.parentid END as topparent
 FROM
 PCH T1
 INNER JOIN Hierarchy1 TH ON TH.id = T1.Parentid

)

/* Get Hierarchy Results*/
SELECT * FROM Hierarchy1

/*Has Children*/

SELECT Hierarchy1.Id, Hierarchy1.Level, CASE WHEN EXISTS
 (SELECT TOP 1 1 --you can actually select anything you want here
 FROM Hierarchy1 Hierarchy1Check
 WHERE Hierarchy1Check.ParentId = Hierarchy1.Id
 ) THEN 1 ELSE 0 END AS HasRows
FROM Hierarchy1


Mobile Configuration for your Outlook.com Live Email account

Start sending and receiving your Outlook.com emails on your mobile device quickly and painlessly with the following configuration.

https://ironcovesolutions.zendesk.com/entries/20330427-imap-pop-settings-for-major-mail-providers

Account : Pop3

Email Address : Self explanatory

UserName : Same as Email Address

Pop Server : pop3.live.com

Pop Security : SSL If Available

Port : 995

Smtp Server : smtp.live.com

Smtp Security : TLS

Port : 587

Note, if you are setting up email for a desktop mac and you want you access your Outlook/Live/Hotmail account, don’t even waste your time with Outlook Mac.   Try Mailtab for Outlook instead.

Limiting Sharepoint 2010 Memory on an All in One B.I. Box

With all of these new fandangle self service Business Intelligence tools hanging off Sharepoint now, you may want to protect the precious box against unintentionally melicious drag and drop power moves.   Sharepoint and SQL Server on the same box is an easy equation and easy to install, however if things start to bog down and you have other web applications running on the same box, you may want to set a limit on the Physical Memory Limit (Kb) property.

Open IIS, go to the default Sharepoint application pool, select Advanced Settings and enter a value for the Physical Memory Limit (Kb).  The best reference I could find on an easy approach is outlined here …

http://blog.walteralmeida.com/2011/07/iis7-private-memory-limit-versus-virtual-memory-limit.html

There is also a really good run down on each of the Application Pools outlined here – thanks Joel! http://blogs.msdn.com/b/joelo/archive/2007/10/29/sharepoint-app-pool-settings.aspx

Of course you can also cap the memory that SQL Server uses and/or use the SQL Server Resource Governor to stop processes running away with your server’s resources.

Failing this, you could get a business case together and go for a distributed architecture.

Performance Point – Importing Items from another server

Nice how-to on how to import performance point items from one server and deploy the content to another.  Much like importing content into an empty Visual Studio solution, the same idea applies to a Performance Point workspace file.   It starts with opening up Dashboard Designer on the source server, then saving the content into a new workspace file.   You can then deploy by copying the workspace file to the new server, then using the Import Items function inside Dashboard Designer.  Finally, apply any additional tweaks such as editing the data source details on the destination server for example, then deploy the dashboard object.

More details here …

http://buddhipro.blogspot.com.au/2011/05/import-performancepoint-content-from.html

Performance Point Not Seeing Changes in the Cube

If you have made changes in your cube yet you don’t yet see the changes in Performance Point, it is likely that the old schema is cached.  A general rundown on how caching works in Performance Point is outlined below.

http://blogs.msdn.com/b/performancepoint/archive/2010/04/16/a-general-caching-overview-for-performance-point-services-for-sharepoint-2010.aspx?Redirected=true

The moral of the story is make sure the cache interval on your Performance Point data source is set to something low (default is 10 minutes) otherwise you could try an iisreset (type iisreset at the command prompt).

If all else fails you have likely got perspectives on your cube that have not been updated when the cube changes were made.