#MiddleInitials

[moraloreldeservedbetter]

OK I HAVE ANOTHER IMPORTANT QUESTION FOR Y'ALL

what character in a different piece of media has clay puppington vibes

they don't have to act like clay

but just by appearance

for example i think pimply paul from horrid henry has clay puppington energy

hell i call him british clay puppington for crying out loud

[icanfixthempolls]

[deckond]

killing myself

[sparkl3w00fzz]

more random clay doodles bcuz im stuck with traditional art </3

[qarker]

clay middleinitial puppington

[tinyattack09]

ALLL MORAL OREL FANS

you wanna see a fix where Clay is confronted by literal god???

Here it is!!!

Beseech

After the Danielle incident and the hunting tragedy, Clay Puppington receded to his study most of the time. Making no attempt to change, he would waste his days drinking and having an affair with both Censordoll and Stopframe. But, we all know that the lord giveth, and the lord taketh away.

“Clayton Middleinitial Puppington!”

“G-…God!?”

“It is I, the Heavenly Father.” His voice shook the room, and boomed into his body.

“Wha-…Why have you visited me, Lord?” Clay said, clearly in a daze.

“You have a lesson to learn, Clayton. You give your own

lessons and stories while convoluting their meaning to fit your way of destructive, abusive life. It is time that you learn, mortal. The Lord’s mercy only reaches you if it remains pure.”

A sword of pure light appeared. As the Lord held it, the power

changed its hue.

“Lord, what are you doing!?” Clay stepped back. He knew that

he was in trouble, and he couldn’t bribe this one to go away. The sword was raised, and all the light in the room was focused only on Clay.

“What is necessary to keep my world pure.”

“Why…?!”

"Why!? Pathetic. Unable to realize thy faults. Let us see your life, and what it really was."

Everything fades. There is a young boy holding a gun. It shakes in his hands as he struggles to bear such a deadly tool.

"It's a tradition in the Puppington family that the head of the household would pass off this gun to the eldest son."

"Wow, dad… But I don't think I could kill an animal. That seems too mean."

"Son, nature was made to be hunted. We get to 'play god.'"

“Play God?? Golly Pops… that doesn’t sound very right…

You know the sixty-third commandment! ‘Thou shalt never hold a gun without anything to shoot at!’ And we have to follow God’s rules!”

The memory switches to a young Puppington, holding that

same gun, while ketchup was spewed all over the child’s body.

“Clay! Clay! Clayton! Open your fucking eyes!” Clay’s body was shook violently.

“What?? I didn’t do anything!” Clay springs back to life, having

pretended he was dead. His mother then cried out

“Oh thank the heavens! Lord, thank you-“ She froze in the

middle of her sentence. She fell to the floor, her heart unresponsive.

“Mom!” Clay ran to his now dead mother, understanding what he did.

“Get back!” Clay’s father pushed him back, slamming him to a wall. No CPR could be done, and Amanda Puppington was declared dead. The memory fades to black.

Clay was frozen.

“I-… I was so young, Father! I didn’t know what I was doing! I was only six years old!”

“Then let us watch something more… recent, yes?” The memory flashes to the wilderness. Clay is an adult, and stands there with his twelve year-old son, Orel.

“Dad… I think you might be *too* drunk.”

“I… Let me tell you something, Orel! Drunk is nature.”

“I’m not really comfortable hunting with you, Dad.” Orel is tense, gripping to the log he’s sitting on.

“You aren’t comfortable hunting with me? Ever tried hunting with you!?” Clay gulps down his liquor like it’s the last bottle of water in the desert.

“Y’know kid, your cup is always half empty. Look at me. You should be more like your old man and look on the blight side of life.”

“B-Blight?” Orel said, shivering from his fear.

“No, I didn’t say ‘bright,’" Clay interrupted, "I said blight. My life is sunny and blight. ‘Bright’ means the opposite — it means sudden withering death. And that’s… not… Oh, who am I kidding, my life is full of bright…”

“Dad..?” Orel replied, terrified that his father would lose composure.

“Oh, God.”

“What’s happening, Dad!?” Orel cried.

"Oh, I hate myself…”

He runs out of liquor in the bottle. He stares at his reflection, silently hoping that it would be the last time he’d have to see it.

“Why do you quit working on me!?!”

Everything goes silent. No birds chirping, no crickets, no owls in the night.

