Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Analysis Service slow

Posted on 2014-03-05
5
Medium Priority
?
298 Views
Last Modified: 2016-02-18
I am attempting to setup a tabular model on my analysis server. I have followed the directions to a key and whenever I add a 2nd table, the queries slow down....really slow. I have created the relationship (orders table customer links to customers table customer). I did notice if you work with the adventureworks and select the orders table, then select "add related tables", it select many other tables. However, on my erp system (which exist in SQL) if I select the orders table and select "add related tables" it does not find any related tables. I know these two tables are linked. If I build a view combining the tables, the view processes very quickly. However, if I create a view to do my analysis, it kinda negates the fact that I am using the analysis service. Does anyone have a suggestion of how to make these queries run faster? Maybe I need to build the relationships in SQL and not using the ERP system. Looking for any suggestion. Thanks One other note, my analysis server is sql 2012 and my erp is on sql 2008...I do not believe that is the issue but just more info
0
Comment
Question by:jsgrosskopf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Author Comment

by:jsgrosskopf
ID: 39906542
Found out some more information. Even though I created a relationship linking the customer number in one table to the customer number in another, When I select a customer from the orders table, it shows me the name of every customer in the customer table. What could I be doing wrong. I see the relationship...but it's not linking them
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39908730
check (the same idea for SSAS 2012) SQL Server 2008 White Paper: Analysis Services Performance Guide
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=17303

and
SQL 2012 TABULAR: Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services   Paper :


Summary: Tabular models hosted in SQL Server 2012 Analysis Service provide a comparatively lightweight, easy to build and deploy solution for business intelligence. However, as you increase the data load, add more users, or run complex queries, you need to have a strategy for maintaining and tuning performance. This paper describes strategies and specific techniques for getting the best performance from your tabular models, including processing and partitioning strategies, DAX query tuning, and server tuning for specific workloads

To review the document, please download the Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services Word document.


http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Performance%20Tuning%20of%20Tabular%20Models%20in%20SQL%20Server%202012%20Analysis%20Services.docx
0
 

Author Comment

by:jsgrosskopf
ID: 39909064
Thanks for the info. However, I think I figured out what my problem is (don't knowhow to fix it). The model is not recognizing or applying my relationship. I grabbed two fields from two tables. Orders (Order Number, Customer Number), Customers (Customer Number, Name). I did a one to many with the customer numbers. if I do a pivot table and select the orders and then add the customer name, it shows me every single customer name for every single order. I cannot figure out why it will not match up my relationship.
0
 

Accepted Solution

by:
jsgrosskopf earned 0 total points
ID: 39955947
After working with Microsoft, they explained I needed to have a measure to make it work. I thought I could simply create the relationships like transact SQL and then run...not the case.
0
 

Author Closing Comment

by:jsgrosskopf
ID: 39966108
Got answer from Microsoft
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question