do you have the time

Do You Have The Time

Michael P. AntonovichBlog: http://SharePointMike.WordPress.comIT PRO Camp Jacksonville 2012

http://sharepointmike.wordpress.com/

Orlando Code Camp 2012 2

My Published Books• User’s Guide to the Apple ][ - 1983• FoxPro 2 Programming Guide – 1992• Debugging and Maintaining FoxPro – 1992• Using Visual FoxPro 3.0 – 1995• Using Visual FoxPro 5.0 – 1996• Office and SharePoint User’s Guide – 2007• Office and SharePoint User’s Guide – 2010

6/16/2012

Speaker at:Code Camp 2009, 2010, 2011, 2012 OrlandoSharePoint Saturday 2011 Tampa, 2012 OrlandoSQL Saturday - #1, #4, #8, #10, #15, #16, #21, #32, #38, #49, #62, #74, #79, #85, #86, #110, #130


Basic Terminology• Aggregate – A mathematical function that allows you to

summarize values of an attribute• Dimension - A dimension is essentially a look-up table that may

define a hierarchy or drill-down path such as Year > Quarter > Month

• Measure – A measure is something that identifies a value• Fact – A fact is another term for a measure that contains

numeric data that can be grouped along one or more dimensional hierarchy

• Star Schema – All dimension tables radiate out from a single fact table

• Snowflake Schema – One fact table may relate to another fact table before relating to dimension tables. One dimension table can also have a related dimension table

• A Pivot table or chart is usually based around a single fact table

6/16/2012


Structure of Analysis Services Cubes• Database must either be in a Star or Snowflake

model. This often means de-normalizing some tables or ‘flattening’ the data model. Essentially you need a central ‘fact’ table from which all supporting dimension data radiates.

• You must flatten many-to-many relationships into a single table with only the data that you need.

• Based on date data in your central fact table, build a date dimension table with the columns needed.

• Create a script to populate the date dimension table with data.

6/16/2012


Original Data Schema for Vaccination Data

6/16/2012


Build the Patient_Vaccinations Table-- Build the Patient_Vaccinations Table-- This will create a table of 3,462,140 rows SELECT PV.VisitID, PV.CountyID, PV.VisitDate, PV.RaceID, PV.LeadScreening, PV.PatientAge, PV.TargetedCaseMgmt, PV.THStepsMed, V.Vaccination, V.VCost INTO Patient_Vaccinations FROM PatientVisits PV JOIN Visits_Vaccinations VV ON PV.VisitID = VV.VisitID JOIN Vaccinations V ON VV.VaccinationID = V.VaccinationID ORDER BY 1,2

6/16/2012


Patient Visits and Vaccinations Flattened

6/16/2012


Calculate Date Range Needed

• You need to calculate the date range needed for your date dimension. I usually look for the earliest and latest dates in the fact table and round the dates to January 1st for the earliest date and December 31st for the latest date.

• DECLARE @Min_Date DATE;• DECLARE @Max_Date DATE;

• -- Get the smallest and largest date for start and end of date table• SET @Min_Date = (SELECT

CONVERT(DATE,'01/01/'+STR(YEAR(MIN(VisitDate)))) FROM dbo.PatientVisits);

• SET @Max_Date = (SELECT CONVERT(DATE,'12/31/'+STR(YEAR(MAX(VisitDate)))) FROM dbo.PatientVisits);

6/16/2012


Write Code to Build Date Dimension• DECLARE @SQLSTR varchar(8000);

• --Delete Date Dimension table if it exists• SET @SQLSTR = 'IF OBJECT_ID(''' + @tblname + ''', ''U'') IS NOT NULL• DROP TABLE ' + @tblname• EXECUTE(@SQLSTR)

• --Create Date Dimension table• SET @SQLSTR = 'CREATE TABLE ' + @tblname +• ' ([PK_Date] [datetime] NOT NULL,• [Date_Name] [nvarchar](50) NULL,• [Year] [char](4) NULL,• [Calendar_Year] [char](13) NULL,• [Quarter_Of_Year] [char](1) NULL,• [Quarter_Of_Year_Name] [char](9) NULL,• [Year_Qtr] [char](6) NULL,• [Year_Qtr_Name] [char](15) NULL,• [Month] [char](2) NULL,• [Month_Name] [char](15) NULL,• [Year_Month] [char](10) NULL,• [Year_Month_Name] [char](20) NULL,• [Day_Of_Year] [char](3) NULL,• [Day_Of_Year_Name] [char](8) NULL,• [Day_Of_Month] [int] NULL,• [Week_Of_Year] [char](2) NULL,• [Week_Of_Year_Name] [char](7) NULL,• CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED • (• [PK_Date] ASC• )WITH (PAD_INDEX = OFF, • STATISTICS_NORECOMPUTE = OFF, • IGNORE_DUP_KEY = OFF, • ALLOW_ROW_LOCKS = ON, • ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]• ) ON [PRIMARY]'• EXEC(@SQLSTR)

6/16/2012


Write Code to Populate Date Dimension

SET @Loop_Date = @Min_Date;

WHILE @Loop_Date <= @Max_Date BEGIN SET @SQLSTR = 'INSERT INTO ' + @tblname + ' VALUES (''' SET @SQLSTR = @SQLSTR + CAST(@Loop_Date as varchar(30)) + ''', ''' SET @SQLSTR = @SQLSTR + DateName(dw,@Loop_Date) + ', ' + DateName(m,@Loop_Date) + ' ' + RTRIM('0'+CAST(DatePart(d,@Loop_Date) AS char(2))) + ' ' + CAST(DatePart(yyyy,@Loop_Date) as char(4)) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Calendar ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''‘ SET @SQLSTR = @SQLSTR + CAST(DATEPART(q,@Loop_Date) as CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Quarter ' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Quarter ' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ', ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(m,@Loop_Date) As char(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + DATENAME(m,@Loop_Date) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('0'+RTRIM(CAST(DATEPART(m,@Loop_Date) As char(2))),2) + '-01' + ''', ''' SET @SQLSTR = @SQLSTR + DATENAME(m,@Loop_Date) + ' ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('00'+RTRIM(CAST(DATEPART(DayOfYear,@Loop_Date) AS char(3))),3) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('00'+RTRIM(CAST(DATEPART(DayOfYear,@Loop_Date) AS char(3))),3) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(dd,@Loop_Date) AS CHAR(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(wk,@Loop_Date) AS char(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('0'+RTRIM(CAST(DATEPART(Wk,@Loop_Date) AS char(2))),2) + ''')‘

EXECUTE(@SQLSTR) --Increment loop date and see if at end SET @Loop_Date = DATEADD(Day,1,@Loop_Date) END;6/16/2012


Final Data Schema for Vaccination Data

6/16/2012


Analysis Services Demo

6/16/2012


What is PowerPivot?

• PowerPivot is a data analysis tool• PowerPivot is a free download from Microsoft• PowerPivot lets you process millions of rows of data• PowerPivot uses as much memory as available &

multi-cores• PowerPivot lets you integrate data from multiple

sources• PowerPivot lets you explore, analyze and create

analysis• PowerPivot was originally called: Project ‘Gemini’

6/16/2012


PowerPivot PreRequesites

• 32-or 64 bit Operating System• 1 GB of RAM, 2 GB recommended (or more)• Windows XP with SP3, Vista with SP1, Windows 7• .NET Framework 3.5 SP1 or better• Microsoft Office 2010

6/16/2012


Why Use PowerPivot

• Fewer data requests since staff can slice and dice their own data

• More control over data other people can access

• Users can generate their own reports• PowerPivot gives SQL Server data access to

the masses

6/16/2012


Pre-Check Your Data

• Use 1 and only 1 header row• All data in column should be consistently

formatted• No blank columns• No blank rows• Data columns must be 100% filled with

data

6/16/2012


Order of Loading Data

• Always load the main transaction table first• This forces the fields to appear at the top of the PowerPivot Field List.• PowerPivot’s relationship detection expects the main table to be first.

• Import lookup tables in their hierarchical order

6/16/2012


Types of DAX Calculations

• Simple Calculations• Calculated columns within table• Calculated columns between tables• Calculated columns to serve as links to other tables• Calculated columns are calculated for every row in the

table.

• Aggregate Calculations• Calculate unique measures • Aggregate measures are only calculated for the displayed

data in the Pivot table

6/16/2012


DAX Functions• Comprised of 135 functions• 71 functions are similar to Excel functions• 69 have the same name – 2 do not• TEXT FORMAT• DATEDIF YEARFRAC

• 64 functions are unique to DAX• Aggregate data functions• Date related functions

6/16/2012


Available DAX Date/Time functions• =DATE(<year>, <month>, <day>)• =DATEVALUE(<date_text>)• =DAY(<date>)• =EDATE(<start_date>, <months>)• =EOMONTH(<start_date>, <months>)• =HOUR(<datetime>)• =MINUTE(<datetime>)• =MONTH(<datetime>)• =NOW()• =SECOND(<datetime>)• =TIME(<hour>, <minute>, <second>)• =TIMEVALUE(<time_text>)• =TODAY()• =WEEKDAY(<datetime>)• =WEEKNUM(<date>, <return_type>)• =YEAR(<date>)• =YEARFRAC(<start_date>, <end_date>, <basis>)

6/16/2012


Using FORMAT to get Names• Get the Year:

=YEAR(Patient_Vaccinations[VisitDate])

• Get Month Number:

=FORMAT(MONTH(Patient_Vaccinations[VisitDate]),"00")

• Get name of the month=FORMAT(Patient_Vaccinations[VisitDate],"MMM")

• Get name of the week day=FORMAT(WEEKDAY(Patient_Vaccinations[VisitDate]),

"dddd")

6/16/2012


Other Calculated Columns

• Calculating Quarter of Year=INT((MONTH(Patient_Vaccinations[VisitDate])-1)/3)+1

• Calculating Quarter of Year Name="Qtr " &

INT((MONTH(Patient_Vaccinations[VisitDate])-1)/3)+1

• Get week number of year=WEEKNUM(Patient_Vaccinations[VisitDate])

6/16/2012


PowerPivot Demo

6/16/2012


Useful Links

• Download PowerPivot at• http://www.powerpivot.com/download.aspx

• PowerPivot Sample Data• http://powerpivotsampledata.codeplex.com

• PowerPivot Tutorial Data• http://technet.microsoft.com/en-us/library/ee835510.aspx

• DAX On-line Function Reference• http://technet.microsoft.com/en-us/library/ff452127.aspx• http://

social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-text-functions.aspx

6/16/2012

http://www.powerpivot.com/download.aspx

http://powerpivotsampledata.codeplex.com/

http://technet.microsoft.com/en-us/library/ee835510.aspx

http://technet.microsoft.com/en-us/library/ff452127.aspx

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-text-functions.aspx





GOT QUESTIONS

?

6/16/2012


Thank YouDon’t forget to fill out your evaluations.

MICHAEL P. ANTONOVICH

[email protected] Book Site: http://sharepointmike.wordpress.com/

6/16/2012

mailto:[email protected]

do you have the time

Technology