Skip to content
Home » Power BI

Power BI

Power BI Information, How-To’s, Tips, Tools, and Guides

SCRAPE DAILY SHORT DATA FROM FINRA

Here is a quick how-to on getting the daily short data. From this I look to see if the short volume has increased or decreased on a particular stock by leveraging a Power BI Dashboard that I had created. This guide is to show the simple command I run to initially scrape all the data

From a Linux box

Open a terminal

Type:
mkdir FINRA
cd FINRA
wget -r -np http://regsho.finra.org/regsho-Index.html

After it downloads the data I run
rm *.html

To remove the additional files that are not relevant, and then I move the data to where I need it

INSTALL MONGODB ON RASPBERRY PI 4

By far the best guide I have found, in starting this journey, was written by Mark Smith on https://developer.mongodb.com/how-to/mongodb-on-raspberry-pi/

If you want to see why things work the way they do, please visit his guide. My guide below is just the commands, and how I am connecting into the database once it is setup.

To begin, first you will want to install Ubuntu Server 20.10 as described here:
https://tacticalware.com/install-ubuntu-server-20-10-64-bit-on-raspberry-pi-4-using-a-m-2-drive/

Once you are up and running, ssh into the Raspberry Pi
Login

From the terminal run the following:
sudo bash

wget -qO – https://www.mongodb.org/static/pgp/server-4.4.asc | sudo apt-key add –

echo “deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu focal/mongodb-org/4.4 multiverse” | tee /etc/apt/sources.list.d/mongodb-org-4.4.list

apt-get update

apt-get install -y mongodb-org

systemctl daemon-reload

systemctl enable mongod

systemctl start mongod

systemctl status mongod

CTRL button +c to exit

mongo
use admin

db.createUser( { user: "admin",
 
        pwd: "SuperSecretPassword",

        roles: [ "userAdminAnyDatabase",

                 "dbAdminAnyDatabase",

                 "readWriteAnyDatabase"] } )

exit

nano /etc/mongod.conf

Add these lines to the end of the file

security:

   authorization: enabled

CTRL +x to Exit
Y to Save

systemctl restart mongod

mongo

db.adminCommand({listDatabases: 1})

Make sure you had no errors

exit

mongo -u “admin” -p

Enter your admin password for mongo, aks your SuperSecretPassword

Type
db.adminCommand({listDatabases: 1})
This will list out your databases and ensure you created it properly

exit

nano /etc/mongod.conf
and Change the bind ip from 127.0.0.1 to 0.0.0.0

net:
   port: 27017
   bindIp: 0.0.0.0

CTRL +x to Exit
Y to Save

systemctl restart mongod

ufw allow 27017/tcp

MongoDB is now successfully setup and running. On to configuring the Windows 10 system

To connect to the MongoDB from a Windows 10 computer

Open an Internet Browser on your Windows 10 computer
Navigate to https://robomongo.org/
Download the Robomongo Robo 3T Client
Once you install it, add these settings to connect to your system
Click Create
Connection Tab
Type – Direct Connection
Address – The IP of your Raspberry Pi
Port 27017
Authentication Tab
Check mark Perform Authentication
Database is admin
User Name is admin
Password is your SuperSecretPassword
Auth is SCRAM
Click Save
Click Connect

You are now connected and ready to run with your MongoDB environment

Hardware that I used:
Raspberry Pi 4 (4gb)
https://amzn.to/3q551IO

CanaKit 3.5A Raspberry Pi 4 Power Supply (USB-C)
https://amzn.to/3fNTYPu

CanaKit Raspberry Pi 4 Micro HDMI Cable – 6 Feet
https://amzn.to/33u5hr9

Western Digital 500GB WD_Black SN750 NVMe
https://amzn.to/3nZ5pH4

Plugable USB C to M.2 NVMe Tool-free Enclosure
https://amzn.to/3lflV3L

POWER BI – DISABLE DATE/TIME HIERARCHY

  • Power BI

Sick of your system being bogged down by the overhead of the autocalculations for the Date/Time fields. Here is how you disable that feature:

Inside Power BI
Go to File
Click Options and Settings
Select Options
Find GLOBAL and look at the options below it
Select Data Load
Uncheck Auto date/time for new files
Find CURRENT FILE and look at the options below it
Select Data Load
Uncheck Auto date/time

POWER BI CALENDAR / DATE TABLE

  • Power BI

If your data warehouse does not have a date table or a time table, how do you create it within Power BI….well here you go

In Power BI
Click the Modeling tab
Select New Table
Type in:
Calendar = CALENDARAUTO(6)


Note:
CALENDAR – You will supply the Start and End Date
CALENDARAUTO – Looks through all the dates in the model and automatically builds out the range. If your model begins on July 15, 1979 and your fiscal year end is June 30th, if you set CALENDARAUTO(6) it will allow all dates from July 15, 1979 to June 30, 2021. If you leave CALENDARAUTO() blank, it will go to the end of the year (December 31).

DATA MODELING BEST PRACTICES

  • Power BI