“She always fools me, Orel. ‘I’ll make things better dear! Drink me! Put me inside of you, I’m great!’ And then she chokes me just like every other whore out there!! They’re all worthless, kid! Every woman. Don’t let ‘em get ya! All of them just wanna get ya! They just grab you and pull you into ‘em! And then you’re forced to stay in, pull out. Stay in, and pull out!! And then they cut ya! And they grip ya by the… right where it counts! And then they start SQUEEZING things out! Things that are like weights around your head! You’re stuck there for the rest of your life, with NOWHERE to go and NO ONE to be!!! AAAAGH!!!”

The scene faded out.

"What's so bad about that?! It's useful advice!" Clay exclaimed, deluded by his own prejudice.

"Just wait, mortal."

The scene reappeared. It opens to clay and Orel sitting across from each other, with clay in a drunken stupor. Orel is now as far away from Clay as he can be while still on a log.

"It's time you became a man. Where's my rifle!?" Clay yells, searching for it/

"Dad, I don't think-"

"There it is! No mistakes, no accidents, no fuck-ups, no blunders."

"Dad! W-What are you doing?!"

"Somethin' important!!"

A single, lone spark flew. Following it was an ear-splitting bang. No noise could be heard after that, other than the ringing going through both of their ears.

"D-...Dad…"

The scene fades for the final time.

Clay was frozen.

"Do you realize thy fault now? Are you able to comprehend the weight of your sin?! Do you finally see that your actions have consequences, Puppington!?" The ethereal voice boomed. Its volume was so loud that Clay was shaken back into reality.

Clay was speechless. Instead of pleading for his case, he simply stood there thinking. He didn't need to speak, for his god could tell exactly what he was thinking.

I couldn't have done that. But I did. How? Why? What do I do!? What do I do?!

But then a thought hit him.

Just give up. You've been so dedicated to your Lord your entire life. Give everything to Him.

"Please…" Clay said, hopeless and weak. Clay then fell to his knees. He didn't dare to raise his head, or so much as stand up.

"Please what?" The deity spoke. Tears began to roll down Clay's cheeks as he remained there kneeling before his God.

"Please… Have mercy," he muttered. He was too weak to raise his voice.

"Forgive me, Lord. Forgive me. I'm weak without you, God."

"You beseech for something you could not give to your own, Clayton. The kingdom of heaven does not allow those like you."

The sword of light raised up once more, as Clay said his final vow.

"I'm sorry, Orel."

A scream rang out. The scream of a weak, hopeless, self-destructive, and miserable young boy. The light disappeared, and all that was left was a soul, going to neither heaven nor hell. The soul was trapped in the mortal world, and had a chain around its neck.

Suddenly, Orel walked into the study. As he stepped further, something came into view.

The end of a chain that led directly to his neck.

RB AND LIKE I SPENT SO MUCH TKMD ON THIS OKAY BYE

[sharingknowledgewithmouli]

Excel Name Splitting Made Simple: Using Text to Columns and Flash Fill or Ctrl+E

[moraloreldeservedbetter]

how’d clay feel off to you in beforel orel?

I don't know how to explain it?? he just felt weird ya know?? like the character was there but his personality wasn't

i make sense i swear to god

[myfootyrthroat]

I've been having an ongoing paperwork problem for 3 weeks despite several phone calls and emails. Today's call was... a lot.

First, the guy who answered couldn't find me in the system at all. When we finally managed to get me pulled up, he goes "Oh, your birthday is Month Day Year, you said Month Day (Wrong year, but only a syllable apart). Just remember that for next time."

Buddy, do you think it's more likely that I forgot my own birthday or that you just misheard me?

Then the next person asks if I have a middle name, and I respond "Yeah, it's MiddleName." and she goes "Oh, your paperwork was submitted under Firstname MiddleInitial Lastname, but the mailing address said Firstname MiddleName Lastname. Now that we've confirmed it's the same person, I'll start processing it."

It took THREE WEEKS to get to that. THREE WEEKS.

[tolerhate]

regress SDKSKDKSDKSKDKSDKSKSK?!??!!?!?!

SEND ‘REGRESS’ TO MEET MY MUSE AS A CHILD !

         ❝   oh, boy !  ❞     barely an inch away from the other boy ( if his mother saw him right now ,  she’d have a heart attack ;  he can even hear her voice loud and clear : “ clay middleinitial puppington !  what if you catch germs !! stay at least an arm’s length ! ” ) ,  he can’t quite contain the excitement & lets it roll off the tip of his tongue .   ❝   i’ve never met another kid before …  nice to meet you !  my name’s clay …  clay puppington !  wanna play ?? ❞

