posted 7/2/2011 by MMilligan - Views: [26554]
BI from g8rpal on 8tracks.
-------------------------------------------------------------------------------
5/22/2013
"The survey findings would seem to suggest that the CFO prioritizes business applications higher than the CIO does," said Bill Sinnett, senior director, research at FERF. "If the CIO does not understand this, then there's a chance the CFO will sponsor his or her own initiatives, and not coordinate them with the IT organization. This demonstrates the trend that BI is becoming less of a CIO responsibility and more of a CFO and line-of-business responsibility."
Gartner Says Business Intelligence/Analytics Is Top Area for CFO Technology Investment Through 2014
Analysis Services and the Case of the Standby Cache
Finding and killing SSAS Sessions
Dealing with Long Running SSAS Queries using Powershell
Power View for Cubes
Coming Soon: A New Edition of Information Dashboard Design
5/20/2013
EffectiveUserName research
Using EffectiveUserName To Impersonate in SSAS
https://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=18
This link says you do not need to configure Kerberos on theSSAS server:
How to configure dynamic security in Analysis Services andPerformance Point 2010 without Kerberos
http://tavislovell.com/how-to-configure-dynamic-security-in-analysis-services-and-performance-point-2010-without-kerberos/
Use Analysis Services EffectiveUserName in SharePoint Server2013
http://technet.microsoft.com/en-us/library/jj219741.aspx
Use EffectiveUserName in PerformancePoint Services(SharePoint Server 2013)
http://technet.microsoft.com/en-us/library/jj938167.aspx
Unit Testing Role Security in Analysis Services
http://www.sqlchick.com/entries/2012/10/7/unit-testing-role-security-in-analysis-services.html
4/25/2013
SQL SATURDAY IS THIS SATURDAY!
Registration is Open! http://www.sqlsaturday.com/215/register.aspx
My session starts at 9AM, Data Mining with DMX.
Get yesterday's date and more using MDX
SELECT PARALLELPERIOD([Date].[Calendar].[Date],1 ,StrToMember("[Date].[Calendar].[Date].&[" + VBAMDX.Format(VBAMDX.Now(), "yyyyMMdd")+ "]")) ON COLUMNs FROM [Adventure Works]; ------------------------------------------------------------------------------- -- The First Calculated member is the value of NOW() WITH MEMBER [Measures].[Full Date] as 'NOW()' -- The Second Calculated Member is the Day part of the first calculated member. MEMBER [Measures].[What Day] as 'DAY([Full Date])' -- The Third Calculated Member is the Month part of the first calculated member. MEMBER [Measures].[What Month] as 'MONTH([Full Date])' -- The Fourth Calculated Member is the Year part of the first calculated member. Member [Measures].[What Year] as 'YEAR([Full Date])' SELECT {[Full Date],[What Day],[What Month],[What Year]} ON COLUMNS FROM [Adventure Works];
SSIS VB.Net Script to download an FTP file from an AS/400 DB2 server. (Credit goes to Michael Simon for piecing this together from various sources on the internet.)
Public Sub Main() Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "192.168.0.1") cm.Properties("ServerUserName").SetValue(cm, "Hugh") cm.Properties("ServerPassword").SetValue(cm, "Janus") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above. Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() 'Build a array of all the file names that is going to be FTP'ed (in this case only one file) Dim FileName(0) As String FileName(0) = Dts.Variables("ftpFileAndMember").Value.ToString Dim FileDate As Date FileDate = System.DateTime.Today Dim currentDirectory As String currentDirectory = Dts.Variables("ftpDirectory").Value.ToString ftp.SetWorkingDirectory(currentDirectory) 'ftp the file Dim LocalPath As String LocalPath = Dts.Variables("localFilePath").Value.ToString ftp.ReceiveFiles(FileName, LocalPath, False, True) ' the True makes it overwrite existing file and False is saying that it is not transferring ASCII ftp.Close() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure End Try Dts.TaskResult = ScriptResults.Success End Sub
Exciting PerformancePoint links!
PerformancePoint tutorial
Formatting your PerformancePoint Analytic Grid!
Using SSAS MDX Calculation Color Expressions
4/1/2013
Extra Exciting Links!
Comparison of Queries Written in T-SQL and SQL Server MDX
SQL Server Analysis Services Period over Period Variance Analysis
2/27/2013
On Saturday, April 27th at 9:00 AM I will be presenting a session at Jacksonville's SQL Saturday event on the DMX data mining query language. Hope to see you there!
http://sqlsaturday.com/viewsession.aspx?sat=215&sessionid=13336
SQL Down Under - Be sure to check out Erin Walker's session on dimensional modeling! Our favorite Jacksonville consulting company Pragmatic Works is mentioned.
http://www.sqldownunder.com/
Bunch of posts about PerformancePoint and using Excel w/ KPIs from SSAS
http://www.baydynamics.com/Blog/category/PerformancePoint.aspx
Unbelievable. M$ and their licensing. Looks like they are effectively killing PowerPivot and PowerView.
http://www.powerpivotpro.com/2013/02/guest-post-from-ken-puls-how-to-buy-powerpivot-2013-including-the-30-volume-licensing-workaround/
Furthermore, with the more expensive SQL Server 2012 licensing (per core) I have to wonder how many people are staying with SQL 2008 R2. Our server has 80 cores! Why would we switch? Theoretically you could keep the database engine on 2008 R2 and upgrade your SSAS, SSIS, and SSRS servers to 2012.
Check out this post from Brent Ozar and in particular his recommendation that you NOT use order by and instead let your application do the sorting. This is contrary to what used to be the best practice of letting the SQL engine do the work. Those of you that work with SSRS should know that letting the SSRS engine sort the data takes longer than the SQL engine more often than not. The reason? Cost per core.
"Avoid ORDER BY; sort in the app instead. To sort your query results, SQL Server burns CPU time. SQL Server Enterprise Edition goes for about $7,000 per CPU core – not per processor, but per core. A two-socket, 6-core-each server rings up at around $84k – and that’s just the licensing costs, not the hardware costs. You can buy a heck of a lot of application servers (even ones with 256GB or more of memory) for $84k."http://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/
2/1/2013
PerformancePoint Gotcha! - "PerformancePoint Services could not connect to the specified data source." When creating a new dashboard, you MUST do a save all right after you create your data sources otherwise you won't be able to create anything that uses that data source.
Dinky Linkys
Starting a Bike Shop (like AdventureWorks!)
Research Quality Data Sets
How to Create and Use Temp Tables in SSIS
Programmer Bob - Latter-day Tom Sawyer or Massive Security Risk
1/25/2013
SharePoint Gotcha! - Creating SharePoint 2010 list from Exel 2010 spreadsheet
This needs to be a future blogpost. Here's the solution:
http://office.microsoft.com/en-us/excel-help/export-an-excel-table-to-a-sharepoint-list-HA010131472.aspx
Here is some of the crud I went through to get there:
http://www.sharepointassist.com/2009/01/23/method-post-of-object-iowspostdata-failed-when-attempting-to-import-an-excel-spreadsheet/
Dadgum it Micro$oft! This product is 3 years old! Here's a bugfix: get rid of the button if you can't make it work! I hope it's fixed in SharePoint 2013. All kidding aside, what am I going to do? Switch to Cognos. LOL.
SSAS Gotcha! - Duplicate Attribute Key
Remove all hierarchies. Add them back one by one. Select the errors through the relational engine w/ each column in the hierarchy ordering from top down. When you find inconsistencies such as: month belonging to more than one quarter (ha ha!), employee having more than one manager (master), then you either need to make a compound key and force the hierarchy or the hierarchy doesn't make sense to begin with.
This needs it's own post, time permitting.
Other interesting tidbits:
Do not Disurb! http://blog.ninlabs.com/2013/01/programmer-interrupted/
Cost of Interruptions: http://www.infoq.com/news/2013/01/Interruptions
(strangely similar)
Game theory and Batman, RIP: http://www.aaronsw.com/weblog/tdk
Reboot! http://www.serverintellect.com/support/windowsserver2003/last-boot-time.aspx
How crApple sets its prices: http://www.macworld.com/article/2024257/how-apple-sets-its-prices.html
Home Depot is looking at Pricing! http://techcrunch.com/2012/12/17/home-depot-acquires-data-driven-pricing-analytics-startup-blacklocus/
Motörhead! http://www.engadget.com/2013/01/05/compressorhead-ace-of-spades/
1/7/2013
New Years Resolutions - Professional Goals
1. Reboot
2. Avoid Snowflakes!
3. Learn more data mining and the DMX query language (and blog about it.)
4. Learn more about Master Data Services (and blog about it.)
5. Become a master at MDX
6. Architect an enterprise solution in which Contoso buys AdventureWorks and Northwind (and blog about it.)
7. One presentation per quarter and at least one really good blog entry per month
8. Mentor someone outside of the company I work
9. Keep a personal log in which I will plan each day and reflect on the previous day every morning.
10. Publish Segment Analyzer on codeplex (and blog about it.)
Segment Analyzer is an idea I have to help analyze various customizable groups of customers, products, or any type of dimension in an analysis services cube. The main force behind the solution is a .Net application that can run against any data warehouse that uses a standard table and column naming scheme. It won't matter what the naming scheme is as long as it is consistent, that criteria would be configurable.
An analyst uses a GUI interface to create criteria for segments to be analyzed. The criteria is based on dimensional attributes, and aggregated and non-aggregated facts and supports complex parenthetical and/or logic at many levels. Dropdowns are populated based on the distinct values in the dimensional attributes and mathematical operations for the numeric information. Which attributes and which metrics are featured in the interface is handled through a one-time configuration process. Segments can have parent segments in which the criteria of the parent is inherited to infinite levels of children. This will be accomplished by parsing the segment criteria into dynamic SQL that populates bridge tables to support many to many dimensions in Analysis Services. The segment dimension itself uses the parent child hierarchy feature in SSAS. Two known query performance killers, I know; but, the features would only be used on specialized cubes or databases just for this purpose.
Examples of segments of customers from adventure works:
Customers that visited the store within the last six months who are female, are between 16 and 24 years old, live within 25 miles of the store and have spent at least $100 in the past six months and redeemed two promotional coupons.
Customers that visited the store within the last year, are male, live in the 12345 zip code, own one car, spent at least $200 in the past year, and redeemed 0 promotions.
11/1/2012
Tip of the Day
How to find the SSIS Package that Loads a Table
Browse to the packages folder using Windows Explorer and type “*.dtsx” into the search box.
Open Editpad Lite or a similar multi-tabbed editor.
Press Control-A to select all the packages and drag them to your editor.
Press Control F and enter the table name into the search dialog. Be sure to check the “All files” and “Loop automatically” checkboxes.
This also works for SSRS reports, stored procedures, views, etc.
10/1/2012
Presenting on the SQL Server 2012 Data Mining Add-ins for Office 2010 at Jacksonville Code Camp on October 6th.
http://jaxcodecamp2012.eventbrite.com/
Free Food!
8/30/2012
Reasons to use Money data type overNumeric(Decimal)
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx
4/24/2012
Presenting at SQL Saturday #130 on Saturday 4/28 at
University of North Florida
1 UNF Drive, Building 50, Jacksonville, FL, 32224
SQL Server 2012 Data Mining Add-ins for Office
http://www.sqlsaturday.com/viewsession.aspx?sat=130&sessionid=8564
Free SQL Server 2012 eBook: Kindle and others.
Great Podcast! Good discussion about the roles data scientists are playing.
Big Data Monetization
Some links I recently found useful...
Using Named Sets in Analysis Services to Deliver Special Date Ranges to End UsersDynamic named setsFilter the Source Data for a Pivot Table
New demo of Power View is Live!
SQL Server 2012 Demo Showcase
I received this question from a past bootcamper I thought I'd share...
"Would have to happen to have a split string function that can accept values with commas?
I have values like this that I will need to split (‘New York, Brooklyn’,’New Jersey,Hoboken’)
The functions that I currently have cannot handle data with commas."
--First we need this function
create function Split (@StringToSplit varchar(2048),@Separator varchar(128)) returns table as return with indices as
(select 0 S, 1 E union all select E, charindex(@Separator, @StringToSplit, E) + len(@Separator) from indices where E > S )
select substring(@StringToSplit,S,case when E > len(@Separator) then e-s-len(@Separator) else len(@StringToSplit) - s + 1 end) String
,S StartIndex from indices where S >0
--Now we use the function
declare @mystr as varchar(255)
set quoted_identifier off;
set @mystr = "'New York, Brooklyn','New Jersey,Hoboken'"
set quoted_identifier on
select @mystr
select REPLACE([String],char(39),'') from dbo.split(@mystr,''',''')
3/7/2012
MS released the Excel Data Mining plug-in for Excel! Woo Hoo! I can't wait to try this out. Once I do, you can be sure I'll be blogging about it. I just can't wait to share this information. Thanks Jorge Segarra for sharing this with me!
http://www.microsoft.com/download/en/details.aspx?id=29061
Also, Adam Jorgensen sent me this awesome link from Brent Ozar about table partitioning in SQL Server. Thanks Adam!
How to Decide If You Should Use Table Partitioning
Brent Ozar's post from a long time ago, Rock Stars, Normal People and You, is my favorite blog post of all time. It helped me pull the trigger on a decision I made about a year ago to leave a company before my first year was up and look for something more fulfilling. Leaving a company before giving them a full year trial was something I normally would never have considered. Now I am at Pragmatic Works and couldn't be more challenged. I've never met Mr. Ozar; but, I have him to thank for that. I hope I get the opportunity to thank him in person one day.
2/29/2012
New blog post on PerformancePoint 2010 Time Intelligence!
2/16/2012
Hey! What happened to January!?!? What a year already. No time for a proper post so this will have to do for now. -------------------------------------------------------------------------------
Happy New Year! It's been a long time since I've posted. Two terrible colds, Christmas, New Years, professional goals, and challenging projects are the culprits. One of my resolutions is to become a consistent blogger so you can expect more from me this year.
Many to Many Relationships http://pragmaticworks.com/foundation/