Solved

Analysis Service slow

Posted on 2014-03-05
5
266 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
  • 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 42

Expert Comment

by:EugeneZ
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now