[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SSAS

927

Solutions

851

Contributors

SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a long running query because it is reading a very large database.  The issue is there is a string of codes that each have an order number.  There can be as many as 30 on a  line. The order is then matched to another table to get the actual value.  What is the best way to loop through this row to get the codes for all of the 20 values.  This is an SQL statement.
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

We have an ETL process that populates a star schema database and then proceeds to create cubes. Problem being its blowing out the star schema ad re doing everything for scratch every single time. Which is incredibly time consuming. We are using SQL Server SSAS 2014 Standard. Is there a way not to do this anymore and just update the information that has changed?
0
need to to understand the physical structure of an OLAP database. And if I looked into a Oracle database, how can I tell if it's relational or OLAP? Isn't the OLAP data still stored in a table with keys that join to the other tables? I'm having a hard time picturing the structure and then I know querying is different too.

For years I heard these data warehouse jockeys talking about cubes, dimensions, and measures, craftily convincing the IT managers they know what they are talking about, and impressing the users that "this is the thing." But it always confused me. So I watched a few videos today, and the light bulb goes off - "oh, it's a pivot table".

btw, the cube is a nifty visual for a powerpoint presentation, but it's still presented in two-dimensions (piece of paper or a computer screen). Unless you have a 3-D printer I suppose ...

So I got the concept of measures, dimensions, etc., but now need to "get the picture" in what the data actually looks like and how to query it. I know this is bigger than one post here, but that's my objective.

For example, here's my confusion - I was also told today that an OLAP database is NOT relational in essence, it's totally different. In this article, you have
- the time dimension
- the product dimension
- the customer dimension
- the Fact table (total $ sales, quantity, discount)

BUT - don't all the dimension have keys that link it to the fact …
0
Hi Experts

 

How would you edit the following formula to count only Distinct items nested within the containsrow function...

 

OHA_OHP = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CaseType]="Face to Face"), DISTINCTCOUNT(Data[WorkType]),CONTAINSROW({"IMA OHP OffSite","OHP OnSite","IMA Network OHP","OHP Review 30 Minutes", "OHP Case Teleconference call (40 mins)"},Data[WorkType]))
0
We are using SSAS 2016 Enterprise

We are building a mega cube with multiple companies data and we want to partition by Company.
We also will want to schedule separate ETL schedules for each company.


Question is in the event there is failure from a partition perspective will that take down the entire cube? In the event the data from one company goes wonky?
0
hi,

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.
0
Hi, I am connecting to SSAS data cube (tabular model) from Excel. This is company's operational data. I am wondering if there are any templates I can reuse to visualize data. What is the best place to start? Any books/video suggestions, other resources?

Thank you in advance.
0
When communicating with SSAS msmdpump via SOAP requests Analysis services returns with Content-Types that are compressed and in binary XML (Content_type: xs+xpress).

We would like to include an extra layer of abstraction between the client and Analysis services and would like to know if there is a way to have Analysis services send back plain XML or how to read the xs+xpress content-types.
0
Is there a way in SSAS 2016 and the latest version of Excel 2016 to add a value to the Description property of a Dimension Attribute and have that render as a tooltip when hovering over it in Excel?
0
I have an excel 2016 pivot table that is using a data connection to an ssas 2012 Olap cube

When I try to add a timeline slicer I get the dreaded

We can't create a timeline for this report because it doesn't have a field formatted as a date

I do have an order date attribute that is defined as a date field on the dimCalendar table

I have done some research and found some possible causes

one possible issue mentioned is that the field may have blank or invalid dates.
(I double checked this is not the case in my data)

The other was that the field needed to have the

ValueColumn of the date key to use an actual date field from the underlying SQL table.  The KeyColumn is an integer and the NameColumn is WChar.  and to set the ValueColumn to use type Date

I validated this was true as well

Anyone have some other thoughts
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference ithrough the Colors of STEM program.

The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.
0
I want Excel to prompt me each and every time for credentials each time i try to connect to the cubes. I don't want them saved ever.

Where does that setting live?
0
the two servers involved are sql 2014 and sql 2016
Our billing system is an OLTP database and it is very hard to report off of.   I have been reading on OLAP and ssas.  Is there a good manual or starting place for a rookie to try to overcome the reporting issues.
any ideas on should i start with trying to use ssas or creating an olap database from the online transactional database.
0
All,

I'm getting the following error when I try and process my SQL data cube:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DIM_GEO', Column: 'CITY', Value: 'COLUMBUS'. The attribute is 'CITY'.

How do I correct the issue?
0
Power BI desktop and url connection to SSAS  cubes using the http msmdpump.dll. How to?

