#Md5 encoding in sql codeRemoving the navigation step and looking at the column of List object placeholders… I want to modify the M code for this step to do the following: If you were to add this column in the query design and then scroll on over to the new column, you’d see that it shows up as a List object placeholder, just all waiting for you to click the magic link that navigates to the list of all the characters in the column string. I created a calculated column and entered something like this: At that point, you have a collection of characters to have your way with. The first step was to tear each string down into a List object. It did, however get me thinking about the problem a certain way and I figured it out. But, the sample code didn’t resolve my issue entirely. The post is here. It was educational and sent me in the right direction. This post didn’t entirely address my scenario but did give me a foundation to figure the rest out on my own. Reza provides several good examples of List manipulation pulling lists apart and putting them back together. He helped me understand the mechanics of the List.Accumulate function, which is really powerful. Reza has an extensive post about parsing and manipulating lists. Running low on options, I came across Reza Rad’s December, 2017 blog post and found Mecca. I have an e-copy of Chris Webb’s book – somewhere – and I know he eats and breathes this kinda stuff. I have a paper copy of Ken Puls book where he mentions List.Splitter, which seemed promising. Most official docs online weren’t very helpful. Yes, I Googled it (I actually used Bing) and found several good resources. Let’s see… that’s probably done with a List object but what method and where do I find the answer? It’s Off To The Web, Batman! I knew that to convert each character one-at-a-time, I would need to bust off each string into a list of characters. Wouldn’t it be nice if I could convert each UserName string to a numeric representation of each character (which would be different for each upper or lower case letter). #Md5 encoding in sql how toIn the Phase Two (production-ready) solution we will generate surrogate keys to define uniqueness but in this version, created with Power BI Desktop, I have to figure out how to make the same user name strings, with different upper and lower-case combinations, participate in relationships and serve as table key identifiers. #Md5 encoding in sql softwareNo right-minded application developer on this planet or any other should have let that happen but since their dink-wad software produces this data, we have to use it as it is. If there were two users named “Bob Smith” that were setup with login user names of “BOBSMITH” and “BobSmith”, that was perfectly acceptable per the rules enforced in the application. I had to ask the business user to repeat that and I had heard it right the first time. Today I learned that the user login names stored in one of the source systems, which we will use to uniquely identify system users, allows different users to be setup using the same combinations of letters as long as the upper and lower case don’t match. Our project is in Phase One and we’re cutting corners all over the place to get reports done and ready to show our stakeholders. We agree to learn what we can from this experience, salvage what we can from the first phase project and then we adhere to proper design rules, using what we learned to build a production-ready solution in Phase Two. Part of the negotiation is that we might use self-service BI tools to bend or even break the rules of proper design the first time through. This gets stakeholders and folks funding the project on-board so we can get the support necessary to schedule and budget the more formal, production-scale long-term business solution. What that means in a few words is that we rapidly work through a quick design, building a functional pilot or proof-of-concept to produce some reports that demonstrate the capability of the solution. I’m going to step out of the frame just a moment to make a soapbox speech: I’m a believer in two-phase Business Intelligence project design. Here’s the story… A client of mine is using Power BI Desktop to munge data from several different source systems to create analytic reports. We needed to convert application user names into an encoded format that would preserve case sensitive comparison. I worked through a brain-teaser on a consulting project today that I thought I’d share in case it was useful for someone else in the community.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |