In this era of data, Business Intelligence and analytics have become massive staples in how we do business. Whether you are creating visual diagrams of Key Performance Indicators (KPIs), a scalable data warehouse of stock market trend data or price comparison portals observing different retailers compared against each other – there are several tools that are designed to easily pick up data elements from database tables and assist users in creating Business Intelligence solutions.
One of the largest hurdles for a business is where they can access this data in order to analyze using these Business Intelligence tools. With Content Grabber, you can access this data directly from the websites or APIs that contain this data in order to integrate it with your Business Intelligence solution. In this article, we will use an agent created using Content Grabber looking at the BMW Motorcycles website (www.bmwmotorcycles.com) in order integrate price, feature and specification data into a SQL database that will connect to Microsoft PowerBI Desktop.
Connecting a Content Grabber Agent to a SQL Database
In this example, we will use a previously created agent for BMW Motorcycles. First, we will want to create a database with no tables included within your database management tool. In this instance, I will be using SQL Server Management Studio. I have given the database the name “bmwmotorcycles.”
Within Content Grabber, you can connect to a SQL database by directing to Data>>SQL Server Configuration. Once on this window, you can select New and input the Server, Database and Connection name of the SQL environment you want to connect to. You can select Test Connection in order to verify the connection works properly and then select Save.
Naming the Data Elements with Table Names
When creating an agent in Content Grabber, you can name the specific data commands for specific pages or list commands with the name of the table(s) you would like in your SQL database. To do this, direct to the specific page or list command and select Edit Command. Now, direct to the Properties tab and under the Export section, select the Export Method: Separate Output and the Export Name with the name of the table, in this example: “bmwmotorcycles Specification.” By default, the column names will be the names listed for the sub-commands of the page or list commands.
Table Relationship Assignment in Content Grabber
By Default, Content Grabber will assign a key to the specific list or page command(s) you have defined as a table within Content Grabber. If a sub-page is linked from a parent page that contains a base item (such as a product or product group), the key will be the parent item that is linked to the sub-page. These keys are dynamically generated when an agent exports data to the SQL database.
Running a Content Grabber Agent to Create SQL Tables
Once you are happy with the output and design of your agent, you can run the agent by executing the play button on the top menu in Content Grabber. If you are connected to a SQL database, the Table names, column names and relationships will be automatically created in your SQL database once the agent is executed.
Connecting the SQL Database to Microsoft Power BI Desktop
Connecting the SQL database that was created using Content Grabber is very easy within Microsoft Power BI Desktop. Open Microsoft Power BI Desktop, select the Get Data dropdown menu on the top toolbar and select SQL Server. Enter your Server, Database and select OK.
Now, either enter your credentials or use Windows Authentication if the SQL Server instance is located on your PC or Active Directory. Once you have chosen your credential option and provided the details, select Connect.
You will now be directed to a window where you can select the table to load into your PowerBI instance. Check each table and then select Load.
PowerBI will now create the associated connections and relationships based on the SQL table structure that has been defined.
You will now see a blank screen with all tables listed under the Fields section on the right.
Creating a Basic Visualization in Microsoft Power BI Desktop
As a basic example, we will create a visualization that will display the product groups and the MSRP prices in a graphical format. First, we will select the Stacked Column Chart under Visualizations.
Now, I selected added ProductName and MSRP to the Axis, and Count of ProductName to the Value.
The result is the following Stacked Column Chart:
As you can see, you can easily create visual charts to use with data collected from Content Grabber. There are unlimited possibilities with what you can do with this type of data, depending on what data values you gather and what visualizations or Business Intelligence metrics you choose.