We have a url setup that gives a user access to our SSAS Cubes in Excel. That works fine no problem. (http://olap.mycubes.com)

But now we have a new user that needs to do the same thing in Power BI.

When I enter the URL in the prompt for server it gives me an "unsupported data format" error.

How do go about doing this for Power BI?
0
I have a blocking report (Blocking Detected On Production Server), Can anybody please help me understand that?

Here are the details:-

Session_id || Status || blocking_session_id || wait_type || wait_resource || WaitSec || cpu_time || Logical_reads || reads || writes

156 || Suspended || 137 || LCK_M_IS || OBJECT: 18:925246351:4 || 172.660000 || 7 || 204 || 66 || 0 ||

Anything more about this will be very helpful.

Thanks in Advance.
0
Hi,
I have invoice Header and Invoice Line Details fact Tables.
After creating Cube Invoice Header is working fine.
But Invoice Details not working properly, for example all are showing same value.(repeating)
In the invoice Header have customerID, But Invoice Details Don't have CustomerID.
Invoice Header Customer ID connected with DimCustomer,but invoiceline not connected with DimCustomer  Table.

Note: FactInvoiceHeader and FactInvoiceLine connected with Invoice Number
I tried to use Dimension Usage to connect but it's not working, Any idea how to connect with this
0
I want to create a cube that encompasses all companies. But there is security concern about a user accessing data across companies when the users access the cube via excel etc...

Having a unified cube would greatly give us granularity across time. (some companies sell assets to other companies so this would make sense when slicing the data to get accurate numbers.

Would a feasible solution be to add a company dimension and somehow hide it..or implement some kind of partitioning?

Any general suggestions would be helpful.
0
I am fairly new to PowerPivot in Excel and I was wondering about how you could use two tables of unrelated data in a pivot table.  I understand about how you can relate two tables like Customer and CustomerOrder that share a common key, but what about something like this:

Claims Table:
Claim Number
Claim Type
Year of Loss
State
County
Zip Code

Population Table:
Population Count
Year of Census
State
County
Zip Code

There isn't a true key that connects these Tables but they have many fields in common.  So if I was to list Florida Hurricane claims by loss year in a Pivot table is there any way to also list the Population for Florida for the same years?  Is there a solution that would automatically switch if I changed from State = Florida to County = DUVAL?  Could this be done with a DAX expression?
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

For example I have the below MDX query where it gives me all the data i want. however i would like to column slice all the 20K DWT's in one column and all the 25K DWT's in another column. Could I replace all the information in the FROM columns section with something like  [Voyage].[Vessel Tag].&[LIKE %20K DWT%] , [Voyage].[Vessel Tag].&[LIKE %25K DWT%] ON COLUMNS?

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Voyage].[Vessel Tag].[All]})}) ON COLUMNS ,
       NON EMPTY Hierarchize({DrilldownLevel({[Date].[Month].[All]})}) ON ROWS  
FROM (SELECT ({[Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, M],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, H],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, F],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco],
                     [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Marc],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Fra],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Hy],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco]}) ON COLUMNS  
      FROM [cube])
WHERE …
0
im Using Analysis Service for SQL Server 2014. im trying to write a simple query that cuts the amount both by Month on the row level and Vessel Tag on the column level. this get me row just fine, the second i try to cut the amount by column  [Voyage].[Vessel Tag] i get the below error. Whats the correct MDX syntax to slicing it by  [Voyage].[Vessel Tag] for column?

SELECT NON EMPTY { [Measures].[TCE] } ON COLUMNS,
              NON EMPTY { ([Date].[Month].[Month].ALLMEMBERS ) }  ON ROWS
FROM ( SELECT ( { [Date].[Year].&[2017] } ) ON COLUMNS
FROM [cube]) WHERE ( [Date].[Year].&[2017] )

"The query cannot be prepared: The query must have at least one axis.
The first axis of the query should not have multiple hierarchies,
nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
0
Using SQL Server 2014 and I'm trying to merge Analytic data and Relational data for a report. I'm almost there but there are parts that need to be dynamic in the MDX part.

See attached query. I need the DATASOURCE dynamic, the Initial Catalog dynamic, and the year currently hardcoded as 2017 dynamic. I try to replace them with variables and it keeps giving me errors.
SQLMDXQuery.txt
0
hi,

any one instlaled SQL 2017 SSAS ? the full installation always show me the starting of SSAS failed.

it seems starting from SQL 2016 it has  this problem and the only way to fix SQL2016 is to add right by DBA to the SSAS folder, then uninstall SQL 2016 SSAS and reinstall it.

any idea?
0
Hi,

I having little bit experience on SSIS,SSRS & SSAS. I want to get advanced knowledge on this.

Please provide some realtime scenarios with solutions?
0
I had this question after viewing Setup of SSAS Cube.

I am still having issues with creating a new data source.

Still looking for HELP, I AM DESPERATE. I have tried numerous approaches including the great help from Kevin Cross.  I am VERY STUCK, I cannot move forward in my training until I resolve this issue.  How do I create a new DSV when I keep getting the following error message:

"TITLE: Microsoft SQL Server Native Client 11.0
------------------------------

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

------------------------------
BUTTONS:

&Retry
Cancel
------------------------------
Any and All help would be greatly appreciated.

Thanks,

Karen
0

SSAS

927

Solutions

851

Contributors

SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

Top Experts In
SSAS
<
Monthly
>