只从数据库中获取我想要的数据但保持结构 [英] Get only the data I want from a db but keep structure
问题描述
我正在尝试在 powerbi 上执行此操作，但我想这只是基本的 SQL
我想在 powerBi 中导入我的数据，但用一些 id 过滤它.让我们举这个例子.我有一些公司的数据库(表 1).每个公司都有大楼(table2)，每个大楼都有员工(table3).我想导入这三个表，但仅限于一家公司(OnePowerBi 是关于一家公司，再也没有).请注意，每个表都有父表的外键.我的架构在 powerBI 中运行良好
我尝试在高级选项中进行 SQL 查询(在 Power BI 上，您可以在选择导入哪个表之前执行一些 SQL)但如果我这样做:
从 idcomp=1 的公司中选择 *它只会导入公司表.如果这些数据链接到选定的公司 ID，我想导入所有表的所有数据，但保留结构而不是在请求"或表"中获取所有数据，就像内部连接一样.我该怎么办?
这个答案是基于我对问题的理解.如果此解决方案无法满足您的需求，我建议您更新问题以阐明您的问题和期望的结果.
另外，我提前为这个答案的长度道歉；我不知道你对 Power BI 的专业水平，我想确保涵盖我采取的每一步.
注意 - 我使用的是 Windows 应用商店提供的最新版本的 Power BI Desktop.如果我执行的任何操作或发布的图片与您的 Power BI 应用程序不一致，请确保您使用的是最新版本.
0) 首先，我创建了一个包含三个表和一些数据的示例 SQL 结构.我运行的 SQL 代码可以在
2) 在此处输入您的 SQL 服务器和数据库名称(我使用的是本地数据库，因此是.").
3) 仅选择 Companies 表并点击 Edit...
...像这样导入.
4) 点击管理参数 -> 新建参数.
5)如下所示填写表格.
6) 返回 Companies 查询，单击 idcomp 列的下拉箭头，然后转到 Number Filters -> Equals...
7) 在弹出框中，单击第一个条件的下拉箭头并将其切换为参数.由于只有一个参数，所以应该会自动填写.点击确定...
...得到这个.
8) 右键单击公司查询并单击复制.再次执行此操作，以获得原始查询和两个副本.
9)返回原始查询，右键单击建筑物"列并选择删除".
10) 右键单击公司 (2) 查询并选择重命名.将此第二个查询命名为 Buildings.
11) 在 Buildings 查询中，右键单击 Buildings 列并选择 Remove Other Columns.
12) 点击 Buildings 列上的展开箭头按钮，取消选择除 idbuild 和 BuildingName 之外的所有内容(如下所示).
13) 右键单击公司 (3) 查询并选择重命名.将此第三个查询命名为雇员".
14) 在员工查询中，右键单击建筑物列并选择删除其他列.
15)点击Buildings列上的展开箭头按钮，取消选择除Employees之外的所有内容(如下图).
12)点击员工栏上的展开箭头按钮，取消选择除idemp、FirstName和LastName之外的所有内容(如下所示).
13) 点击关闭 &申请.
14) 要查看数据，请制作几张表格，然后查看仅加载了公司 1 的信息.
15)要更改加载的公司，请单击主页 -> 编辑查询.
16) 点击 CompanyID (1) 查询并将当前值更改为 2.
17) 点击关闭 &应用并注意表格更新为仅显示公司 2 的信息.
I'm trying to do this on powerbi but I guess it's just basic SQL
I would like to import my data in powerBi but filtering it with some id. Let's take this example. I have db with some companies (table 1). Each compay has building (table2) and each building has employees (table3). I would like to import thoses three table but only for a company ( OnePowerBi is about One company, never more). Please notice that each table has the foreign key of the parent one. My schema works well in powerBI
I tried to make SQL query in advanced option ( on power BI you can do some SQL before chossing which table import) but if I do:
select * from companies where idcomp=1
It will only import the company table. I would like to import all the data of all the tables if thoses data are linked to the selected company ID but keep the structure and not get all the data in a "request" or "table", as an inner join will do. How should I do?
This answer is based off my understanding of the question. If this solution doesn't get you what you want, I would suggest updating the question to clarify what your problem and desired result is.
Also, I apologize ahead of time for the length of this answer; I don't know your level of expertise with Power BI and I wanted to make sure to cover every step I took.
Note - I am using the latest version of Power BI Desktop available through the Windows Store. If any of the actions I take or pictures I post don't align with your Power BI application, make sure you are using the latest version.
0) As a start, I created a sample SQL structure with three tables and some data. The SQL code I ran can be found here.
1) In Power BI, click on Get Data -> SQL Server
2) Enter your SQL server and database name here (I'm using a local DB, hence the ".").
3) Select only the Companies table and click Edit...
... which imports like this.
4) Click on Manage Parameters -> New Parameter.
5) Fill out the form as shown below.
6) Back in the Companies query, click on the dropdown arrow for the idcomp column and go to Number Filters -> Equals...
7) In the pop up box, click the dropdown arrow for the first condition and switch it to Parameter. Since there is only one parameter, it should automatically fill in. Click OK...
... to get this.
8) Right click on the Companies query and click Duplicate. Do this a second time so you have the original query and two copies.
9) Back in the original query, right click on the Buildings column and select Remove.
10) Right click on the Companies (2) query and select Rename. Name this second query Buildings.
11) In the Buildings query, right click on the Buildings column and select Remove Other Columns.
12) Click on the expansion arrows button on the Buildings column and deselect everything except idbuild and BuildingName (as shown below).
13) Right click on the Companies (3) query and select Rename. Name this third query Employees.
14) In the Employees query, right click on the Buildings column and select Remove Other Columns.
15) Click on the expansion arrows button on the Buildings column and deselect everything except Employees (as shown below).
12) Click on the expansion arrows button on the Employees column and deselect everything except idemp, FirstName, and LastName (as shown below).
13) Click Close & Apply.
14) To check the data, make a few tables and see that only company 1's information has been loaded.
15) To change which company is loaded, click on Home -> Edit Queries.
16) Click on the CompanyID (1) query and change the Current Value to 2.
17) Click Close & Apply and notice that the tables update to only show company 2's information.
这篇关于只从数据库中获取我想要的数据但保持结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