To get the best results in your Power BI model, use the following below as a checklist

  • Data Model changes do not impact the source. If you need source data always changed, you will need to modify that directly or through Power Query
  • For the Data Model, only pull the data you need
  • If you need the database to do some work, push it back down to the database to do it. Save your local workstation the hassle.
  • Data warehouse…build one
  • Set your data types (date, decimel, etc)
  • Disable the default summarization
  • Categorize your Fields
  • Hide items you don’t need
  • If you don’t need certain data, remove it…. For instance, if you have Date and Time together in one column, split Time out from the Date, and remove it.
  • Reduce the number of distinct values in a field as much as possible. For example if you have Date and Time together in one column, and you need both, split them out, and keep both. It will reduce the number of distinct values altogether and improve the efficiency of your model.
  • Fact Table – Large Tables that has relationships out to Dimensions
  • Dimension Table – These are Small tables (filter on these and it will improve the efficiency of your model)
  • Star Schema – Set your Fact Table in the middle, and your Dimension Tables around the Fact Table
  • Snowflake Schema – Multiple Fact Tables and Multiple Dimension Tables
  • Your Tables must flow to the Fact Table in a single direction. If the flow is away from your fact table, your data will not be accurate
  • Oversampling and/or Ambiguity occurs when you have a table relationship set to both, or bidirectional, instead of in a single direction. Do not set the relationship to both as it increases performance overhead and can cause confusion in the engine
  • Within Power BI take the time to Categorize the fields that can be categorized (Select Field > Column Tools > Data Category > Select what category it is)
  • Within Power BI, in the fields section, you may see summarized data that shouldnt be summarized. You will need to change Summarized Data in the Summarization dropdown, to Don’t Summarize, where it makes sense…such as in the Key field.

POWER QUERY – SETTING PARAMETERS

  • Power BI

To use parameters, which expecially helps when moving files from your local computer to the web service, perform the following

Open Power Query Editor
Click Home
Select Manage Parameters
Click New
Give it a Name (tacticalware)
Specify a Type from the drop down. For me I selected Text
In the Current Value field, specify the location of the csv file (C:\tacticalware\x.csv)
NOTE – it is case sensitive
Click Ok

Now go into your Advanced Editor
Find the location of the csv file (C:\tacticalware\x.csv)
Replace the path with the Name of the parameter (tacticalware)
So it will look like this:
Source = Csv.Document(File.Contents(tacticalware),……and so on
Click Done


POWER BI – QUERY FOLDING

  • Power BI

What is Power Bi Query Folding…..
– It reduces trips to the data source
– Pushes transformations to the data source
– It combines steps into a single statement that is sent to a data source
– Some operations may prevent query folding from happening
– It is supported by SQL Server
– Also supported by relational data sources

What it is NOT
– This is Not Available with all data sources

To see the steps, Enter into your Advanced Editor. It will show you the M Language here.

To view the SQL Server code that is generated:
In Power Query > Applied Steps section
Right click on a step, and select View Native Query
This will show you the SQL Server Code that will be sent back to the server for that particular step. However it does not sent the code back to SQL Server after each step. It sends it at the end when you Apply or Close & Apply

ADDING CUSTOM COLUMNS IN POWER BI

  • Power BI

I have been scraping Wikipedia for islands information, and building models in Power BI around them. One thing that I run into over and over is the lack of a certain field in each of the scrapes. The scraped don’t have the name of the State associated with the island names. So I will have to fix this by adding custom columns in Power BI / Power Query, so my models have better visuals. To do this you will need to perform the following:

Click Transform data to open Power Query Editor
Select Add Column from the Main Menu
Click Column From Examples
Select From All Columns
Enter in a value – for me I put in NH, and press Enter
Enter in your value a second time…for me it was placing NH in the second row, Now the rest of the rows should auto populate with the data (again for me NH showed in every subsequent row)
Click OK
Rename the leader from Literal, to State

You are done! Thanks for reading

POWER BI IMPORT VS DIRECT QUERY

  • Power BI

Import is taking a copy of a data from SQL server, and the copy is placed into your Power BI Desktop file

Direct Query leaves the data at the source and only pulls the data needed, when it is needed

CREATING A LOCAL MSSQL SERVER ON YOUR W10 PC

From time to time you will need to utilize a local sql server, for dev, testing, education, whatever. So here is a quick how-to on creating a local mssql server on your W10 pc.

Download and install the free version of SQL, called SQL Express:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Accept all the defaults, except for these few
Use your credentials when prompted
Select all packages to be installed

Once SQL Express is installed, download and install SQL Server Management Studio
https://aka.ms/ssmsfullsetup

Now you have both installed, open SQL Server Management Studio or SSMS for short, and connect to your local SQL Express Database
Connect to “localhost\sqlexpress“
You dont have to put in any credentials if you selected to use your credentials earlier.

To do a test on the database, try restoring the AdventureWorksDW database
Right click on Databases
Select Restore database
Select the AdventureWorksDW file
Click Ok

You can now connect into your database using Power BI or another tool, and start playing around.

Thanks for reading