Solved

Analysis Service slow

Posted on 2014-03-05
5
272 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

23 Experts available now in Live!

Get 1:1 Help Now