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
CREATE TABLE Demo.Users
( UserId varchar(10)
CREATE TABLE Demo.Dates
( DateValue date
SELECT Users.UserId, Dates.DateValue
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
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)
T1.id, 0,T1.parentid, T1.id as topparent
T1.parentid IS NULL
T1.id, TH.Level+1, T1.parentid,
CASE WHEN TH.parentid IS NULL THEN T1.parentid ELSE TH.parentid END as topparent
INNER JOIN Hierarchy1 TH ON TH.id = T1.Parentid
/* Get Hierarchy Results*/
SELECT * FROM Hierarchy1
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
Start sending and receiving your Outlook.com emails on your mobile device quickly and painlessly with the following configuration.
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.
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 …
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.
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 …
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.
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.
You created your first Performance Point data source in Performance Point Dashboard Designer, you fill in the servername, however when you select the database you get the error along the lines of “Data source cannot be used because PerformancePoint Services is not configured correctly”.
This should just be a matter of going into Sharepoint 2010 Central Administration, select Application Management, Manage Service Applications, select the Performance Point Service Application (not the proxy), and select Manage. You can then select “Performance Point Service Application Settings” and configure and unattended account that has permissions to the data source. Once the login and password has been entered and you have clicked OK, then retry selecting the database in Dashboard Designer.