tim laqua dot com Thoughts and Code from Tim Laqua

8Apr/140

jderef.com is dead – Create your own using SSRS and JDE metadata tables

ssrs-jderef-1

Anyone who has used jderef.com is certainly mourning its demise as JDE, like most ERP systems, is a bear to understand without a really big instruction book. Luckily you can chat with your JDE people and, given the correct libraries, modify the following query to get the same information:

SELECT
       RTRIM(TDOBNM)   AS TABLE_NAME,
       RTRIM(d.SIMD)   AS Table_Description,
       RTRIM(d.SIPFX)  AS Table_Prefix,
       RTRIM(TDOBND)   AS Data_Dictionary_Object_Name,
       RTRIM(TDSQLC)   AS SQL_Column_Name,
       RTRIM(b.FRDSCR) AS Column_Description,
       RTRIM(e.DRDL01) AS Field_Type,
       RTRIM(c.FRDTAS) AS Field_Length,
       RTRIM(c.FRDTAD) AS Field_Decimals,
       --RTRIM(c.FROWDI) as DD_Item,
	CASE 
		WHEN RTRIM(c.FROWER) = 'UDC' THEN 'UDC: RTRIM(DRSY)=''' || RTRIM(c.FROER1) || ''' AND RTRIM(DRRT)=''' || RTRIM(c.FROER2) || ''''
		ELSE 'Generic Edit'
	END AS Edit_Type,
       --RTRIM(c.FROWER) as Edit_Rule,
       --RTRIM(c.FROER1) as Edit_Rule_Parm1,
       --RTRIM(c.FROER2) as Edit_Rule_Parm2,
       RTRIM(TDPSEQ)   AS Program_Sequence_Number
FROM
        COPD910.F98711 a
        LEFT OUTER JOIN DD910.F9202 b
                ON a.TDOBND = b.FRDTAI
                   AND FRLNGP = '  '
                   AND FRSYR = '    '
        LEFT OUTER JOIN DD910.F9210 c
                ON a.TDOBND = c.FRDTAI
        LEFT OUTER JOIN OL910.F9860 d
                ON a.TDOBNM = d.SIOBNM
                   AND d.SIFUNO = 'TBLE'
        LEFT OUTER JOIN PRODCTL.F0005 e
                ON LTRIM(RTRIM(c.FROWTP)) = LTRIM(RTRIM(e.DRKY))
                   AND e.DRSY = 'H98'
                   AND e.DRRT = 'DT'
WHERE
        RTRIM(TDOBNM) LIKE ?
ORDER BY
        TDOBNM,
        TDPSEQ

ssrs-jderef-2

Sample RDL can be found here: http://timlaqua.com/jderef.rdl. You will need to change the data source to work in your environment as well as update the following libraries to your environment:

  • COPD910.F98711 - F98711 varies per environment, so make sure you get the right library
  • DD910.F9202 - all the F92 files are in the same library
  • PRODCTL.F0005 - whatever library your UDC table is in
  • OL910.F9860 - the Object Librarian library
Tagged as: , , No Comments
10Feb/130

Hanging The TV

One morning as we were eating breakfast, Lindy (my wife) looked over to the other room where we had a TV sitting on a sofa table and commented that it looked "terrible" because of all the wires all over the place. This is basically license to knock down the entire wall and do whatever you like as long as she can't see those wires when you're done. I stopped short of that and just pulled a little part of the wall off.

Here's what it looked like originally (the terrible wires photo):
1-Original

6Jan/131

Our Life Fitness X5 Elliptical: Part 1

I want to start off by saying that this is the greatest home elliptical I've ever used. I'm 6' and my wife is 5' 4" - the adjustable stride really goes a long ways (pun intended) towards allowing both of us to enjoy this piece of equipment. I'm not familiar with the Basic console, but I can attest that the Advanced console's auto-adjusting workouts are very handy at 6AM when you're really not interested in pressing buttons - or really thinking at all. This unit gets used almost daily (by Lindy - I fell off the wagon some years ago) and we've owned it for about 6 years now. Here are the stats on this mamma jamma:

DSCN0069[1]

DSCN0068[1]

We haven't had a SINGLE problem with it... until now. After our most recent move, to be exact. Now - we've been in our new place for about 4 months and this is the first time I noticed it was having an issue. Wait - but didn't I say it was used daily? Oh, it is. But Lindy is they type that doesn't really care about the flashing lights on the screen, she actually finds them to be distracting. So what she does is turns the unit on, hits all the buttons to get it in to manual mode, and lays a DVD case across the entire screen, cause she really doesn't care what it says. Well, I decided I should jump back on the wagon for some strange reason earlier today and my little auto adjusting program wasn't working. The Level area of the display simply says "OFF" and no amount of button pressing will wake it up.

DSCN0070[1]

So the only good news here is I get to give Lindy crap for always using the elliptical on Level 1 and being a slacker. Bad news, I gotta fix it since the warranty ran out like 3 years ago.

11Jun/120

SQL 2012: XMLA Change Connection Command Missing

I'm fairly sure this has to do with the options I chose when I first opened SSMS, where I asked it to import my settings. Regardless of why it happened, my Change Connection command is gone from my context menu when editing XMLA files:

I use this quite a bit as we have many environments and most of those environments also have staging environments. Here's now to manually add it (and add it to add it to any other context menus you want)

27Feb/124

Slowly Changing Dimensions with MD5 Hashes in SSIS

We recently moved away from the 3rd party Checksum component (and all 3rd party components) in SSIS and I wanted to share the pattern we settled on for maintaining our Type 1 Slowly Changing Dimensions (SCDs). There are two things we wanted to address with our new pattern. First, our previous implementation wasn't performing as well as we needed it to or generating reliable checksums. The second was that we wanted to get away from dependencies on custom assemblies in general. To illustrate the pattern, we're going to build a SCD package off the Adventure Works DW DimCustomer table and skip over the actual source of the business keys and attributes by selecting directly from the completed dimension for now.

First, we assume that our dimension already exists (and we were using some other checksum or MERGE to maintain it). We have to add a column to store the MD5 hash:

ALTER TABLE dbo.DimCustomer ADD
	MD5 VARCHAR(34) NOT NULL DEFAULT ''

Second, we need a staging table to store updated/changed rows. Script out the current dimension as a CREATE, remove all unneeded constraints and indexes, and create a staging table as a heap:

CREATE TABLE [dbo].[Staging_DimCustomer_UpdatedRows](
	[CustomerKey] [int] NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [datetime] NULL,
	[MaritalStatus] [nchar](1) NULL,
	[Suffix] [nvarchar](10) NULL,
	[Gender] [nvarchar](1) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) NULL,
	[SpanishEducation] [nvarchar](40) NULL,
	[FrenchEducation] [nvarchar](40) NULL,
	[EnglishOccupation] [nvarchar](100) NULL,
	[SpanishOccupation] [nvarchar](100) NULL,
	[FrenchOccupation] [nvarchar](100) NULL,
	[HouseOwnerFlag] [nchar](1) NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[AddressLine1] [nvarchar](120) NULL,
	[AddressLine2] [nvarchar](120) NULL,
	[Phone] [nvarchar](20) NULL,
	[DateFirstPurchase] [datetime] NULL,
	[CommuteDistance] [nvarchar](15) NULL,
	[MD5] [varchar](34) NOT NULL)

Now in to SSIS - We will be building:

  1. Execute SQL Task to Truncate our Staging table(s)
  2. Data Flow Task to Insert new rows and Stage updated rows
  1. OLE DB Source to retrieve our source data
  2. Script Component to Generate Row Numbers
  3. Conditional Split to Evenly Distribute Rows
  4. Script Component to Generate MD5 Hashes
  5. Union All to Squish it all back together
  6. Lookup to get the existing MD5 Hash (if it exists)
  7. Conditional Split to separate Unchanged and Changed rows
  8. RowCount Transformation
  9. OLE DB Destination for Changed rows
  10. OLE DB Destination for New rows
  • Execute SQL Task to Update changed rows
  • Completed Control Flow

    Completed Data Flow

    24Jul/1122

    Troubleshooting: 1998 Jeep Grand Cherokee (ZJ) Doesn’t Start – Cranks, No Fuel, No Spark

    This has been one of the most frustrating repairs we have come across. One day the Jeep just plain refused to start anymore. We have had prolonged starting times for a while now where we will let fuel pump prime at least twice before it would start, but it would always start. Nothing really precluded the no start condition, we just turned it off one day and it didn't want to start again. The symptoms don't really point to any particular component (other than the PCM) and without some fancy tools, you'll do exactly what we did - replace the PCM... and it won't fix anything. So we took out our fancy tool, looked up some diagnostic diagrams on alldatadiy.com, and got to work.

    A few things to check for right away to see if you're having the same issue we were:

    • Check to see if the fuel pump runs during cranking - ours didn't
    • Check for spark during cranking - we didn't have any
    • Check to see if your check engine light is on (ours WASN'T)
    • Check your voltage and fuel gauges with the ignition on - ours didn't register anything
    • Check to see if your low fuel light is on - our was
    28Nov/0819

    BlackBerry Storm (9530) Error: SIM Card Rejected

    This error pops up when you lose CDMA (1XEV) connectivity in the US and the phone attempts to connect to a GSM network (you can duplicate if you force your Storm to GSM network technology) and you have a Global SIM card installed (and most of us do). The kicker is that the message "SIM Card Rejected" doesn't go away until your hard reboot - even after you've reconnected to the CDMA network.

    So, if you're not using your Global SIM (if you are, call VZW and ask for Global Support - they can activate your SIM), you can just force CDMA only on the phone, do a battery pull, and you'll never see this again. I assume some future firmware update will handle this error better by making it go away once you're back on CDMA, but for now - this is what I had to do:

    16Nov/081

    Installing ASTrace.exe in a 64-bit environment

    ASTrace.exe is part of the SQL Server Analysis Services Community Samples on CodePlex. This utility works just fine in a 32-bit environment, however some of the assemblies needed to interact with Analysis Services do not have 64-bit counterparts - thus, you have to use WOW64 to get the service to work properly on a x64 machine.

    ASTrace can be downloaded here:
    http://www.codeplex.com/SQLSrvAnalysisSrvcs/Release/ProjectReleases.aspx?ReleaseId=13572

    32-bit Installation Instructions can be found here:

    http://msdn.microsoft.com/en-us/library/bb283156(SQL.90).aspx

    And to get it to work on x64, you need to do two things differently:

    • Target x86 processors when you build the application - this way, your 64-bit OS will use WOW64 when the service runs (Project > ASTrace Properties... > Build Tab).

    • Create the following registry key and string value pointing to where you copied the ASTrace files:
      [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\ASTrace]
      "path"="C:\\ASTrace"
    5Nov/080

    How to add a calculated measure (calculated field) to an Excel 2007 PivotTable with a SSAS data source

    As it turns out, you can do it programatically as explained by Allan Folting here:
    Microsoft Excel: Common Questions Around Excel 2007 OLAP PivotTables:
    http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

    And the parts that I have to keep looking up (I use my blog as a notebook for things I don't want to forget ;-):

    Sub AddCalculatedMeasure() 
    	Dim pvt As PivotTable
    	Dim strName As String
    	Dim strFormula As String 
     
    	Set pvt = Sheet1.PivotTables("PivotTable1")
    	strName = "[Measures].[Internet Sales Amount 25 %]"
    	strFormula = "[Measures].[Internet Sales Amount]*1.25"
    	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember 
    End Sub 
     
    Sub AddCalculatedMember() 
    	Dim pvt As PivotTable
    	Dim strName As String
    	Dim strFormula As String 
     
    	Set pvt = Sheet1.PivotTables("PivotTable1")
    	strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]"
    	strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25"
    	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
    	pvt.ViewCalculatedMembers = True 
    End Sub 
     
    Sub AddNamedSet() 
    	Dim pvt As PivotTable
    	Dim strName As String
    	Dim strFormula As String
    	Dim cbf As CubeField 
     
    	Set pvt = Sheet1.PivotTables("PivotTable1")
    	strName = "[My Mountain Bikes]"
    	strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
    	pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
    	Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes") 
    End Sub

    He also mentions that you can expose these members to Excel Services 2007 by creating the new objects and then removing the VBA code - very useful article.

    14Sep/08258

    How to replace the battery in a Polar F11 Heart Rate Monitor (HRM)

    Disclaimer: The user manual for the Polar F11 clearly states to take the HRM to a Polar dealer and have them replace the battery - taking the back off could void your warranty. If you wish to be 100% sure that you maintain the water-resistant seal and do not want to void your warranty - or risk breaking your HRM (certainly a possibility when you take it apart...), take the unit to an authorized Polar dealer and have them replace the battery.

    Step 1: Remove the back cover (4 little screws - make sure you have the right size screwdriver or you'll strip them out). Also note that the back has a water-resistant seal - keep this clean and remember that there is always a chance that the seal may be damaged if you do this yourself.
    Step 2: Remove the internal unit from the watch casing (it just pulls out).
    Step 3: Get something to pry the battery latch tab with (I used another small screwdriver)
    Step 4: Take whatever you used to pry the latch tab out with and use it to pry the battery up by placing the device (screwdriver in my case) in the notch above the latch (see picture).
    Step 5: Go to Wal-Mart and purchase a new CR2032 watch battery (you may want to do this before hand... I didn't know what battery it used so I had to take it out first) - runs about $3.50.
    Step 6: Put the battery back in and put everything back together.