Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Feilin
Helper II
Helper II

Combining imported and live data???

I'm struggling a bit with large data quantities. I have a cube that I can import data from, which works. I can extract the columns and rows I want, to produce the desired reports. The problem is that it takes a huge amount of time to load (more than half an hour each time...). I thought that I could use a live connection to the cube, but then everything seems to break down. If I start with another source, I can only import the cube data while live connection is unavailable. I've since come to understand this is by design.

 

Using data from live connections I would like to:

  • also add simple tables imported from Excel files, and
  • create calculated measures on the live data (or just manipulate the data in general).

This doesn't seem to be possible (along with other basic functionalities), either because it's greyed out or simply missing. How can I achieve this? Or have I completely missed something really basic? I'm fairly new to cubes etc, so please explain in detail.

 

If live connections isn't possible, I would at the very least like to filter before I import the data from the cube, such as by date (more than 90% is too old to be relevant in most cases). How can I achieve this?

 

As a foot note, I'm quite curious as to why this is the case, and why connecting to one live source blocks all other data sources (seemingly a simple Excel table, as far as I can tell). Why is this the case; why can't I even have simple Excel lists in combination with the live connections? In general, why has the above mentioned functions not been added? To me they seem like important features, and they've had a couple of years to work something out. Maybe most importantly: are these features and/or similar ones coming in the future, and what's the ETA?

6 REPLIES 6
Feilin
Helper II
Helper II

Yes, @quentin_vigne, I know some SQL. Although, I am slightly unsure where to input that code, and what the difference would be compared to my solution below.

 

So, what I ended up doing was learning a bit about the MDX language (thanks @Anonymous for bringing that up) and, after piecing together several different sources of tutorials/examples, creating the import statement below:

 

= AnalysisServices.Database(
	"server",
	"database",
		[Query=
			"SELECT {"&
				"[Measures].[Some Measure]"&
				"} ON COLUMNS,"&
			"NONEMPTY ("&
				"CROSSJOIN ("&
						"[Date].[Date]"&
					","&
						"[Some].[Other].[Dimensions]"&
					")"&
				", "&
					"[Measures].[Some Measure]"&
				") ON ROWS "&
			"FROM ("&
				"SELECT "&
					"[Date].[Year].&[2017]"&
					" : "&
					"null "&
					"ON COLUMNS "&
				"FROM "&
					"[My Cube]"&
				")"
			]
	)

 

The query parameter of the function is what I entered into the MDX text box when importing an SSAS source, and the entire code snippet above is what is found in the source in the data view of Power BI.

 

I'm very new to this, and don't have any experience/knowledge with memory/performance issues, but what I'm trying to do is to get Some Measure based on the Date and Some Other Dimensions from My Cube. At a glance, I'd say this does what I want, since I don't seem to import the undesired irrelevant years, and it does it faster than filtering within data view (like I used to). Although I tried it and seem to get the same result (except I have an extra [empty] category in Power BI), I'm not sure if it actually does reduce amount of data loaded through the system, and it was on a fairly small test data sample and not necessarily the best approach or code.

 

If someone who's more experienced in this could give any hints (if I should rethink and do in another way), please do. Otherwise, please let me know if this is a viable solution; that would be highly appreciated, too.

Anonymous
Not applicable

You cannot combine live data with any other imported data. Power BI does not permit that. If you are using a live connection to OLAP DB, you cannot use any other data source in that report. For more details check this : https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional
quentin_vigne
Solution Sage
Solution Sage

Hi @Feilin

 

I had the same issue, I wanted to import some datas while Direct Query on the others ...

The best solution that I've found was to : 

1 - Create view of your tables with the less column possible (take everything you need and throw the rest)

2 - Try to reduce the size of the table (Does your records are dated from 1990 ? Do you really need those olds records ?)

 

3 - It worked for me, I've created view with only the needed columns and took only my sales after 2013.

 

Good luck 

Thank you, @quentin_vigne. What do you mean "create a view"? Isn't that what I do when I import a table from a live source and then perform actions on it in the first place. That's what I do right now, but the problem is that it's a lot of information, so I'd prefer if I didn't even load it into the database in the first place. So, I have data from 2010 in the cube, and I only need from 2016. Currently, I have the list of queries performed on the table which also allow me to back track, to a point where I can see all the data (earlier and irrelevant ones as well) which indicates that the data is indeed being imported. Or are you referring to something else?

 

 Thank you, @Anonymous, but the site you linked doesn't address the issue that I would like to use cubes in a certain way (see OP). Do you have any suggestions for that?

Anonymous
Not applicable

Hi @Feilin,

 

Power BI has some limitations when live connected to MDX cubes with Power BI Desktop. You cannot create DAX measures or columns. Connecting to multiple data sources are also not supported. You can vote them up here :

Create Calculated Measures

Connect multiple data sources with live MDX connection

@Feilin

 

Are you familiar with SQL query ? 

 

Creating a view is a part of sql language 

 

What you can do is do something like that : 

 

 

USE YourDataBase

GO

CREATE VIEW YourView AS

SELECT NeededColumns 

FROM YourTable

WHERE YEAR(YourDateColumn)>2015

 

I mean something like that

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.