[obscurepopref]

Kind of obligated to take this one. #middleinitial #thriftshopfinds #thriftshopping

[devotedlyannoyingcheesecake]

For less space consumption, I threw all the challenges into a single file. I kept it simple because the purpose of writing this was not to work on error handling.

import java.util.*; public class Chapter2 {    private static Scanner keyboard = new Scanner(System.in);    private static void challenge1(){        String name = "Nicole Binette";        int age = 21;        double annualPay = 100000;        System.out.println("My name is " + name + ", my age is " + age + " and I hope to earn $" +                annualPay + " per year.");    }    private static void challenge2() {        String firstName = "Nicole", middleName = "Amporn", lastName = "Binette";        char firstInitial = 'N', middleInitial = 'A', lastInitial = 'B';        System.out.println(firstInitial + " " + middleInitial + " " + lastInitial);        System.out.println(firstName + " " + middleName + " " + lastName);    }    private static void challenge3() {        System.out.println("Nicole" + "\n16410 NE 99th ST Redmond, WA 98052" + "\n425-951-9256" +                "\nComputing and Software Development");    }    private static void challenge4() {        System.out.println("\t\t\t*");        System.out.println("\t\t   ***");        System.out.println("\t\t  *****");        System.out.println("\t\t *******");        System.out.println("\t\t  *****");        System.out.println("\t\t   ***");        System.out.println("\t\t\t*");    }    private static void challenge5() {        double salesDivisionsPercentage = 0.62;        double salesEstimatedGeneration = 4.6 * 0.62;    }    private static void challenge6() {        double acreInSquareFeet = 43560;        double numAcres = 389767 / acreInSquareFeet;    }    private static void challenge7() {        System.out.println("Enter purchase amount.");        double amount = keyboard.nextDouble();        double stateSalesTax = 0.04;        double countySalesTax = 0.02;        double totalSalesTax = stateSalesTax + countySalesTax;        double total = amount * totalSalesTax + amount;        System.out.println("amount: " + amount);        System.out.println("state tax: " + stateSalesTax);        System.out.println("county tax: " + countySalesTax);        System.out.println("total tax: " + totalSalesTax);        System.out.println("total: " + total);    }    private static void challenge8() {        double cookiesPerBag = 40;        double servingsPerBag = 10;        int calPerServing = 300;        System.out.println("Enter number of cookies eaten.");        double numberOfCookiesEaten = keyboard.nextDouble();        double totalCalories = numberOfCookiesEaten * (servingsPerBag / cookiesPerBag) * calPerServing;        System.out.println("Calories consumed: " + totalCalories);    }    private static void challenge9() {        System.out.println("Enter number of miles driven.");        double milesDriven = keyboard.nextDouble();        System.out.println("Enter gallons of gas used.");        double gallonsOfGasUsed = keyboard.nextDouble();        double mpg = milesDriven / gallonsOfGasUsed;        System.out.println(mpg);    }    private static void challenge10() {        System.out.println("Enter test scores separated by a space."); //Indicate the number of tests with that score with x. For example, 3 tests with a perfect score would be 100x3");        String[] tests = keyboard.nextLine().split("\\s");        Map<Double, Integer> testScores = new HashMap<>();        for (String string : tests) {            String[] test = string.split("x");            testScores.put(Double.parseDouble(test[0]), Integer.parseInt(test[1]));        }        Object[] scores = testScores.keySet().toArray();        Object[] numberOfTestsPerScore = testScores.values().toArray();        double total = 0;        int numberOfTests = 0;        for (int i = 0; i < scores.length; i++) {            total += (Double) scores[i] * (Integer) numberOfTestsPerScore[i];            numberOfTests += (Integer) numberOfTestsPerScore[i];        }        double average = total / numberOfTests;        System.out.println("average test score " + average);    }    private static void challenge11() {        System.out.println("Enter the retail price for a circuit board.");        double retailPrice = keyboard.nextDouble();        double percentProfit = 0.4;        double profit = retailPrice / percentProfit;        System.out.println("profit " + profit);    }    private static void challenge12() {        System.out.println("Enter the name of your favorite city.");        String favCity = keyboard.nextLine();        System.out.println(favCity.length() + " " + favCity.toUpperCase() + " " + favCity.toLowerCase() + " " + favCity.charAt(0));    }    private static void challenge13() {        double taxPercent = 0.0675;        System.out.println("Enter the price for the meal.");        double mealCharge = keyboard.nextDouble();        double mealChargeWithTax = mealCharge + taxPercent * mealCharge;        double tipPercent = 0.2;        double totalMealCharge = mealChargeWithTax + tipPercent * mealChargeWithTax;        System.out.println("total meal charge " + totalMealCharge);    }    private static void challenge14() {        System.out.println("Enter the number of males and females in registered for a class. Like so M F");        String[] maleAndFemale = keyboard.nextLine().split("\\s");        int male =  Integer.parseInt(maleAndFemale[0]);        int female = Integer.parseInt(maleAndFemale[1]);        int totalRegistered = male + female;        double percentMale = ((double) male / totalRegistered) * 100;        double percentFemale = ((double) female / totalRegistered) * 100;        System.out.println("percent male " + percentMale + "%" + "\npercent female " + percentFemale + "%");    }    private static void challenge15() {        int numOfStockShares = 600;        double pricePerShare = 21.77;        double percentCommission = 0.02;        double priceForStock = numOfStockShares * pricePerShare;        double commission = priceForStock * percentCommission;        double totalPrice = priceForStock + commission;        System.out.println("total price for stocks including commission " + totalPrice);    }    private static void challenge16() {        int customersSurveyed = 12467;        double percentConsuming1plus = 0.14;        double percentPartialToCitrus = 0.64;        double numPurchasing1plus = customersSurveyed * percentConsuming1plus;        double numPartialToCitrus = customersSurveyed * percentPartialToCitrus;        System.out.println("number of customers who purchase 1+ energy drinks per week " + numPurchasing1plus);        System.out.println("number of customers who are partial to citrus flavored energy drinks " + numPartialToCitrus);    }    private static void challenge17() {        // cookie recipe        double cupsSugar = 1.5;        double cupsButter = 1;        double cupsFlour = 2.75;        int makesCookies = 48;        System.out.println("Enter number of cookies you wish to make.");        int toMakeCookies = keyboard.nextInt();        double quotient = (double) toMakeCookies / makesCookies;        // adjust ingredients        System.out.println("cups of sugar " + cupsSugar * quotient + "\ncups of butter " + cupsButter * quotient +                "\ncups of flour " + cupsFlour * quotient);    }    private static void challenge18() {        System.out.println("Enter your name, your age, the name of a city, the name of a college, a profession," +                " a type of animal," +                "and a pet's name.");        String[] words = keyboard.nextLine().split(",\\s");        System.out.println("There once was a person named " + words[0] + " who lived in " + words[1] +                ". At the age of " + words[2] + ", " + words[0] + " went to college at " + words[3] +                ". " + words[0] + " graduated and went to work as a " + words[4] + ". Then, " + words[0] +                "adopted a(n)" + words[5] + " named " + words[6] + ". They both lived happily ever after!");    }    private static void challenge19() {        int numberOfShares = 1000;        double pricePerShare = 32.87;        double percentCommission = 0.02;        double commission = (numberOfShares * pricePerShare) * percentCommission;        double total = numberOfShares * pricePerShare + commission;        double salePricePerShare = 33.92;        double saleCommission = (numberOfShares * salePricePerShare) * percentCommission;        double totalSale = numberOfShares * salePricePerShare + saleCommission;        double profit = totalSale - total;        System.out.println("profit " + profit);    }    public static void main(String args[]) {        challenge1();        challenge2();        challenge3();        challenge4();        challenge7();        challenge8();        challenge9();        challenge10();        challenge11();        challenge12();        challenge13();        challenge14();        challenge15();        challenge16();        challenge17();        challenge18();        challenge19();    } }

[webdesignersolutions]

I am a recently graduated mechanical engineer, and I am putting together a personal site for an online resume (and just cause). I’m looking for opinions on names.

I’m looking at:

firstname<middleinitial>lastname.com

OR

firstnamelastname.me

I’m in the US, not Montenegro, so there’s that. I like the double meaning of ME (mechanical engineering), but I’m not sure of the popular perception of .me domains.

As a corollary, I might be able to get firstnamelastname.com in the future. If I can, I’d probably keep that and the .me. Anyone have an opinion on .me vs .com for a personal website. Given that I’m an ME, and the fact that it is a personal, rather than business site?

Thanks in advance, guys!

Submitted January 29, 2020 at 01:40PM by sitruC_Acid https://www.reddit.com/r/webhosting/comments/evsv50/personal_websiteportfolio_domain_name/?utm_source=ifttt

from Blogger http://webdesignersolutions1.blogspot.com/2020/01/personal-websiteportfolio-domain-name.html via IFTTT

[kagaya25]

You can use multiple conversion specifiers in a single printf() function - C Data Type

You can use multiple conversion specifiers in a single printf() function – C Data Type

Description

You can use multiple conversion specifiers in a single printf() function

#include <stdio.h> int main() { char firstInitial, middleInitial, lastInitial; firstInitial = 'M'; middleInitial = 'A'; lastInitial = 'V'; printf("My Initials are %c.%c.%c.", firstInitial, middleInitial, lastInitial); return 0; }

Output:

My…

[moraloreldeservedbetter]

idk i liked beforel orel it gave some depth and insight to clay and his dad's relationship. admittedly, im not as attached to the show as u might be, so i can only assume it feels off the way equestria girls was "all wrong" to the mlp canon in 2013

well yes but also no??

i wasn't in the mlp fandom until 2014 so i was more exposed to equestria girls then mlp

i think the reason the special felt off was because of clay

i swear i make sense

[powerbitraining]

Using Relationship Columns to Kickstart your Data Modelling

(In this blog post/tutorial, we will use a database called “Event Sales”, which you can download here. If you wish to follow along, you will need to download and UnZip the .BAK file, then use it to restore the EventSales database on an instance of SQL Server.)

When connecting to a relational database from Power BI, it is often the case that you wish to rationalize and flatten the structure of the original database to make it more suitable for reporting purposes; and there are several possible workflows which will help you to achieve this goal.

The Ideal Workflow

Before we get to the use of relationship columns, the main topic of this post, I should stress that it is a feature which is used in “plan B”, which only kicks in when you do not have read/write access to the database to which you are connecting. If you are a DBA of the database in question, then you should use “plan A”, the ideal workflow; so, let’s start by summarizing this ideal workflow.

If you are the DBA of the database to which you want to connect, the ideal workflow is to create a series of views, on the database server; each of which will become a table in the Power BI data model. To illustrate this process, and to keep things simple, we will start with a relational database consisting of 11 tables and flatten this structure down to 4 tables.

In SQL Server Management Studio, let’s look inside the EventSales database which, hopefully, you have downloaded and restored; and, let’s examine the structure of the database by expanding the Database Diagrams folder and double-clicking the dbo.Default diagram.

The database contains 11 tables which are listed below, in alphabetical order.

Categories (CategoryID, WorkshopCategory)

Clients (ClientID, Title, FirstName, MiddleInitial, Surname, Gender, StreetAddress, City, Region, PostalCode, CountryCode)

Countries (CountryCode, CountryName, DialingCode, CountryZoneID)

EventsClients (EventID, ClientID, Revenue, Rating)

EventsData (EventID, EventID, StartDate, CourseID, VenueID)

EventsTutors (EventID, TutorID, TutorFee)

Testimonials (TestimonialID, ClientID, DateFilmed, VideoURL)

Tutors (TutorID, Title, FirstName, MiddleInitial, Surname, Gender, StreetAddress, City, Region, PostalCode, CountryCode, EmailAddress)

Venues (VenueID, VenueName, StreetAddress, City, Region, PostalCode, CountryCode, Latitude, Longitude)

Workshops (CourseID, Duration, CourseName, CategoryID)

ZoneID (ZoneID, Zone)

Now, let us compare this structure with that of the data model which we want to create from the database, in Power BI.

In the data model, we have six tables, only five of which have been imported from the original database:

Clients (First Name, Surname, Gender, City, Region, Postal Code, Country, Zone, Testimonial Date, Testimonial URL)

Events (Event ID, Start Date, Workshop ID, Venue ID, Tutor ID, Tutor Fee, Client ID, Revenue, Rating)

Tutors (Tutor ID, First Name, Surname, City, Region, Postal Code, Country, Zone)

Venues (Venue ID, Venue Name, City, Region, Postal Code, Latitude, Longitude, Country, Zone)

Workshops (Course ID, Duration, Course Name, Category).

The final table in our data model, Dates Table, is a dynamic DAX table created in Power BI:

Dates Table (Date, Year, Month Num, Month)

(Almost all data models in Power BI require a date table containing all the different date and time related categories by which you want to categorize your data.)

This reduction in the number of tables from database to data model is typical of Power BI workflows. As our focus shifts towards reporting and analysis, we denormalize the relational database and introduce a certain amount of redundancy in order to facilitate data analysis.

For example, in the database, since only a small percentage of our clients provide a testimonial, we had a separate Testimonials table which had a one-to-one relationship with the Clients table. In the data model, the key testimonial fields (Testimonial Date and Testimonial URL) have been brought into the Clients table, and Testimonials no longer exists.

As you probably know, the table structure inside our data model is referred to as a star schema. It consists of a single main table, referred to as a fact table; and several supporting tables, all linked to the central fact table, which will be used to categorize the data in the fact table, and which are referred to as dimension tables.

To convert the original relational database structure into a star schema, the EventsClients, EventsData and EventsTutors tables have been combined to form a single fact table called Events. Workshops and Categories have been combined into a single dimension table called Workshops. And the information in the Countries and Zones tables has been imported into the Clients, Tutors and Venues tables, which constitute the remaining dimension tables derived from the original database.

When importing data from SQL Server, you always need to think ahead to the data model you wish to create and devise a strategy which will facilitate its creation. In general, the most efficient approach is to avoid connecting directly to the tables in your relational database. Instead, you can create a series of views, each corresponding to one of the tables which will be present in your data model. In Power BI, you then connect to these views rather than to the original database tables.

Taking this approach makes it easier to update your Power BI data models if modifications are made to the database tables or to the database structure. It also means that you will not have different teams of people, making the same, possibly complex changes to their Power BI datasets to create similar data models.

If most of the transformational logic takes place when the views are created, and report creators connect to the views, all you need to do is to synchronize changes to the database with your views; a much more straightforward task than attempting to synchronize changes to the database with multiple Power BI reports.

In SQL Server Management Studio’s Object Explorer, expand the Views folder. Inside, you will find the five views which can be imported straight into Power BI. For convenience, all these views have been placed inside a schema called “PBI_Sales”.

If you expand the Views section and drill down into the columns, you will notice that the columns have also been given report-friendly names; for example, “Course Name”, rather than “CourseName”.

In a blank Power BI file, let us now import these views. To import data from a SQL Server database, choose Home > Get Data > SQL Server. When the SQL Server connection dialog appears, enter the name of your SQL Server instance in the Server field. (To save typos, you can right-click on the server name in the Object Explorer, choose properties, then copy the entry in the Name field.)

You do not need to enter a name in the Database field; since, if the database field is left blank, when you click the OK button, you will be given access to all the databases on the server. This is particularly useful if you have databases with similar names; since recognition is usually easier than recall.

Let’s set the Data Connectivity mode to Import though this will have no impact on the features we are discussing.

Click on the Advanced options button and make sure that that Navigate using Full Hierarchy is checked.

This causes Power BI to display all objects in easily recognizable groups, making it much easier to find stuff.

Click OK.

Authentication Mode

When you connect to SQL Server, you need to specify the authentication mode you wish to use. There are three options: Windows, Database and Microsoft account.

The default selection, Windows, should be selected if you wish to connect using Windows authentication. If you performed an install of SQL Server Express using the default options, you will be able to connect to the database using your Windows login credentials. Simply leave the default Windows > Use my current credentials activated, as shown below.

In most production scenarios, you will connect using SQL Server authentication. After you select this option, specify a user name and password to connect to your SQL Server instance.

Click the Connect button to advance to the next screen.

If you receive a message regarding encryption (“We were unable to connect to the data source using an encrypted connection. To access the data source using an unencrypted connection, click OK.”), you can click OK to dismiss it. By default, Power BI attempts to encrypt the connection; clicking OK deactivates this default option.

In the Navigator window, use the arrows on the left to expand, first, the SQL Server instance, then EventSales > PBI_Sales (the name of our view). Click in the checkboxes next to each of the five views to activate them, then click the Load button.

Once the data has been loaded into Power BI, click on the Relationships button on the left of the screen, and you will see that Power BI has automatically created relationships between each of the four dimension tables and the main fact table, Events.

Note that this has only happened because of the naming convention we have used: for example, “Client ID” in the Clients table has been associated with “Client ID” in the Events table, and so forth.

Connecting to Database Tables Using SQL Statements

We have seen that, when connecting to SQL Server, it is best to create views and then connect to your views from Power BI, in preference to the underlying tables. However, this may not always be possible; for example, if you are not the DBA of the SQL Server instance, you may not have the authority to create all the required resources.

One way of proceeding in such situations is to use SQL statements. The same statements that you would use to create views in the SQL Server database can be executed from within Power BI.

For example, let us use an SQL statement to combine the three transactional tables (EventsClients, EventsData and EventsTutors) into a single fact table, as we did when creating views, we could execute the following SQL Statement.

SELECT Sales.EventsData.EventID ‘Event ID’ , Sales.EventsClients.ClientID ‘Client ID’ , Sales.EventsClients.Revenue , Sales.EventsClients.Rating , Sales.EventsData.StartDate ‘Start Date’ , Sales.EventsData.WorkshopID ‘Workshop ID’ , Sales.EventsData.VenueID ‘Venue ID’ , HR.EventsTutors.TutorID ‘Tutor ID’ , HR.EventsTutors.TutorFee ‘Tutor Fee’ FROM Sales.EventsClients INNER JOIN Sales.EventsData ON Sales.EventsClients.EventID = Sales.EventsData.EventID INNER JOIN HR.EventsTutors ON Sales.EventsData.EventID = HR.EventsTutors.EventID

To create the data model which we discussed in the previous section, we could proceed in this manner, executing a series of SQL statements equivalent to those used for creating the views which we imported earlier.

However, if we were to do this, we would be disabling a very useful Power BI feature, called query folding, which we will discuss shortly. So, the bottom line is, when connecting to a SQL Server (or similar) database, executing raw SQL statements is the least desirable approach.

Leveraging Query Folding and Relationship Columns

Query folding is the process whereby some, or all, of the transformations you define in the Query Editor are converted into SQL statements and executed by the database server. This can provide a significant performance boost each time the data source is refreshed. However, only SQL statements automatically generated by Power BI are permitted; if you execute your own SQL statement; or, if you use a Query Editor transformation which has no SQL equivalent, no further query folding takes place.

So, let us look at how we might create the Events fact table that we discussed earlier, incorporating the main columns from the EventsData, EventsClients and EventsTutors tables, while taking full advantage of query folding and the relationship columns feature.

Choose Home > Get Data > SQL Server. In the SQL Server database dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

What we will do is to import only the EventsData table; and then use a feature called Include relationship columns, to add the required columns from the related EventsClients and EventsTutors tables. (The Include relationship columns feature uses query folding.)

To understand how Include relationship columns works, let us begin by deactivating it. It is activated by default; so, click on the arrow to reveal the Advanced options section and click in the checkbox to deactivate Include relationship columns.

When the Navigator window appears, click in the checkbox next to the Sales.EventsData table. Look at the preview which appears on the right of the dialog, and you will notice that only the four columns which the table actually contains are displayed. This is what we might call normal behaviour.

Now, press Cancel, so that we can contrast this result with the one produced when Include relationship columns is activated.

Choose Home > Get Data > SQL Server once more. In the SQL Server connection dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

Click on the arrow to reveal the Advanced options section and, this time, click in the checkbox to reactivate Include relationship columns.

Click OK and, when the Navigator window appears, click in the checkbox next to the Sales.EventsData table. This time, on the right of the dialog, you will notice that in addition to the four columns which the table actually contains (EventID, StartDate, CourseID, VenueID), four extra columns are displayed (HR.EventsTutors, HR.Venues, Sales.EventsClients, and Sales.Workshops). (You will need to scroll to the right to see these extra columns.)

These are the relationship columns. Notice how the names of the columns correspond to the names of the four tables which are directly linked to the Sales.EventsData table.

When Include relationship columns is checked, Power BI creates extra columns containing binary data which links to each of the tables directly related to the current table.

In the Query Editor, we can use the Expand button to add any of the columns in the related tables into the current table. So, click on the Edit button to import the data and open the resulting query in the Query Editor.

We now want to add all of the pertinent columns in the HR.EventsTutors and Sales.EventsClients tables into Sales.EventsData. This means that we can begin by selecting and removing the HR.Venues and Sales.Workshops columns, since they do not contain any of the columns that we want to add.

Next, let us click on the Expand button on the right of the HR.EventsTutors column.

In the dialog which appears, deactivate all the checkboxes except for TutorID and TutorFee; since these are the only two options which represent actual data. (We do not need to import the EventID column, since this would simply be a duplication of the existing EventID which is the primary key of the EventsData table.

Let us also deactivate the option Use original column name as prefix; this is only useful where the columns we are importing have some names which are the same as those of the columns in the current table.

Click OK and the TutorID and TutorFee columns are imported into the Sales.EventsData table.

Now, let us do the same for the Sales.EventsClients column. Click on the Expand button on the right of the Sales. EventsClients column. In the dialog which appears, deactivate all the checkboxes except for ClientID, Revenue and Rating; and deactivate the option Use original column name as prefix.

Now, we have a single table containing all the main data which we want to analyse, and we can also benefit from query folding.

Query Folding and Native Queries

As we have said, query folding is the mechanism whereby Power BI creates SQL statements which are equivalent to the transformational steps carried out in the Query Editor; and, on each reload, executes these steps against the SQL Server database, in preference to using the M language to perform the transformations in Power BI.

To monitor Power BI’s query folding decisions, right-click on a Navigation step and choose View Native Query from the context menu.

Power BI displays the Native Query dialog within which is displayed the SQL statement which will be executed when this step is encountered.

Now, right-click on each of the Expanded HR.EventsTutors step and choose View Native Query from the context menu. In the Native query window, you will see the SQL statement which SQL Server will be asked to execute when each step is traversed.

  When creating Power BI reports, it makes sense to ensure that all, or as many as possible, of the operations you perform in the Query Editor will be executed as native queries; since this will help to improve the refresh rates of your reports. The key points to note are as follows.

Query folding will take place whenever a Query Editor transformation can be converted from the underlying M language into an SQL statement.

If you create a query by executing an SQL statement of your own, query folding will not be possible in any M transformations you subsequently perform.

· On the first occasion where there is no SQL equivalent to a Query Editor step that you perform, query folding will cease to occur; not only for that step, but for all subsequent steps.

Creating the Dimension Tables

Hopefully, using the steps outlined above, you will be able to create the remaining four tables required for our data model: Clients, Tutors, Venue and Workshops. For this reason, I will only outline the creation of one of the four; the Clients table.

This will give us a chance to see how the relationship columns feature can be used to incorporate data from tables which are not directly linked to the table to which you are connecting. In this example, we will bring in data from the Zones table into Clients; and, as you can see from the diagram, Zones is not directly linked to Clients; rather, it is indirectly linked, via the Countries table.

Choose Home > Get Data > SQL Server once more. In the SQL Server connection dialog, enter the name of your SQL Server instance in the Server field and “EventSales” in the Database field.

Click on the arrow to reveal the Advanced options section and activate Include relationship columns.

Click OK and, when the Navigator window appears, click in the checkbox next to the Sales.Clients table. On the right of the dialog, you will notice that in addition to the four columns which the table actually contains three extra columns are displayed (Media.Testimonials, Sales.Countries, and Sales.Zones), in other words, one column for each of the three tables directly linked to Sales.Clients.

Click the Edit button to import the data into the Query Editor.

Since we won’t need to bring in any data from the Sales.EventsClients table, let us delete that column.

Click on the expand button on the right of the Media.Testimonials column heading; and activate just the DateFilmed and VideoURL columns.

This gives us two new columns called DateFilmed and VideoURL.

Now, click on the expand button on the right of the Sales.Countries column heading; and activate the CountryName column. However, in addition to the columns in Sales.Countries, you will notice that we also have binary columns which link to tables related to Sales.Countries, including the one that we need, which is Sales.Zones.

Activate Sales.Zones and click OK. Then, finally, click on the expand button on the right of the Sales.Zones; and activate the ZoneName column.

Click OK and we have all the columns that we need in our Clients table. We would then perform all of the Query Editor steps that we need, keeping an eye out to make sure that as many steps as possible can be query folded. (One of our first steps will probably be to rename the columns to make them more report-friendly.)

And so on, for the HR.Tutors, HR.Venues and Sales.Workshops tables…

In summary, when connecting to a relational database, if you are a DBA, then create a view corresponding to each of the tables that you need in your Power BI data model and connect to these views from Power BI.

If you are not a DBA, then you can leverage the Include relational columns and query folding features to achieve the same result from inside Power BI.

from G Com Solutions Limited https://ift.tt/2OdxE30 via IFTTT