Forum Discussion

EVEAdmin's avatar
EVEAdmin
Post Patron
7 years ago
Solved

Import tables or write SQL query? The debate

Please share your opinions on this subject. I successfully connected to my remote MSSQL Server and can import those tables necessary to generate reports.  When I import tables, I can hide/delete co...
Need Help
  • Mark_Timson's avatar
    Mark_Timson
    7 years ago

    Using direct query can help if your table is too large to import. From what I can tell trying to import using params in power bi is pretty difficult. I tend to create a sql view with ctes to act as params and use another cte to bring it altogether in view to only return and smaller data subset and then import in with power bi. 

     

    The import is fairly quick for tables under 30 million rows.

Mark_Timson's avatar
Mark_Timson
Helper I
7 years ago

Using direct query can help if your table is too large to import. From what I can tell trying to import using params in power bi is pretty difficult. I tend to create a sql view with ctes to act as params and use another cte to bring it altogether in view to only return and smaller data subset and then import in with power bi. 

 

The import is fairly quick for tables under 30 million rows.

EVEAdmin's avatar
EVEAdmin
Post Patron
7 years ago
Mark_Timson wrote:

Using direct query can help if your table is too large to import. From what I can tell trying to import using params in power bi is pretty difficult. I tend to create a sql view with ctes to act as params and use another cte to bring it altogether in view to only return and smaller data subset and then import in with power bi. 

 

The import is fairly quick for tables under 30 million rows.

That is exactly where I stand. 

Yes, the tables I need to import are large in terms of both fields and records. So, I have come to the same solution, where I use CTEs to select those fields and records I need. Then, use Power BI filters or measures to apply further selection. But the main data are extracted using a SQL Query. 
Thank you Mark_Timson 

  • Mark_Timson's avatar
    Mark_Timson
    Helper I
    7 years ago

    Its better when you have a date range you need to get data for but, In my case I had a very large table over 200 million rows, I couldnt load all of this data into a pbix file so I filtered a range of the data down using 2 ctes and a final select and put it all into a view like the below:

     

     

    CREATE VIEW [dbo].[Example]
 
WITH [StartDate] AS 
(
SELECT 
[date] AS [FirstDate]
FROM [dbo].[lookup_date] ld
WHERE CAST([ld].[date] AS DATE) = CAST(DATEADD(Year, -2 ,GETDATE()) AS DATE) -- Get 2 years worth of data 
)
,[EndDate] AS 
(
SELECT 
[date] AS [LastDate]
FROM [dbo].[lookup_date] ld
WHERE CAST([ld].[date] AS DATE) = CAST(GETDATE() AS DATE) -- Get to today
)
, [stk_retail] AS
(
SELECT
[week] ,
[item_code] ,
alloc_wh_stock , 
avail_wh_stock , 
branch_stock , 
wip_in_transit 
FROM [dbo].[retail]
WHERE 
[week] >= ( SELECT [FirstDate] FROM StartDate ) 
AND [week] <= ( SELECT [LastDate] FROM EndDate )
)
SELECT 
[week] ,
[item_code] ,
[alloc_wh_stock] , 
[avail_wh_stock] , 
[branch_stock] , 
[wip_in_transit] 
FROM [stk_retail]

GO

    This does 2 things gets only the columns I need making my dataset and model much smaller and only get the rows I need to import into the model making it easier to load into power bi. 

     

    For models where you need very large amounts of data creating a SSAS tabular model and partitioning it would probably be the way to go.