-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathindex.html
More file actions
executable file
·341 lines (304 loc) · 23.6 KB
/
Copy pathindex.html
File metadata and controls
executable file
·341 lines (304 loc) · 23.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
<html>
<head>
<link rel="shortcut icon" href="https://www.commerce.gov/sites/all/themes/doc/doc_theme/favicon.ico" type="image/vnd.microsoft.icon" />
<title> Schooling Power BI </title>
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=320, height=device-height, target-densitydpi=medium-dpi" />
<link rel="stylesheet" href="css/creative.css" type="text/css">
<link rel="stylesheet" href="css/animate.min.css" type="text/css">
<script src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script src="js/parallax.js"></script>
<script src="http://d3js.org/d3.v3.min.js"></script>
<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="generator" content="pandoc"/>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" type="text/css" href="css/bootstrap.css">
<!-- <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css"> -->
<style type="text/css">
pre:not([class]) {
background-color: white;
}
</style>
<script type="text/javascript">
if (window.hljs && document.readyState && document.readyState === "complete") {
window.setTimeout(function() {
hljs.initHighlighting();
}, 0);
}
</script>
<style>
html, body. div{
width: auto !important;
overflow-x: hidden !important;
}
hr {
width: 100%;
height: 8px;
margin-left: auto;
margin-right: auto;
background-color:white;
color:white;
border: 0 none;
}
#btn {
background: purple;
/*background: #d93434;*/
background-image: -webkit-linear-gradient(top, purple, #820a0a);
background-image: -moz-linear-gradient(top, purple, #820a0a);
background-image: -ms-linear-gradient(top, purple, #820a0a);
background-image: -o-linear-gradient(top, purple, #820a0a);
background-image: linear-gradient(to bottom, purple, #820a0a);
-webkit-border-radius: 4;
-moz-border-radius: 4;
border-radius: 4px;
font-family: Arial;
color: #ffffff;
font-size: 12px;
padding: 10px 20px 10px 20px;
text-decoration: none;
}
#btn:hover {
background: #6387cf;
background-image: -webkit-linear-gradient(top, #6387cf, #3498db);
background-image: -moz-linear-gradient(top, #6387cf, #3498db);
background-image: -ms-linear-gradient(top, #6387cf, #3498db);
background-image: -o-linear-gradient(top, #6387cf, #3498db);
background-image: linear-gradient(to bottom, #6387cf, #3498db);
text-decoration: none;
}
.main-container {
max-width: 940px;
margin-left: auto;
margin-right: auto;
}
code {
color: inherit;
background-color: rgba(0, 0, 0, 0.04);
}
img {
max-width:100%;
height: auto;
}
#woot{
background-color: white;
opacity: 0.9;
color:purple;
padding: 10px;
border-radius: 10px;
font-family: Helvetica;
}
#weight{
font-weight: bold;
}
.main-container {
max-width: 940px;
margin-left: auto;
margin-right: auto;
}
code {
color: inherit;
background-color: rgba(0, 0, 0, 0.04);
}
img {
max-width:100%;
height: auto;
}
</style>
</head>
<body>
<!--NavBar-->
<nav id="mainNav" class="navbar navbar-default navbar-fixed-top">
<div class="container-fluid">
<!-- Brand and toggle get grouped for better mobile display -->
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="https://www.commerce.gov/dataservice/"><img style="max-width:100%; max-height:100%;" src="img/logo.png"></a>
</div>
<!-- Collect the nav links, forms, and other content for toggling -->
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav navbar-right">
<li>
<a class="page-scroll" href="#top">Home</a>
</li>
<li>
<a class="page-scroll" href="#start">Tools</a>
</li>
<li>
<a class="page-scroll" href="#story">Visuals</a>
</li>
</ul>
</div>
<!-- /.navbar-collapse -->
</div>
<!-- /.container-fluid -->
</nav>
<!--Cover-->
<section class="scroll backie datacorps " id="top">
<div class="header-content">
<div class="header-content-inner" >
<h2 style="position:absolute;left:15%; top:50%;text-align:left;">
<div id="woot">
<span style="font-size:150%;line-height:1.3;" >
schooling with<strong><br>Power</strong> BI<br></span>
<a id = "btn" href="https://github.com/CommerceDataService/TEMPLATE_REPO/zipball/master"> <strong>ZIP File</strong></a>
<a id = "btn" href="https://github.com/CommerceDataService/TEMPLATE_REPO/tarball/master"> <strong>TAR Ball</strong></a>
<a id = "btn" href="https://github.com/CommerceDataService/TEMPLATE_REPO"><strong>GitHub</strong></a>
<p><br>Use Power BI to visualize school districts</p>
</div>
</h2>
</div>
</div>
</section>
<body>
<!--header-->
<div class="container-fluid main-container" >
<div class="container">
<div class="row">
<br>
<div class="col-lg-3 col-md-4 col-sm-5" style="color:grey">
<h4>By <a href="http://twitter.com/adamhecktman">Adam J. Hecktman</a> and <a href="https://twitter.com/tableteer">Frank J. La Vigne</a>, <a href="http://www.microsoft-chicago.com/">Microsoft Chicago</a></h4> <h5> Edited by Star Ying, <a href="https://www.commerce.gov/dataservice">Commerce Data Service</a> </h5>
</div>
<div class="col-lg-6 col-md-5 col-sm-5" style="color:grey">
<em>As part of the <a href="https://www.commerce.gov/datausability/">Commerce Data Usability Project</a>, Microsoft Chicago in collaboration with the <a href="https://www.commerce.gov/dataservice/">Commerce Data Service</a> has created a tutorial that introduces PowerBI as a way to explore Commerce data. If you have question, feel free to reach out to the Commerce Data Service at <a href="mailto:DataUsability@doc.gov">DataUsability@doc.gov</a> or Microsoft Chicago at <a href="mailto:[]">Email Goes Here</a>.</em><br>
</p>
</div>
</div>
</div>
</div>
<div id='start' class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Introducing PowerBI</strong><br></span>
Power BI is a free piece of software from Microsoft. With simple out-of-the box dashboards for services like oData, Hadoop, and other common open data sources (as well as commercial data sources like Salesforce, Google Analytics, and Dynamics) you can get insights from your data very quickly. It runs as a web service through a browser or as a desktop application. While this tutorial will give you experience using both the desktop app as well as the sharing functionality of Power BI in the browser.</p>
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>What data?</strong><br></span>
Data analysis using open government data has taken the center stage for everything from community organizing to starting or growing a business to crafting good public policy. Many government entities – federal, state, and local – have made open data a cornerstone of their transparency initiatives. But opening data to the public is just the first step to driving knowledge and insights in using that data. People need easy to use tools to help them discover, combine, analyze, and visualize that data. This two part tutorial will cover how to use the tool Power BI with open data from both federal and local data sources.</p>
<p>In Part 1 of this tutorial series, you will learn how to pull data into Power BI. We will then do some transforming of the data to make it in the exact format we want in order to use it. We will create some simple visuals with that data. And then we will ask questions in, natural language, to gain some insights.</p>
<p>The next tutorial will focus on creating queries, doing advanced calculations, creating and sharing reports, and building dashboards so that every user can get a personalized view of the data that matters most. You will also see how easy it is to keep on top of your data no matter where you are. With touch-enabled native apps for Windows, iOS, and Android, you will be able to access your data wherever you go.</p>
</div>
<br><br>
<div class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Getting the tool:</strong><br></span>
Go to <a href="http://powerbi.com">http://powerbi.com</a> and click on the Get Started For Free button. This will enable you to both download the desktop version of Power BI, as well as sign up with an account that you can use to share and access your data from anywhere.</p>
<br><br>
<img src="img/image1.png" style="width: 100%;align:center">
<br><br>
<p>Follow the instructions to install Power BI and run it. The first thing you will see is this welcome screen:</p>
<br><br>
<img src="img/image2.png" style="width: 100%">
<br><br>
<p>There are 4 very short, but very informative videos to help you get started. I encourage you to take the time to review them to understand how you can use this incredibly simple, but also very powerful tool.</p>
</div>
<br><br>
<div class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Getting started by getting data:</strong><br></span>
We are going to start by looking at a very simple data set to create a very simple report. We are going to visualize the number of school districts in each state using US Department of Commerce Census data. In the process, we will see what it takes to load that data, transform the way it looks, and use it to create some visualizations. Then we will move on to something more complex in Part 2.</p>
<p>You can find a lot of good data quickly using the American Fact Finder site, which gives you a friendly way to search for the data you need. We are interested in schools, so we can look at this data set that lists, by state, the number of school districts, and the size of enrollment in those districts. We won’t be concerned about size for now, just the number of districts. You can see the <a href="http://factfinder.census.gov/faces/tableservices/jsf/pages/productview.xhtml?pid=COG_2012_ORG11.US01&prodType=table">dataset</a> here.</p>
<p>Let’s download this data so that we can work with it. Click on the Download button and uncheck the “Include descriptive data element names” button. Choose the Excel format (which is actually CSV) and click ok. When it is ready, click on the download button to download the file (or you can simply use the file included in the folder wit this demo.</p>
<p>In Power BI, click on Get Data When you click on Get Data, you will see the vast number of data sources that you can bring into Power BI. You can bring in standard file formats such as Excel, CSV, and Text. You can also bring in data from database formats, such as SQL Server, Oracle, DB2, and MySQL. Many government data sources use the oData format. You have access to cloud-based data sources such as Azure, Hadoop, and Salesforce. And many others.</p>
<p>In this case, we are going to open this dataset up as a CSV, so click on CSV and point it to the file you just downloaded.</p>
<p>Before we load it, let’s transform this so that when it comes in, we have less work to do to change it structurally (we can also do this later after we have the data loaded and have worked on it). Click on Edit.</p>
<br><br>
<img src="img/image3.png" style="width: 100%">
<br><br>
</div>
<br><br>
<div class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Cleaning the data:</strong><br></span>
We are only going to focus on the number of school districts in the state, and the distribution of schools by size. So we can get rid of many of these columns. To get rid of a column, select the column and choose “Remove column” from, the ribbon. Do this for the first column (Geo.id). You can also remove multiple columns simultaneously. For example select the GEO.display-label column, hold the Shift button and select the GC.display-label column. Now multiple columns are selected. Click on Remove Columns from the ribbon.</p>
<p>Now your first column will show the states. GC.display-label1 is not a very descriptive name for the column heading. Right click on the column heading and choose “Rename…” and give it a more meaningful name like “States”.</p>
<br><br>
<img src="img/image4.png" style="width: 100%">
<br><br>
<p>For this exercise, we are only interested in Independent Schools. That means you can remove all of the columns from “Total Dependent” on to the right till the end. Remove these columns. Also remove the “all_systems” column.</p>
<br><br>
<img src="img/image5.png" style="width: 100%">
<br><br>
<p>We can do more to transform this data. For example, we can make sure that the data in the numeric columns are seen as numeric. This may sound intuitive, but remember that this is from a CSV file, which is something of a glorified text file. We want to make sure that as we are working with numbers that Power BI (or Excel or any database) realizes they are numbers and not text.</p>
<p>First, we need to select the rather large number of columns that extends from total_independent to the end. Again, we can do this quickly by selecting the total_independent column, scrolling to the right and selecting the last column. These columns all contain whole numbers. In the ribbon (in the Transform section) click on “Data Type” and change the data type from Text to Whole Numbers.</p>
</div>
<br><br>
<div class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Data Types, Data Types:</strong><br></span></p>
<img src="img/image6.png" style="width: 100%">
<br><br>
<p>Note the wide variety of data types you have to choose from</p>
<p>There are a few other things that we can do before we load this data to make it easier to work with. For example, note that the number zero is represented by the “-“ character. For charting, we want this to be a real digit. In the ribbon (in the Transform section), click on replace. Replace “-“ with “0” (do not put the quotes in.</p>
<br><br>
<img src="img/image7.png" style="width: 100%">
<br><br>
<p>We have one more clean up task to take care of. Note that the top row is the entire US. It is an aggregated row. We want to do that ourselves with our visualization. So in the ribbon (in the Reduce Row section), click on the small arrow next to the Remove Rows button and choose remove top rows. Remove only the first row.</p>
</div>
<br><br>
<div id='story' class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Visualizations:</strong><br></span></p>
<p>We are now ready to create some interesting visualizations. Click on Close & Apply on the ribbon (in the close section on the left). Now you will see three sections on your window. The big empty space is your pallet. To the right is the visualizations pane where you can choose and modify your visuals. To the right of that is your fields pane, where you can choose which fields you would like to visualize. Click the expand arrow on the table in the fields pane.</p>
<br><br>
<img src="img/image8.png" style="width: 100%">
<br><br>
<p>For our first visual, we will create a filled map, with the shading on the map representing the total number of independent school districts in the state. In the visuals pane, click on the filled map icon. This will give you a blank template on your pallet.</p>
<br><br>
<img src="img/image9.png" style="width: 100%">
<br><br>
<p>You will first need to tell Power BI which field represents the geography data (i.e. States). Drag the States field from the Fields pane to your visual. Expand the visual so that the United States is in view, and you will see that it is shaded.</p>
<p>You now need to tell Power BI which field represents the data you want to map. Drag the total_independent field to the map, and you will see the states with more school districts now appear darker than those with less. In the visualization pane, click on the format icon (the little paint brush) and expand Title to reveal title options. Give this visual a meaningful name such as “Independent Schools by State”.</p>
<br><br>
<img src="img/image10.png" style="width: 100%">
<br><br>
<p>Next, we want to add another visual. You may need to expand the pallet by clicking on the edge of the white space and sliding it to the right. Add a stacked column chart by clicking on it in the visualizations pane (top row, second from the left). Again, you are presented with a template. Start by dragging the state field to the template. Then select all of the breakdowns for size of the school districts and drag those to the page (i.e. all of the fields that start with “independent_...”. Note that Power BI automatically predicts that you want to see all of the states and their school districts, broken down in the column by the sizes of those districts. You did not have to do any formulas or connections.</p>
<br><br>
<img src="img/image11.png" style="width: 100%">
<br><br>
<p>Again, give this a title by clicking on the pencil icon in the visualization pane. Expand the “Title” section and replace the name that is there with something meaningful like “School District Sizes”.</p>
<p>Now that is a lot of data to cram into one column chart. You can simplify charts by adding slicers. Slicers allow you to choose just the data you want to focus on. Click on a white space on your pallet and then choose the slicer icon in the visualization pane.</p>
<br><br>
<img src="img/image12.png" style="width: 100%">
<br><br>
<p>You now need to decide what your users will slice the data by. The obvious choice in this example is state. It will allow your users to focus on one or a few states. Click on State in the field pane.</p>
<p>It is important to note that Power BI automatically figures out how to tie all of your visualizations in your report together. For example, if you click on Arizona in the slicer, you will see the data on both the map and the column chart highlight Arizona data.</p>
<p>Let’s add a “Select All” button to our slicer. In the visualization pane, click on the format icon and open the “Selection Controls” group. Turn the “select all” slider to on. Now you have a select all button on your slicer. Click on it and you will see all of the states come back into focus.</p>
<p>But it is not just the slicer that is tied to the rest of your visuals. On your column chart, scroll over to New York and click on its column. You will see the data for New York come into focus on the map.</p>
<br><br>
<img src="img/image13.png" style="width: 100%">
<br><br>
<p>Save your Power BI reports by clicking on File/Save. After you save, you are prompted to get some insights that Power BI has discovered for you. Click on Get Insights and you will be taken to your personal Power BI report page on the browser.</p>
<br><br>
<img src="img/image14.png" style="width: 100%">
<br><br>
</div>
<br><br>
<div class="container-fluid main-container">
<p style="font-family:Helvetica;">
<span style="font-size:150%;color:purple;"><strong>Next steps:</strong><br></span></p>
<p>How did it do this? Power BI quickly searches different subsets of your dataset while applying a set of sophisticated algorithms to discover potentially-interesting insights. Power BI scans as much of a dataset as possible in an allotted amount of time. In this case, with the visual on the right, it is telling you that Florida has noticeably more independent schools with 100,000 or more students. If you click on the visual, you can see a more focused view. </p>
<p>Now you are in the browser version of Power BI. From here, it is easy to create dashboards from your visuals, to publish reports to the web, and to ask questions in your own natural language. We will cover these in Part 2.</p><br><br>
</div>
<!-- Getting Started/Code Notebook Section -->
<!-- <div id='code' style="color:black;font-family:Helvetica;" >
<div class="container-fluid main-container">
<p style="font-family:Helvetica"><span style="font-size:200%;color:purple;"><br><strong>TEMPLATE_GETSTARTED</strong></span>
<br>Schlitz hoodie echo park godard, chicharrones gentrify small batch wayfarers gastropub put a bird on it humblebrag microdosing taxidermy drinking vinegar. Health goth scenester tacos actually, ugh tumblr vinyl XOXO ennui meditation try-hard. Narwhal seitan artisan, ugh flannel green juice polaroid pickled drinking vinegar tousled plaid man bun heirloom mixtape. Keytar try-hard jean shorts thundercats meh. Wayfarers banh mi sriracha, bitters intelligentsia messenger bag distillery migas. Fashion axe fixie photo booth gluten-free seitan, meh bicycle rights thundercats post-ironic occupy etsy. Post-ironic chia synth, cronut chillwave tousled kale chips craft beer umami sriracha health goth shoreditch paleo.</p>
<p>Follow along in the Jupyter Notebook below or check out the code files at <a href="https://github.com/CommerceDataService/TEMPLATE_REPO">TEMPLATE_REPO</a>.</p>
<p><a href="./TEMPLATE_NOTEBOOK.ipynb">Download TEMPLATE_NOTEBOOK_TYPE</a></p>
</div>
</div> -->
<!-- <div>
<iframe src="markdown_or_jupyter_export" marginwidth="0" frameborder = "0" marginheight="0" scrolling="no" width="100%" height="100%" onload="resizeIframe(this)"></iframe>
</div>
<script language="javascript" type="text/javascript">
function resizeIframe(obj) {
obj.style.height = obj.contentWindow.document.body.scrollHeight + 'px';
}
</script>
-->
</body>
</html>